Cool script to document table column usage in SQL 2005

Hey,

While running the Microsoft course “2784 – Tuning and Optimizing Queries using SQL Server 2005″ I ran into a need to list all table columns that were referenced by the various code objects (Procs, View, etc.) in the database.

After some poking around I decided to use a few features of SQL Server 2005 to get my results; The sys.sql_dependencies system catalog view and the PIVOT statement.

The result I desired would show every column in every table that is referenced by a programming object, and document the usage (Select or Update) of the column by the programming object. For Example:

Table    Column     pInsertEmployee  pUpdateEmployee vOrders<br />Employee EmployeeID U                U               S<br />Employee DateAdded  U    <br />Orders   EmployeeID                                  S<br />

Using the sys.sql_depencies system catalog view, the PIVOT operator and some dynamic sql code generation I was able to get what I needed. Following is the script I used.

-- =============================================================================<br />-- Title: SQL Server 2005 Column Usage<br />-- Author: Bret Stateham<br />-- bret@pingit.biz<br />-- Created: 08/01/07<br />-- Description: Sample script to show table column usage by server objects<br />-- =============================================================================<br />SET NOCOUNT ON<br /><br />IF OBJECT_ID('TempDB..#Dependencies') IS NOT NULL<br />DROP TABLE #Dependencies<br /><br />CREATE TABLE #Dependencies<br />(<br />  ReferencingObject nvarchar(256),<br />  ReferencingColumn nvarchar(256),<br />  ReferencedObject nvarchar(256),<br />  ReferencedColumn nvarchar(256),<br />  Usage nchar(256)<br />)<br /><br />INSERT INTO #Dependencies (ReferencingObject,ReferencingColumn,ReferencedObject,ReferencedColumn,Usage)<br />SELECT<br />  object_name(object_id) AS ReferencingObject<br />  ,IsNull<br />   (<br />    (<br />     SELECT name <br />     FROM sys.columns AS c <br />     WHERE c.object_id = d.object_id <br />       AND c.column_id = d.column_id<br />    )<br />    ,''<br />   ) AS ReferencingColumn<br />  ,OBJECT_NAME(referenced_major_id) AS ReferencedObject<br />  ,ISNULL<br />   (<br />    (<br />     SELECT name <br />     FROM sys.columns AS c <br />     WHERE c.object_id = d.referenced_major_id <br />       AND c.column_id = d.referenced_minor_id<br />    )<br />    ,''<br />   ) AS ReferencedColumn<br />  ,CASE<br />    WHEN is_selected = 1 and is_updated = 1 THEN 'SU'<br />    WHEN is_selected = 1 and is_updated = 0 THEN 'S'<br />    WHEN is_selected = 0 and is_updated = 1 THEN 'U'<br />    WHEN is_selected = 0 and is_updated = 0 THEN ''<br />   END AS Usage<br />FROM sys.sql_dependencies AS d<br /><br />DECLARE @PivotStatement nvarchar(max)<br />DECLARE @PivotColumns nvarchar(max)<br />DECLARE @SelectColumns nvarchar(max)<br /><br />SET @PivotStatement = ''<br />SET @PivotColumns = NULL<br />SET @SelectColumns = ''<br /><br />SELECT @PivotColumns = <br />  COALESCE(@PivotColumns + ',[' + Referencing + ']','[' + Referencing + ']')<br />FROM<br />(<br />  SELECT DISTINCT<br />    CASE<br />      WHEN ReferencingColumn <> '' THEN<br />        ReferencingObject + '.' + ReferencingColumn<br />      ELSE<br />        ReferencingObject<br />    END AS Referencing<br />  FROM #Dependencies<br />) AS DistinctReferencing<br /><br />SELECT <br />  @SelectColumns = ISNULL(@SelectColumns,'') + <br />                   ' ,ISNULL([' + Referencing + '],'''') AS [' + Referencing + ']' + CHAR(13) + CHAR(10)<br />FROM<br />(<br />  SELECT DISTINCT<br />    CASE<br />      WHEN ReferencingColumn <> '' THEN<br />        ReferencingObject + '.' + ReferencingColumn<br />      ELSE<br />        ReferencingObject<br />    END AS Referencing<br />  FROM #Dependencies<br />) AS DistinctReferencing<br /><br />SET @PivotStatement = '<br />  SELECT<br />    ReferencedObject<br />    ,ReferencedColumn<br />' + @SelectColumns + '<br />    FROM<br />    (<br />      SELECT<br />        ReferencedObject<br />        ,ReferencedColumn<br />        ,CASE<br />           WHEN ReferencingColumn <> '''' THEN<br />             ReferencingObject + ''.'' + ReferencingColumn<br />           ELSE<br />             ReferencingObject<br />         END AS Referencing<br />        ,Usage<br />      FROM #Dependencies<br />    ) AS d<br />    PIVOT<br />    (<br />      MAX(Usage)<br />      FOR Referencing IN (' + @PivotColumns + ')<br />    ) AS PivotedDependencies<br />    ORDER BY ReferencedObject, ReferencedColumn<br />'<br /><br />EXEC (@PivotStatement)<br /><br />DROP TABLE #Dependencies<br />SET NOCOUNT OFF<br />

One Response

  • This is amazing, exactly what i needed. I inherited a progject that linked 2 database. I had a vlue getting changed somehow and couldn't figure itout. I used this to find where that column was bineg "U"'d. THAKNYOU!

Leave a Reply