Monday, November 24, 2014

How to Find a SQL Server Database Object in entire sql server




Script to find the weather an object is exist in entire sql server or not, if yes in which database it is exit. Below is the  better why to find a SQL Server object, such as a table, a procedure, or a trigger, would be to query the sysobjects system table in each and every  local database. Using below query we are searching for UdfUserAccessLevel



Declare @SqlStmt nvarchar(500)


/* drop the temporary -tblTempDBObjects table if already exists which store all objects information */
If Object_Id('tempdb..#tblTempDBObjects') is Not Null
Drop table #tblTempDBObjects
/* create temporary tblTempDBObjects table in temp db*/
Create TABLE tempdb..#tblTempDBObjects (
dbName sysname,
objName varchar(250),
objtype char(2)
)


/*assign string value of sql statement to insert all objects to variable */
Select @SqlStmt = 'sp_msforeachdb ''Insert tempdb..#tblTempDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''

Exec sp_executesql @SqlStmt
/* searching for equired oj=bject in temptable --- UdfUserAccessLevel */
Select * From tempdb..#tblTempDBObjects Where objName like '%UdfUserAccessLevel%'
RETURN

Above statement will give below resultset and it contain database name, object and object type.