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.
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.
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).
DECLARE @ObjectUsed TABLE
( ObjectName NVARCHAR(128)
INSERT INTO @ObjectUsed
,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')
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 + '%'