Advanced Analysis with Information Schema Views

This is a follow up to the Intro to Information Schema Views that can be found hereNow 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. 1.     Column Usage. Basic list of where the column is used.
  2. 2.     Table Constraints. Is the column used in any type of constraint?
  3. 3.     Referential Constraints. Lists the referenced table for foreign keys.
  4. 4.     View Usage. Lists views that reference the column.
  5. 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.