Search Columns, Stored Procedures, or Tables using Dynamic SQL

I wanted to share the script I created to help Analysts and DBA's easily identify where a particular column, table, or keyword is used within a particular instance.


DECLARE @Dynamic_SQL VARCHAR(MAX)
DECLARE @SSQL VARCHAR(MAX)
DECLARE @DB_NAME VARCHAR(256)
DECLARE @SearchParam VARCHAR(256)
SET @SearchParam = 'ENTER KEYWORD HERE'

--REMOVE COMMENT FROM ONE OBJECT YOU WANT TO SEARCH
--Table Name Search
--set @Dynamic_SQL = 'select ''[dbname]'' database_name, name, null [col] from [dbname].dbo.sysobjects where xtype=''u'' and name like ''%'+@SearchParam+'%'''

--Stored Procedure Dependency Search
--set @Dynamic_SQL = 'use [dbname];select ''[dbname]'' database_name, Name, null [col] FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'+@SearchParam+'%'''

--Column Name Search
--set @Dynamic_SQL = 'select ''[dbname]'' database_name, so.name, sc.name [col] from [dbname].dbo.sysobjects so inner join [dbname].dbo.syscolumns sc on so.id = sc.id where so.xtype=''u'' and so.name like ''%%'' and sc.name like ''%'+@SearchParam+'%'''

CREATE TABLE #Results (dbname VARCHAR(256), [name] VARCHAR(MAX), col VARCHAR(MAX))

--GET DB LIST
SELECT [name]
INTO #DBNAME
FROM MASTER.dbo.sysdatabases
WHERE [name] NOT IN('master','tempdb','model','msdb')

DECLARE Cur_DB CURSOR FOR SELECT [name] FROM #DBNAME
OPEN Cur_DB
FETCH Next FROM Cur_DB INTO @DB_NAME

WHILE @@FETCH_STATUS = 0 BEGIN
   SET @SSQL = REPLACE(@Dynamic_SQL,'dbname',@DB_Name)
   --print @SSQL
   BEGIN TRY
       INSERT INTO #Results (dbname, [name], [col])
       EXEC(@SSQL)
   END TRY
   BEGIN CATCH
       IF @@TRANCOUNT > 0
   ROLLBACK
   END CATCH

   FETCH Next FROM Cur_DB INTO @DB_NAME

END
CLOSE Cur_DB
DEALLOCATE Cur_DB
DROP TABLE #DBNAME

SELECT dbname, [name], col FROM #Results
ORDER BY dbname, [name], col

DROP TABLE #Results