Here’s a Stored Procedure May Want to Use to Find Objects
Create PROC FindObject_usp (
@objname varchar(200) = Null
, @objtype varchar(20) = Null
)
As
Declare @sqlstr nvarchar(200)
— Insert wildcard, if exact search is not required.
— Set @objname = ‘%’ + @objname + ‘%’
— Its better to supply custom wild card in the input parameter @objname
/* drop the temporary table if already exists */
If Object_Id(‘tempdb..#tblDBObjects’) is Not Null
Drop table #tblDBObjects
/* create temporary table */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)
Begin
If @objtype = ‘CHECK’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””C”””’
If @objtype = ‘Default’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””D”””’
If @objtype = ‘FOREIGN KEY’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””F”””’
If @objtype = ‘Log’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””L”””’
If @objtype = ‘Scalar function’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””FN”””’
If @objtype = ‘Inlined table-function’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””IF”””’
If @objtype = ‘Stored procedure’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””P”””’
If @objtype = ‘PRIMARY KEY’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””PK”””’
If @objtype = ‘Replication filter stored procedure’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””RF”””’
If @objtype = ‘System table’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””S”””’
If @objtype = ‘Table function’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””TF”””’
If @objtype = ‘Trigger’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””TR”””’
If @objtype = ‘User table’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””U”””’
If @objtype = ‘UNIQUE constraint’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””UQ”””’
If @objtype = ‘View’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””V”””’
If @objtype = ‘Extended stored procedure’
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects where xtype = ””X”””’
If (@objtype = ”) Or (@objtype is Null)
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects”’
End
/* execute SQL string */
If (@sqlstr <> ”) Or (@sqlstr is Not Null)
Exec sp_executesql @sqlstr
/* If @objname is not supplied it should still return result */
If (@objname = ”) Or (@objname is Null)
Select * From #tblDBObjects
Else
Select * From #tblDBObjects Where objName like @objname
RETURN
(Script V)
The above script creates a stored procedure which takes two optional parameters, @objname (name of the object to be searched) and @objtype (type of the object to be searched). Different types of object type and their abbreviations can be found in SQL online help for sysobjects string). Stored procedure FindObject_usp creates different SQL string based on different object types, e.g., @objtype parameter, if @objtype is not provided is selects all the objects from sysobjects table and inserts into #tblDBObjects temp table. It is evident that incase of enormous databases, if object type is known, providing @objtype parameter makes query much faster. Once #tblDBObjects table has been populated it can be queried with @objname parameter with or without a wild card as needed.
We can execute the FindObject_usp procedure, for example, to find a object of type Check constraints whose name starts with ‘CK_B’ as;
Exec FindObject_usp ‘CK_B%’, ‘check’
Or
Exec FindObject_usp1 ‘xp_%’, Null