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
Thursday, August 11, 2011
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.
Labels:
Column,
columns,
DB,
Dynamic SQL,
find,
Search,
Stored Procedure,
stored procedures,
T-SQL,
Table,
tables,
TSQL
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment