Object Search for Sql Server 2005

,

Change the @text value to the text you want to search for.

Alter the database names in the below part of the script to only the databases you want to search in. The below code part will only search in Master, Tempdb, and MSDB.

DELETE FROM #databases
WHERE DatabaseName not IN ('master','tempdb','msdb')

 

SET quoted_identifier ON 

GO 

SET ansi_nulls ON 

GO 

IF Object_id('tempdb..#columns') IS NOT NULL 
 DROP TABLE #columns 

IF Object_id('tempdb..#servers') IS NOT NULL 
 DROP TABLE #servers 

IF Object_id('tempdb..#databases') IS NOT NULL 
 DROP TABLE #databases 

DECLARE @ob AS VARCHAR(50), 
 @svname AS VARCHAR(50), 
 @dbname AS VARCHAR(50), 
 @text AS VARCHAR(50) 

IF @ob = '' 
 SET @ob = NULL 

IF @svname = '' 
 SET @svname = NULL 

IF @dbname = '' 
 SET @dbname = NULL 

--Change the word Test to what you are looking for 
SET @text = 'Job' 

CREATE TABLE #servers ( 
 sname VARCHAR(255)) 

CREATE TABLE #databases ( 
 ServerName VARCHAR(255), 
 DatabaseName VARCHAR(128)) 

CREATE TABLE #columns ( 
 Foundin VARCHAR(100), 
 ServerName VARCHAR(100), 
 DatabaseName VARCHAR(100), 
 SchemaName VARCHAR(50), 
 TableName VARCHAR(255), 
 ColumnName VARCHAR(245), 
 LineNumber INT, 
 ProcName VARCHAR(200)) 

INSERT INTO #servers 
SELECT @@ServerName 

UPDATE #servers 
SET sname = Ltrim(sname) 

DELETE #servers 
WHERE sname = 'Servers:' 
 OR sname LIKE '%(local)%' 
 OR sname IS NULL 

CREATE INDEX idx_servname ON #servers ( 
 sname) 

DECLARE search CURSOR FOR 
SELECT sname 
FROM #servers 

DECLARE @Servname VARCHAR(200) 

DECLARE @TableName VARCHAR(200) 

DECLARE @Type CHAR(1) 

DECLARE @ColumnName VARCHAR(128) 

DECLARE @LINKSERVER VARCHAR(128) 

DECLARE @SQL VARCHAR(1000) 

DECLARE @SQL1 VARCHAR(1000) 

DECLARE @SQL2 VARCHAR(1000) 

DECLARE @SQL3 VARCHAR(1000) 

OPEN search 

FETCH NEXT FROM search 
INTO @Servname 

WHILE @@FETCH_STATUS = 0 
 BEGIN 
 SET @SQL = 'INSERT INTO #DATABASES select distinct ' + Char(39) + @servname + Char(39) + ' AS ServerName, name from [' + @servname + ']. master.dbo.sysdatabases' 
 
 EXEC( @SQL) 
--Put the database names of databases you want to search 
 DELETE FROM #databases 
 WHERE DatabaseName not IN ('master','tempdb','msdb') 
 
 DECLARE databases CURSOR FOR 
 SELECT DatabaseName 
 FROM #databases 
 
 DECLARE @database VARCHAR(100) 
 
 OPEN databases 
 
 FETCH NEXT FROM databases 
 INTO @database 
 
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
 SET @sql1 = ('insert into #columns (foundin,ServerName,DatabaseName,SchemaName,TableName, ColumnName)SELECT distinct ''Column'' as Foundin,' + Char(39) + @servname + Char(39) + ' as ServerName, ' + Char(39) + @database + Char(39) + ' as DatabaseName,Table_Schema as SchemaName, Table_name AS TableName, Column_name as ColumnName FROM ' + @database + '.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ''%' + @text + '%''') 
 
 IF @ob = 'C' 
 EXEC( @sql1) 
 
 IF @ob IS NULL 
 EXEC( @sql1) 
 
 SET @sql2 = ('insert into #columns (foundin,ServerName,DatabaseName,SchemaName,TableName) SELECT distinct ''Table'' as Foundin,' + Char(39) + @servname + Char(39) + ' as ServerName, ' + Char(39) + @database + Char(39) + ' as DatabaseName,Table_Schema as SchemaName,Table_name AS TableName FROM ' + @database + '.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ''%' + @text + '%''') 
 
 IF @ob = 'T' 
 OR @ob = '' 
 EXEC( @sql2) 
 
 IF @ob IS NULL 
 EXEC( @sql2) 
 
 IF @ob = 'P' 
 INSERT INTO #columns 
 (foundin, 
 ServerName, 
 DatabaseName, 
 SchemaName, 
 LineNumber, 
 ProcName) 
 SELECT DISTINCT 'Proc' AS foundin, 
 '' + @servname + '' AS ServerName, 
 '' + @database + '' AS DatabaseName, 
 s.name AS SchemaName, 
 Len(Substring(TEXT,1,Patindex('%' + @text + '%',TEXT))) - Len(Replace(Substring(TEXT,1,Patindex('%' + @text + '%',TEXT)), 
 Char(13),'')) + 1 AS line, 
 Object_name(id) AS ProcName 
 FROM syscomments sc 
 JOIN sys.all_objects ao 
 ON sc.id = ao.object_id 
 JOIN sys.schemas s 
 ON s.schema_id = ao.schema_id 
 WHERE TEXT LIKE '%' + @text + '%' 
 ORDER BY ProcName, 
 line 
 
 IF @ob IS NULL 
 INSERT INTO #columns 
 (foundin, 
 ServerName, 
 DatabaseName, 
 SchemaName, 
 LineNumber, 
 ProcName) 
 SELECT DISTINCT 'Proc' AS foundin, 
 '' + @servname + '' AS ServerName, 
 '' + @database + '' AS DatabaseName, 
 s.name AS SchemaName, 
 Len(Substring(TEXT,1,Patindex('%' + @text + '%',TEXT))) - Len(Replace(Substring(TEXT,1,Patindex('%' + @text + '%',TEXT)), 
 Char(13),'')) + 1 AS line, 
 Object_name(id) AS ProcName 
 FROM syscomments sc 
 JOIN sys.all_objects ao 
 ON sc.id = ao.object_id 
 JOIN sys.schemas s 
 ON s.schema_id = ao.schema_id 
 WHERE TEXT LIKE '%' + @text + '%' 
 ORDER BY ProcName, 
 line 
 
 FETCH NEXT FROM databases 
 INTO @database 
 END 
 
 CLOSE databases 
 
 DEALLOCATE databases 
 
 FETCH NEXT FROM search 
 INTO @Servname 
 END 

CLOSE search 

DEALLOCATE search 

SELECT DISTINCT * 
FROM #columns 
WHERE (ServerName = @svname 
 OR @svname IS NULL) 
 AND (DatabaseName = @dbname 
 OR @dbname IS NULL) 
ORDER BY 1, 
 2, 
 3, 
 4 

GO 

SET quoted_identifier OFF 

GO 

SET ansi_nulls ON 

GO

Rate

5 (1)

Share

Share

Rate

5 (1)