Column Dependencies in SQL Server

Having the need to do a column rename in the DB I wanted to try and find all references to the column to ensure I didn’t miss any.

By using the INFORMATION_SCHEMA views in SQL server I used the following to locate all references…

NB: Something to watch for, the SQL containing LIKE may return additional columns to the ones you search for e.g. searching for ‘CUSTOMER’ would return ‘CUSTOMER_ID’, ‘CUSTOMER_ORDER_NO’, etc…

DECLARE @COLUMN_NAME NVARCHAR(100)

SET @COLUMN_NAME = 'SAC_PREFIX'

--List Tables and Views using the column
SELECT
	TAB.TABLE_NAME,
	TAB.TABLE_TYPE,
	COL.COLUMN_NAME,
	COL.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS COL
	JOIN INFORMATION_SCHEMA.TABLES TAB
		ON COL.TABLE_NAME = TAB.TABLE_NAME
WHERE COLUMN_NAME = @COLUMN_NAME

--List Stored Procs / Fuctions using the column
SELECT
	ROUTINE_NAME,
	ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' + @COLUMN_NAME + '%'

--List constraints using the column
SELECT 
	*
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE COLUMN_NAME = @COLUMN_NAME

--Key column usage
SELECT 
	TABLE_NAME,
	COLUMN_NAME,
	ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE COLUMN_NAME = @COLUMN_NAME
Advertisements
This entry was posted in Programming and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s