Object Search for Sql Server 2005

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