Anybody who’s ever had to work with a SQL Server object without being told which database it lives in, or worked in a huge schema - like Great Plains or SAP - should find this post interesting. There are several ways to search for a database object by its name or type; we’ll examine a few options, look into the issues involved, and present code to accomplish our search.
The Code
Set NoCount On
Declare @dbName varChar(200), @ins varChar(1000)–Internal use only
Declare
@searchName varChar(200),
@objType varChar(2),
@objTypeList varChar(300),
@owner varChar(200),
@excludeName varChar(100),
@ignoreDatabases varChar(1000),
@sort varChar(1000)
Set @searchName = ‘Log’
/*Set @objTypeList = ‘U, P, V, FN, TR’
Set @objType = ‘P’
Set @owner = ‘dbo’
Set @excludeName = ‘Backup-Log’*/
Set @searchName = IsNull(@searchName, ”)
Set @sort = IsNull(@sort, ‘DatabaseName, SchemaOwner, SqlObjectType’)
Set @ignoreDatabases = IsNull(@ignoreDatabases, ‘model, msdb, tempDB, master, ReportServerTempDB, ReportServer’)
Create Table #sqlSearchResults ( DatabaseName varChar(200), SchemaOwner varChar(100), ObjectName varChar(200), SqlObjectType varChar(2) )
Declare c Cursor Forward_Only For
Select name From master.dbo.sysDatabases
For Read Only
Open c
Fetch Next From c Into @dbName
While @@Fetch_Status = 0 Begin
If PatIndex(’%’ + @dbName + ‘%’, @ignoreDatabases) <= 0 Begin
Set @ins = ‘Insert Into #sqlSearchResults Select ”’ + @dbName + ”’ As DatabaseName, IsNull(U.Name, ”???”), O.Name, xType From ‘ + @dbName + ‘.dbo.sysObjects O Left Join ‘ + @dbName + ‘.dbo.sysUsers U On O.uid = U.uid Where (O.Name Like ”’ + ‘%’ + @searchName + ‘%”)’
If @objType Is Not Null Set @ins = @ins + ‘ And (xType = ‘ + ”” + @objType + ”’)’
If @owner Is Not Null Set @ins = @ins + ‘ And (U.Name = ‘ + ”” + @owner + ”’)’
If @objTypeList Is Not Null Set @ins = @ins + ‘ And (”’ + @objTypeList + ”’ Like ”%” + rTrim(xType) + ”%”)’
If @excludeName Is Not Null Set @ins = @ins + ‘ And (O.Name Not Like ‘ + ”’%’ + @excludeName + ‘%”)’
Exec(@ins)
End
–Print @ins
Fetch Next From c Into @dbName
End
Close c
DeAllocate c
Exec(‘Select * From #sqlSearchResults Order By ‘ + @sort)
Drop Table #sqlSearchResults
Arguments
There are seven variables you can set to control the search. All are optional; passing nothing in will return a list of all objects in all non-system databases. Continue Reading »