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.
No comments:
Post a Comment