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 />
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!