Advanced Analysis with Information Schema Views

This is a follow up to the Intro to Information Schema Views that can be found here. Now we will look at a script that will display a lot of information about where a column is used. This example will use a key field because it is more prevalent throughout the database. Let’s take a look at the data sets the script will return.

1. Column Usage. Basic list of where the column is used.
2. Table Constraints. Is the column used in any type of constraint?
3. Referential Constraints. Lists the referenced table for foreign keys.
4. View Usage. Lists views that reference the column.
5. Stored Procedure Usage. List of Stored Procedure that reference the column. NOTE: this may return false positives because of the search. This query uses a CHARINDEX function because Full Text Search is not always turned on and to avoid dynamic SQL. But, you should be able to quickly review the output.

Script

Declare @ColName varchar(80) = 'ReturnID'; -- Update with column name to search.

-- Column Usage

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE

FROM INFORMATION_SCHEMA.COLUMNS

where COLUMN_NAME = @ColName

---- Table Constraints

SELECT TC.CONSTRAINT_TYPE, kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME, kcu.CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu

join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

on kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME

where COLUMN_NAME = @ColName

order by tc.CONSTRAINT_TYPE

-- Referential Constraints

SELECT KCU.CONSTRAINT_SCHEMA, KCU.CONSTRAINT_NAME, kcu.TABLE_SCHEMA, KCU.TABLE_NAME, COLUMN_NAME,

TC.TABLE_SCHEMA AS REFERENCED_SCHEMA, tc.TABLE_NAME AS REFERENCED_TABLE

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu

join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc

on kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME

and KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA

join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

on rc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA

and rc.UNIQUE_CONSTRAINT_NAME = tc.CONSTRAINT_NAME

where COLUMN_NAME = @ColName

-- View Usage

SELECT VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

where COLUMN_NAME = @ColName

-- Procedure or Function

SELECT ROUTINE_TYPE, ROUTINE_SCHEMA, ROUTINE_NAME

FROM INFORMATION_SCHEMA.ROUTINES

WHERE charindex(@ColName, ROUTINE_DEFINITION) > 0

Sample Output

Column Usage

TABLE SCHEMA	TABLE_NAME	COLUMN_NAME	DATA TYPE	COLUMN DEFAULT	IS_NULLABLE
CW	TaxpayerReturn	ReturnID	int	NULL	NO
CW	RuleStaging	ReturnID	int	NULL	NO
CW	ReturnSchedule	ReturnID	int	NULL	NO
CW	ReturnDueDayExceptions	ReturnID	int	NULL	NO

Table Constraints

CONSTRAINT TYPE	TABLE SCHEMA	TABLE_NAME	COLUMN NAME	CONSTRAINT_NAME
FOREIGN KEY	CW	JurisdictionReturn	ReturnID	FK_JurisdictionReturn_Return
FOREIGN KEY	CW	PrepaymentReturn	ReturnID	FK_PrepaymentReturn_Return
FOREIGN KEY	CW	ReturnDueDayExceptions	ReturnID	FK_ReturnDueDayExceptions_Return
FOREIGN KEY	CW	ReturnFile	ReturnID	FK_ReturnFile_Return

Referential Constraints

CONSTRAINT SCHEMA	CONSTRAINT NAME	TABLE SCHEMA	TABLE NAME	COLUMN NAME	REFERENCED SCHEMA	REFERENCED TABLE
CW	FK_JurisdictionReturn_Return	CW	JurisdictionReturn	ReturnID	CW	Return
CW	FK_PrepaymentReturn_Return	CW	PrepaymentReturn	ReturnID	CW	Return
CW	FK_ReturnFile_Return	CW	ReturnFile	ReturnID	CW	Return
CW	FK_ReturnRule_Return	CW	ReturnRule	ReturnID	CW	Return

View Usage

VIEW SCHEMA	VIEW_NAME	TABLE SCHEMA	TABLE_NAME	COLUMN_NAME
CW	ScheduleSpecificSettingsActive	CW	ReturnSchedule	ReturnID
CW	ReturnActive	CW	ReturnSMB	ReturnID
CW	ReturnActive	CW	Return	ReturnID
CW	ReturnActive	CW	ReturnTaxCategory	ReturnID

Stored Procedure Usage

ROUTINE_TYPE	ROUTINE_SCHEMA	ROUTINE_NAME
PROCEDURE	CW	TaxpayerReturnFilePayment_InsUpd
PROCEDURE	CW	GetJurisdictionsByIntegrationClientRegistryID
PROCEDURE	CW	Configurator_DeleteTaxpayer
PROCEDURE	CW	Admin_ResetSingleClient

Conclusion

With this script you get a picture of the usage of a column. The next time you are doing data flow analysis, refactoring, or just working on a new feature, I hope you give this a try and find it useful.