Exo-Brain Software Blog

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.

1. @searchName - Find an object with this string in its name.
2. @objType - Only look for objects of specific types. This can be ignored in favor of parameter #3, although it’s provided because, in very large databases, a match using the equality ( = ) operator instead of the LIKE operator will give somewhat better performance.
3. @objTypeList - Allows you to search for tables and views, but nothing else … or triggers, procedures, and constraints. Simply build a list of xType values - instead of only one using @objType - that you want to see results for.
4. @owner - The object’s owner or schema. If you only want to see results belonging to dbo, set @owner to dbo.
5. @excludeName - Provided to avoid useless matches. If you’re looking for any object with the word user, but don’t want to see the sysUsers table from each database you search, set @excludeName to sysUsers.
6. @ignoreDatabases - Similar to #5, you can avoid false positives and improve performance by restricting your search to databases of interest. Unlike #5, this can be a list; SQL’s Pat[tern]Index function is used to determine a match, so you can use any delimiter you like.
7. @sort - How you’d like the search results sorted.

How it Works

A temp table - #sqlSearchResults - is used to hold matches. Temporary tables are specific to the connection using them, so two people can run this code at the same time, and each have their own individual ( session based ) results table. A cursor is used to construct an insert statement logging matches from each database. Inside the cursor loop, the Exec command is used to issue a dynamic insert command populating our temp table. Finally, another Exec command selects the data back out of our search results table, ordered by whatever column or expression list the user wants.

This was designed to be a stored procedure, so, for extra credit, we’ll look into how to promote it from a humble query window script to a global proc. Stored procedures in the master database are available from any database on the server, so this is a straightforward change. Of the two declare statements, one is marked for internal use only, while the second is indented like parameters. Simply promote these when you wrap the code with a Create Procedure command.

Multiple/All Databases

SQL Server allows any number of databases to be hosted on one server: we can easily search one of them, but the question becomes how to search them all. To be useful, we only want one result set, rather than one per database.

sp_msforeachdb

An undocumented system stored procedure, sp_msForEachDB runs a dynamic query in your databases. Theoretically this happens in each db on a server, although there seem to be unknown limitations. More importantly, undocumented features in software development tools are generally meant for internal use only, not supported to the public, and may change or even vanish in any future version.

A Cursor

Cursors let you iterate through a result set, row-by-row. Each record can be evaluated, even changed, in a procedural manner. Instead of applying clever formulas in a select list - the set-based approach - a cursor lets you use looping, branch control, other queries or stored procedures, and other concepts for every row in a query.

This is a familiar concept to anyone who’s developed software using virtually any procedural language ( C and its variants, VB, PHP, etc ) but somewhat alien to relational theory.

System Tables

Even in humble Access, Microsoft has shown they like storing metadata in relational format. SQL Server provides a number of information schema views that can simplify the type of work we’re doing. If the code above didn’t allow users to search for any type of SQL object, the system views might be a better choice.

Master..SysDatabases

The sysDatabases table in the master database has a row for every database the server maintains. We only use the name column in the script above, although crdate and cmptlevel can also be interesting. In the search code, the magic is Declare c Cursor For Select Name From sysDatabases. Interestingly, the three tables being used in this query are available in both the dbo and sys schema.

SysObjects

This exists in every database. Every object in the db has a row in this table, giving its: name, type, parent ( triggers and constraints are ‘children’ of the tables they apply to ) and the owner or schema. The xType column is a char(2) value - this is important because single-character values are padded with a space - whose most important values are

U

User table

V

View

P

Stored Procedure

TR

Trigger

FN, IF, TF

User Defined Functions ( UDFs ) broken out as scalar, inline table, and table valued

PK, FK

Primary and Foreign Keys

SysUsers

Again, this table exists in every database, and is joined to sysObjects to find the schema an object ‘lives’ in. Of interest here are uid and name.