Hello Readers!
Below is how do we search for a word or a subscript in every object definitions across the databases in an instances. The script basically uses a cursor to navigate among databases and further uses object_definition function to scan the definitions for a word or a substring.
For logging the result set from each database, I have created a table in DBadmin databases and have also made sure all the object types are covered. "Create" is the substring we are seaching for , which is expected to have a lot of results as most of object definition starts with "Create ". Please replace "Create" with what you are looking for .
One of a few other applications of this script could be finding if a cross DB query is used to reference objects in other database. For example , If you think you have objects like stored procedures, views that are written with cross DB query (DB.Schema.objectName) that references one of a master kind of database named 'masterdb', and you wish to confirm if a particular DB is referred from every database in your instance, you can try searching for "%masterdb.%" or "%masterdb].%". This will list all objects in your instances which is written with scripts referring to the database masterDB with masterdb.dbo.object or [masterdb].dbo.object.
Below is the script. Hope you find it useful !
Use DbAdmin
go
IF OBJECT_ID('dbo.substringref', 'U') IS NOT NULL
DROP TABLE dbo.substringref;
Create table dbo.substringref (ObjectName Nvarchar(100),ObjectId int,ObjectDefinition nvarchar(max),DatabaseName Nvarchar(30),object_type Nvarchar(30))
go
Use master
go
DECLARE @tsql nvarchar(max)
DECLARE @dbname varchar(500)
DECLARE MyCur CURSOR STATIC FORWARD_ONLY FOR
SELECT [name]
FROM sys.databases
WHERE [name] NOT IN ('tempdb','master','Msdb','model','distribution')
OPEN MyCur
WHILE (1=1)
BEGIN
FETCH NEXT FROM MyCur INTO @dbname
IF @@FETCH_STATUS <> 0
BREAK
SET @tsql = 'use ' + @dbname + '
SELECT [name] as ObjectName,[id] as ObjectID,Object_definition(id) as [Defenition],Db_Name() as Databasename,
CASE obj.type
WHEN ''AF'' THEN ''Aggregate function (CLR)''
WHEN ''C'' THEN ''CHECK constraint''
WHEN ''D'' THEN ''Default or DEFAULT constraint''
WHEN ''F'' THEN ''FOREIGN KEY constraint''
WHEN ''FN'' THEN ''Scalar function''
WHEN ''FS'' THEN ''Assembly (CLR) scalar-function''
WHEN ''FT'' THEN ''Assembly (CLR) table-valued functionIF = In-lined table-function''
WHEN ''IT'' THEN ''Internal table''
WHEN ''K'' THEN ''PRIMARY KEY or UNIQUE constraint''
WHEN ''L'' THEN ''Log''
WHEN ''P'' THEN ''Stored procedure''
WHEN ''PC'' THEN ''Assembly (CLR) stored-procedure''
WHEN ''R'' THEN ''Rule''
WHEN ''RF'' THEN ''Replication filter stored procedure''
WHEN ''S'' THEN ''System table''
WHEN ''SN'' THEN ''Synonym''
WHEN ''SQ'' THEN ''Service queue''
WHEN ''TA'' THEN ''Assembly (CLR) DML trigger''
WHEN ''TF'' THEN ''Table function''
WHEN ''TR'' THEN ''SQL DML Trigger''
WHEN ''TT'' THEN ''Table type''
WHEN ''U'' THEN ''User table''
WHEN ''V'' THEN ''View''
WHEN ''X'' THEN ''Extended stored procedure''
ELSE ''Unspecified'' END AS [type]
FROM sys.sysobjects obj
WHERE Object_definition(id) LIKE ''%Create%''
Insert into DBadmin.dbo.substringref
EXEC sp_executesql @tsql
END
CLOSE MyCur;
DEALLOCATE MyCur;
go
use DBadmin
go
Select * from dbo.substringref
Below is how do we search for a word or a subscript in every object definitions across the databases in an instances. The script basically uses a cursor to navigate among databases and further uses object_definition function to scan the definitions for a word or a substring.
For logging the result set from each database, I have created a table in DBadmin databases and have also made sure all the object types are covered. "Create" is the substring we are seaching for , which is expected to have a lot of results as most of object definition starts with "Create ". Please replace "Create" with what you are looking for .
One of a few other applications of this script could be finding if a cross DB query is used to reference objects in other database. For example , If you think you have objects like stored procedures, views that are written with cross DB query (DB.Schema.objectName) that references one of a master kind of database named 'masterdb', and you wish to confirm if a particular DB is referred from every database in your instance, you can try searching for "%masterdb.%" or "%masterdb].%". This will list all objects in your instances which is written with scripts referring to the database masterDB with masterdb.dbo.object or [masterdb].dbo.object.
Below is the script. Hope you find it useful !
Use DbAdmin
go
IF OBJECT_ID('dbo.substringref', 'U') IS NOT NULL
DROP TABLE dbo.substringref;
Create table dbo.substringref (ObjectName Nvarchar(100),ObjectId int,ObjectDefinition nvarchar(max),DatabaseName Nvarchar(30),object_type Nvarchar(30))
go
Use master
go
DECLARE @tsql nvarchar(max)
DECLARE @dbname varchar(500)
DECLARE MyCur CURSOR STATIC FORWARD_ONLY FOR
SELECT [name]
FROM sys.databases
WHERE [name] NOT IN ('tempdb','master','Msdb','model','distribution')
OPEN MyCur
WHILE (1=1)
BEGIN
FETCH NEXT FROM MyCur INTO @dbname
IF @@FETCH_STATUS <> 0
BREAK
SET @tsql = 'use ' + @dbname + '
SELECT [name] as ObjectName,[id] as ObjectID,Object_definition(id) as [Defenition],Db_Name() as Databasename,
CASE obj.type
WHEN ''AF'' THEN ''Aggregate function (CLR)''
WHEN ''C'' THEN ''CHECK constraint''
WHEN ''D'' THEN ''Default or DEFAULT constraint''
WHEN ''F'' THEN ''FOREIGN KEY constraint''
WHEN ''FN'' THEN ''Scalar function''
WHEN ''FS'' THEN ''Assembly (CLR) scalar-function''
WHEN ''FT'' THEN ''Assembly (CLR) table-valued functionIF = In-lined table-function''
WHEN ''IT'' THEN ''Internal table''
WHEN ''K'' THEN ''PRIMARY KEY or UNIQUE constraint''
WHEN ''L'' THEN ''Log''
WHEN ''P'' THEN ''Stored procedure''
WHEN ''PC'' THEN ''Assembly (CLR) stored-procedure''
WHEN ''R'' THEN ''Rule''
WHEN ''RF'' THEN ''Replication filter stored procedure''
WHEN ''S'' THEN ''System table''
WHEN ''SN'' THEN ''Synonym''
WHEN ''SQ'' THEN ''Service queue''
WHEN ''TA'' THEN ''Assembly (CLR) DML trigger''
WHEN ''TF'' THEN ''Table function''
WHEN ''TR'' THEN ''SQL DML Trigger''
WHEN ''TT'' THEN ''Table type''
WHEN ''U'' THEN ''User table''
WHEN ''V'' THEN ''View''
WHEN ''X'' THEN ''Extended stored procedure''
ELSE ''Unspecified'' END AS [type]
FROM sys.sysobjects obj
WHERE Object_definition(id) LIKE ''%Create%''
Insert into DBadmin.dbo.substringref
EXEC sp_executesql @tsql
END
CLOSE MyCur;
DEALLOCATE MyCur;
go
use DBadmin
go
Select * from dbo.substringref
