Find Columns and Object Definitions in SQL Server

March 11, 2019

A few years ago, I needed to find a query that I had written in a SQL Stored Procedure but after clicking through several of them and striking out, I decided that there had to be a better way. I ended up writing a simple Stored Procedure with a single search parameter that would return a stored procedure definition and excerpt from it. I like it so much that I’ve added it to every database I’ve managed since then. It was a quick and dirty solution to a problem and I never really put much more thought into it – until tonight.

I was on the Windows Admins Slack tonight working on some SQL queries in the SCCM/ConfigMgr database with Riley Childs and Cody Mathis and during the course of the conversation, Cody shared a short query to find columns with a specific name in the database which got me thinking about my Stored Procedure. I took a look at what I was using and realized that we were in the same book, just on different pages. I took a few minutes and combined our logic and added a few extra bits to make a nicer version of the query. It will now return Table, View, Stored Procedure, and Function names where the object name, column name or object definition match your search criteria. It’s simple but effective. Hope you find it useful as well.

The Script

I’ve added it to my GitHub repo, which I will update as needed, or you can grab the code below:

--Change the USING to the DB you want to run this against. 
USE [CM_ASD]

--Change the value to your search term
DECLARE @Text nvarchar(1000)
SET @Text = 'TextToFind'


----Main Query--

DECLARE @newText varchar(1000)
SET @NewText = '%' + @Text + '%'

SELECT
	DISTINCT
	'TableOrView' AS 'ObjectType'
	,TABLE_NAME AS  'ObjectName'	
	,CASE WHEN COLUMN_NAME LIKE @NewText THEN COLUMN_NAME ELSE NULL END AS 'ColumnName'
	,NULL AS 'Excerpt'
	,NULL AS 'Definition'
FROM
	INFORMATION_SCHEMA.COLUMNS
WHERE
	TABLE_NAME LIKE @NewText OR
	COLUMN_NAME LIKE @NewText

UNION

SELECT
	DISTINCT
	'StoredProcedure' AS 'ObjectType'
	,SPECIFIC_NAME AS 'ObjectName'
	,NULL AS 'ColumnName'
	,SUBSTRING(ROUTINE_DEFINITION, CHARINDEX(@Text, ROUTINE_DEFINITION)-50, 100) AS 'Excerpt'	
	,ROUTINE_DEFINITION AS 'Definition'
FROM 
	INFORMATION_SCHEMA.ROUTINES
WHERE
	SPECIFIC_NAME LIKE @NewText OR
	ROUTINE_DEFINITION LIKE @NewText

UNION

SELECT
	DISTINCT
	'ViewDefinition' AS 'ObjectType'
	,TABLE_NAME AS 'ObjectName'
	,NULL AS 'ColumnName'
	,SUBSTRING(VIEW_DEFINITION, CHARINDEX(@text, VIEW_DEFINITION)-50, 100) as 'Excerpt'
	,VIEW_DEFINITION  AS 'Definition'
FROM 
	INFORMATION_SCHEMA.VIEWS
WHERE
	TABLE_NAME LIKE @NewText OR
	VIEW_DEFINITION LIKE @newText

UNION

SELECT
	DISTINCT
	'Function' AS 'ObjectType' 
	,TABLE_NAME AS  'ObjectName'
	,COLUMN_NAME AS 'ColumnName'
	,NULL AS 'Excerpt'
	,NULL AS 'Definition'
FROM 
	INFORMATION_SCHEMA.ROUTINE_COLUMNS
WHERE
	COLUMN_NAME LIKE @newText
ORDER BY
	ObjectType,
	ColumnName,
	ObjectName

--Change the USING to the DB you want to run this against.

USE [CM_ASD]

--Change the value to your search term

DECLARE @Text nvarchar(1000)

SET @Text = 'TextToFind'

----Main Query--

DECLARE @newText varchar(1000)

SET @NewText = '%' + @Text + '%'

SELECT

DISTINCT

'TableOrView' AS 'ObjectType'

,TABLE_NAME AS 'ObjectName'

,CASE WHEN COLUMN_NAME LIKE @NewText THEN COLUMN_NAME ELSE NULL END AS 'ColumnName'

,NULL AS 'Excerpt'

,NULL AS 'Definition'

FROM

INFORMATION_SCHEMA.COLUMNS

WHERE

TABLE_NAME LIKE @NewText OR

COLUMN_NAME LIKE @NewText

UNION

SELECT

DISTINCT

'StoredProcedure' AS 'ObjectType'

,SPECIFIC_NAME AS 'ObjectName'

,NULL AS 'ColumnName'

,SUBSTRING(ROUTINE_DEFINITION, CHARINDEX(@Text, ROUTINE_DEFINITION)-50, 100) AS 'Excerpt'

,ROUTINE_DEFINITION AS 'Definition'

FROM

INFORMATION_SCHEMA.ROUTINES

WHERE

SPECIFIC_NAME LIKE @NewText OR

ROUTINE_DEFINITION LIKE @NewText

UNION

SELECT

DISTINCT

'ViewDefinition' AS 'ObjectType'

,TABLE_NAME AS 'ObjectName'

,NULL AS 'ColumnName'

,SUBSTRING(VIEW_DEFINITION, CHARINDEX(@text, VIEW_DEFINITION)-50, 100) as 'Excerpt'

,VIEW_DEFINITION AS 'Definition'

FROM

INFORMATION_SCHEMA.VIEWS

WHERE

TABLE_NAME LIKE @NewText OR

VIEW_DEFINITION LIKE @newText

UNION

SELECT

DISTINCT

'Function' AS 'ObjectType'

,TABLE_NAME AS 'ObjectName'

,COLUMN_NAME AS 'ColumnName'

,NULL AS 'Excerpt'

,NULL AS 'Definition'

FROM

INFORMATION_SCHEMA.ROUTINE_COLUMNS

WHERE

COLUMN_NAME LIKE @newText

ORDER BY

ObjectType,

ColumnName,

ObjectName

Sample Output

This shows an example of the expected output. I changed my DB name to CM_PS1 and entered my search of BGB.

Also, if you’re looking for something more robust that can search within the database data itself (plus much more), check out ApexSQL Search. I just started using it a few weeks ago and it really is fantastic. Best of all, it’s FREE, along with several other tools that I plan to try at some point.