This week I needed to quickly determine which database fields are being actively used or displayed within a report. We’re working with a client to map their source data into the K12 data warehouse model provided by Mariner. We wanted to cross-reference which fields are actively being used or not, for the purpose of determining how much work we should go to to populate particular fields that are difficult to retrieve or derive. Because our SSRS reports rely upon stored procedures in SQL Server to perform the queries, I was able to query the system tables.

Query Results

The results of the query shown below are 3 columns: the table, the column, and the procedure name or function name that uses the column.

Query

The query looks for both stored procedures and functions, since we often utilize functions underneath stored procedures (more info on that here: Using Table Valued Functions to Support SSRS Reporting).

Key components of the query are as follows:

The initial table variable returns the object name and the full text (i.e., the create statement). The full text is what’s searched in the last 2 lines of the where statement.
It is within the initial table variable that we specify to only search stored procedures or functions.
The final lines of the where statement searches the “ObjectText” from the table variable for the column name within it. This is where the magic happens (and prevents bad effects from the cross join we had to do).

USE EdDW;

GO

DECLARE @ObjectUsed TABLE

( ObjectName NVARCHAR(128)

,ObjectText NVARCHAR(MAX)

)

INSERT INTO @ObjectUsed

     SELECT DISTINCT

            SysObj.Name

          ,LTRIM(RTRIM(SysComm.[text])) AS [Text]

     FROM [sysobjects] AS SysObj

            INNER JOIN [syscomments] AS SysComm

                  ON SysObj.Id = SysComm.ID

                 AND SysObj.[Type] IN('P','TF','IF','FN')

---------------

SELECT DISTINCT

     SysTbls.name AS [Table Name]

    ,SysCols.name AS [Column Name]

    ,ObjectUsed.ObjectName AS ObjectName

FROM [sys].[tables] AS SysTbls

     LEFT JOIN [sys].[columns] AS SysCols

                ON SysTbls.[object_id] = SysCols.[object_id]

     LEFT JOIN [sys].[objects] as SysObj

               ON SysTbls.[object_id] = SysObj.[object_id]

    LEFT JOIN [sys].[types] AS SysTyp

               ON SysCols.user_type_id = SysTyp.[user_type_id]

     CROSS JOIN @ObjectUsed AS ObjectUsed

WHERE SysTbls.name IS NOT NULL

     AND SysCols.name IS NOT NULL

     AND ObjectText LIKE '%' + SysCols.name + '%'

    AND ObjectText LIKE '%' + SysTbls.name + '%'

ORDER BY

     SysTbls.name

    ,SysCols.name