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


Creating a simple T-SQL query

For people starting to learn how to write T-SQL queries the first thing you need to know is how to perform a simple query. A query is a way to get information back from a table in a database.

A table should represent a single entity, for example you may have a person table or a product table. We will focus on the person table for this example. A person may have multiple attributes (first name, last name, birth date, sex). These attributes are defined as columns/fields in a table (similar to a row header in a spreadsheet).



The first part of the query is the select statement. You will use the select statement to define what information you want to see pulled from the table. Each field needs to be separated by a comma. If we are only interested in seeing the first name and last name we would write the following.

select first_name, last_name

The statement above will fail to execute because the query is not complete. We have not defined where we wanted to get this information from. For this example table is named "person". In order to declare where we want to get the data from, we use the "from" statement after the list of columns as follows:

select first_name, last_name
from person

That is pretty much the simplest a query will get.