Tuesday, June 21, 2011

Get usage for all columns in a SQL Server database

First if you just want to know how one column is used you want to use the UI or check out my other entry. If however you want to scan your entire database and determine the dependencies (usage) of each and every column in the database then stay here. The code found in either of these places will only look at the database references (stored procedures, functions, views, triggers). This means if you are using something like SSIS, LINQ, Entity Framework, embedded SQL in your code, etc you will need to check these area on your own.

With that said, I really just continued the thought from my other entry (same as the one I noted above). The first step is to convert the stored procedure used there to one that just dumps the data to a table for later querying. I call this table ColumnUsage. I also add a TagName to the table so that you can easily identify your results among other results in that table. That way you can compare results over time, or just support multiple users. The table will be created the first time the stored procedure is executed.

Here is the new stored proc

/****** Object:  StoredProcedure [dbo].[UTL_07_WriteColumnUsageToColumnUsageTable]    Script Date: 06/21/2011 15:25:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[UTL_07_WriteColumnUsageToColumnUsageTable]
    @vcTableName varchar(100),
    @vcColumnName varchar(100),
    @tagName varchar(100)
AS
/************************************************************************************************
DESCRIPTION:    writes all stored procedures, views, triggers
        and user-defined functions that reference the
        table/column passed into this proc into the ColumnUsage table.
   
PARAMETERS:
        @vcTableName - table containing searched column
        @vcColumnName    - column being searched for
        @tagName - a name you make up to so you can later query for your results

        This procedure must be installed in the database where it will
        be run due to it's use of database system tables.

USAGE:   
  
  UTL_07_WriteColumnUsageToColumnUsageTable 'schema.tablename', 'columnname', 'tagName'
   
AUTHOR:    Karen Gayda

DATE: 07/19/2007

MODIFICATION HISTORY:
WHO        DATE        DESCRIPTION
---        ----------    -------------------------------------------
Brent Vermilion    06.21.2011    Recreated such that writes to a table instead of output
*************************************************************************************************/
SET NOCOUNT ON

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'ColumnUsage'))
BEGIN
    CREATE TABLE dbo.ColumnUsage
        (
        ID int NOT NULL IDENTITY (1, 1),
        TableName varchar(500) NOT NULL,
        ColumnName varchar(500) NOT NULL,
        UsedByType varchar(50) NOT NULL,
        UsedByName varchar(2000) NOT NULL,
        TagName varchar(100) NOT NULL
        )  ON [PRIMARY]

    ALTER TABLE dbo.ColumnUsage ADD CONSTRAINT
        PK_ColumnUsage PRIMARY KEY CLUSTERED
        (
        ID
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

END

insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'Stored Procedure', @tagName, SUBSTRING(o.NAME,1,60) AS [Procedure Name]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE = 'P'
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
    ORDER BY  [Procedure Name]


insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'View', @tagName, SUBSTRING(o.NAME,1,60) AS [View Name]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE = 'V'
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'             
    ORDER BY  [View Name]


insert into dbo.ColumnUsage(TableName, ColumnName, TagName, UsedByName, UsedByType)
SELECT DISTINCT @vcTableName, @vcColumnName, @tagName, SUBSTRING(o.NAME,1,60) AS [Function Name],
        CASE WHEN o.XTYPE = 'FN' THEN 'Scalar Function'
            WHEN o.XTYPE = 'IF' THEN 'Inline Function'
            WHEN o.XTYPE = 'TF' THEN 'Table Function'
            ELSE '? Function'
        END
        as [Function Type]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE IN ('FN','IF','TF')
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
    ORDER BY  [Function Name]


insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'Trigger', @tagName, SUBSTRING(o.NAME,1,60) AS [Trigger Name]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE = 'TR'
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'     
    ORDER BY  [Trigger Name]

Now that we have that we can write another stored proc that will loop over every column in our database and call the above stored procedure for each column. Please note, depending on how complex your schema, the number of stored procedures, number of columns, etc this query could take quite a long time. Here is the stored proc to scan your database.


/****** Object:  StoredProcedure [dbo].[UTL_08_WriteAllColumnUsageToColumnUsageTable]    Script Date: 06/21/2011 15:25:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[UTL_08_WriteAllColumnUsageToColumnUsageTable]
    @TagName varchar(100)
AS
/************************************************************************************************
DESCRIPTION:    writes all stored procedures, views, triggers
        and user-defined functions that reference the
        table/column passed into this proc into the ColumnUsage table.
   
PARAMETERS:
        @TagName - a name you make up to so you can later query for your results

        This procedure must be installed in the database where it will
        be run due to it's use of database system tables.

USAGE:   
  
  UTL_08_WriteAllColumnUsageToColumnUsageTable 'tagName'
   
AUTHOR:    Brent Vermilion

DATE: 06/21/2011
*************************************************************************************************/

Declare @TableName as nvarchar(512)
Declare @ColumnName as nvarchar(300)

Declare ColumnCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME

OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor
into @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- do row specific stuff here
    exec UTL_07_WriteColumnUsageToColumnUsageTable @TableName, @ColumnName, @TagName

    FETCH NEXT FROM ColumnCursor
    into @TableName, @ColumnName
END

CLOSE ColumnCursor
DEALLOCATE ColumnCursor

After executing the either of the stored procedure on your database you can answer some very useful questions.

  1. What columns are not being used?
  2. select distinct allcols.TABLE_SCHEMA + '.' + allcols.TABLE_NAME, COLUMN_NAME, UsedByName from
    INFORMATION_SCHEMA.COLUMNS allcols
    left outer join ColumnUsage usage
    on (allcols.TABLE_SCHEMA + '.' + allcols.TABLE_NAME = usage.TableName and allcols.COLUMN_NAME = usage.ColumnName)
    where UsedByName is null

  3. What is using a particular column?
    select * from ColumnUsage where TableName = 'MyTableHere' and ColumnName = 'MyColumnHere'

No comments: