To get a list of un-used columns in all tables in a database

  • I am looking to get a list of un-used columns in all tables in a database.  I would like the output to look like something like this.

    DatabaseName, SchemaName, TableName, ColumnName, etc

    If anyone has a handy script or any link, please share.  Thanks.

    Regards,
    SQLisAwe5oMe.

  • And how do you define "un-used", what are your criteria for this?

  • My understanding from app team is, historically they created additional columns for future use, but never used....so, I am doing this as part of a clean up process where I need to identify any columns that was never used and then have them reviewed it before deleting them.

    Hope it makes sense.

    Regards,
    SQLisAwe5oMe.

  • Is there a reason the app team can't tell you which columns are unused?

    You could use server-side trace or extended events to log executions over a period long enough to make you comfortable they aren't used (watch out for end-of-month- & end-of-year-only activity) , & compare that history to existing procedures.

    This DMV approach from https://dba.stackexchange.com/questions/47025/identifying-unused-stored-procedures will only reflect history since the instance was restarted/data was flushed:

    SELECT p.name AS 'SP Name' -- Get list of all SPs in the current database
    FROM sys.procedures AS p
    WHERE p.is_ms_shipped = 0

    EXCEPT

    SELECT p.name AS 'SP Name' -- Get list of all SPs from the current database
    FROM sys.procedures AS p -- that are in the procedure cache
    INNER JOIN sys.dm_exec_procedure_stats AS qs
    ON p.object_id = qs.object_id
    WHERE p.is_ms_shipped = 0;
  • Are they looking for columns that are entirely null? or not null, but entirely populated by a default value?Or columns that are not referenced by code?

     

  • Here is a sample script..... to get column usage metrics.

    Replace DBNAME with your database name.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE procedure [dbo].[usp_Capture_TableColumnUsageMetrics] (@TableName nvarchar(100))
    as
    begin

    SET NOCOUNT ON

    /*Declare variables */
    DECLARE @DatabaseName NVARCHAR(255) = N'DBName'
    DECLARE @SchemaName NVARCHAR(255) = N'dbo'
    DECLARE @ColumnType nvarchar(15)

    DECLARE @SQLString NVARCHAR(MAX) = N''
    DECLARE @ParamDefinition NVARCHAR(MAX) = N''
    DECLARE @ParamDefinition1 NVARCHAR(MAX) = N''

    DROP TABLE IF EXISTS #ColumnList

    /* Declare a temp table with required metrics for to capture count(s) and percentages to check overall usage */
    CREATE TABLE #ColumnList (
    ColumnId INT IDENTITY (1, 1)
    ,ColumnName NVARCHAR(255)
    ,ColumnNullCount INT
    ,ColumnNullPercentage NUMERIC(10, 2)
    ,ColumnSingleSpaceCount INT
    ,ColumnSingleSpacePercentage NUMERIC(10, 2)
    ,ColumnMinusOneCount INT
    ,ColumnMinusOnePercentage NUMERIC(10, 2)
    ,ColumnDefaultDateCount INT
    ,ColumnDefaultDatePercentage NUMERIC(10, 2)
    )
    /* To get the row count in a table to calculate what percentage the data is filled */
    DECLARE @TableRecordCount INT

    /* To get the No. of columns in a table */
    DECLARE @ColumnCount INT = 0

    /* declare variables to capture metrics */
    DECLARE @ColumnNullCount INT
    DECLARE @ColumnSingleSpaceCount INT
    DECLARE @ColumnMinusOneCount INT
    DECLARE @ColumnDefaultDateCount INT
    DECLARE @SQLColumnType NVARCHAR(1005)

    DECLARE @LoopCounter INT = 1
    DECLARE @ColumnName NVARCHAR(255)

    /* Generate a dynamic SQL to get the row count of a table */
    SET @SQLString = N'SELECT @TableRecordCount = COUNT(*) FROM '
    + @DatabaseName + N'.' + @SchemaName + N'.' + @TableName
    SET @ParamDefinition = N'@TableRecordCount INT OUTPUT'
    EXECUTE sp_executesql @SQLString ,@ParamDefinition ,@TableRecordCount OUTPUT

    /*Capture all the column names from a table */
    SET @SQLString = N'SELECT COLUMN_NAME FROM '
    + @DatabaseName + N'.' + N'INFORMATION_SCHEMA.COLUMNS
    WHERE
    TABLE_SCHEMA = @SchemaName
    AND TABLE_NAME = @TableName'

    SET @ParamDefinition = N'@SchemaName NVARCHAR(255), @TableName NVARCHAR(255)'

    /*Insert all the columns in table */
    INSERT INTO #ColumnList (ColumnName)
    EXECUTE sp_executesql @SQLString ,@ParamDefinition ,@SchemaName ,@TableName

    SELECT
    @ColumnCount = COUNT(*)
    FROM #ColumnList

    WHILE (@LoopCounter <= @ColumnCount)
    BEGIN


    SELECT
    @ColumnName = ColumnName
    FROM #ColumnList
    WHERE ColumnId = @LoopCounter

    /* Check For NULL Count */
    SET @SQLString =
    N'SELECT @ColumnNullCount = COUNT(*) FROM '
    + @DatabaseName + N'.' + @SchemaName + N'.' + @TableName
    + N' WHERE ' + @ColumnName + N' IS NULL'

    SET @ParamDefinition = N'@ColumnNullCount INT OUTPUT'

    EXECUTE sp_executesql @SQLString ,@ParamDefinition,@ColumnNullCount OUTPUT
    /* ColumnNullCount and percentage */
    UPDATE #ColumnList
    SET ColumnNullCount = @ColumnNullCount
    ,ColumnNullPercentage = CASE WHEN @TableRecordCount <> 0 then
    @ColumnNullCount * 100.0 / @TableRecordCount
    ELSE 0 END
    WHERE ColumnId = @LoopCounter

    /*Get data type for the selected column */
    SET @SQLColumnType = N'SELECT @ColumnType = DATA_TYPE
    from DBNAME.INFORMATION_SCHEMA.COLUMNS C
    inner join DBNAME.INFORMATION_SCHEMA.TABLES T on c.TABLE_NAME = T.TABLE_NAME
    and T.TABLE_NAME = ''' + @TableName + ''' and C.COLUMN_NAME = ''' + @ColumnName + ''' '

    SET @ParamDefinition1 = N'@ColumnType nvarchar(20) OUTPUT'

    EXEC sp_executesql @SQLColumnType ,@ParamDefinition1,@ColumnType OUTPUT

    IF @ColumnType IN ('nvarchar', 'nchar', 'char', 'varchar')
    BEGIN

    /* Check and update single space Count */
    SET @SQLString = N'SELECT @ColumnSingleSpaceCount = COUNT(*) FROM '
    + @DatabaseName + N'.' + @SchemaName + N'.' + @TableName
    + N' WHERE ' + @ColumnName + N' ='' '' '

    SET @ParamDefinition = N'@ColumnSingleSpaceCount INT OUTPUT'

    EXECUTE sp_executesql @SQLString ,@ParamDefinition ,@ColumnSingleSpaceCount OUTPUT

    UPDATE #ColumnList
    SET ColumnSingleSpaceCount = @ColumnSingleSpaceCount
    ,ColumnSingleSpacePercentage = CASE WHEN @TableRecordCount <> 0 then
    @ColumnSingleSpaceCount * 100.0 / @TableRecordCount
    ELSE 0 END
    WHERE ColumnId = @LoopCounter
    END


    IF @ColumnType IN ('int','bit')
    BEGIN
    /* Check for negative values and update the table */
    SET @SQLString = N'SELECT @ColumnMinusOneCount = COUNT(*) FROM '
    + @DatabaseName + N'.' + @SchemaName + N'.' + @TableName
    + N' WHERE ' + @ColumnName + N' < 0 '

    SET @ParamDefinition = N'@ColumnMinusOneCount INT OUTPUT'

    EXECUTE sp_executesql @SQLString ,@ParamDefinition ,@ColumnMinusOneCount OUTPUT

    UPDATE #ColumnList
    SET ColumnMinusOneCount = @ColumnMinusOneCount
    ,ColumnMinusOnePercentage = CASE WHEN @TableRecordCount <> 0 then
    @ColumnMinusOneCount * 100.0 / @TableRecordCount
    ELSE 0 END
    WHERE ColumnId = @LoopCounter
    END

    IF @ColumnType IN ('datetime', 'datetime2')
    BEGIN
    /* Check for default date values like 01-01-1900 and update the table */
    SET @SQLString = N'SELECT @ColumnDefaultDateCount = COUNT(*) FROM '
    + @DatabaseName + N'.' + @SchemaName + N'.' + @TableName
    + N' WHERE ' + @ColumnName + N' = ''01-01-1900'' '

    SET @ParamDefinition = N'@ColumnDefaultDateCount INT OUTPUT'

    EXECUTE sp_executesql @SQLString ,@ParamDefinition ,@ColumnDefaultDateCount OUTPUT

    UPDATE #ColumnList
    SET ColumnDefaultDateCount = @ColumnDefaultDateCount
    ,ColumnDefaultDatePercentage = CASE WHEN @TableRecordCount <> 0 then
    @ColumnDefaultDateCount * 100.0 / @TableRecordCount
    ELSE 0 END
    WHERE ColumnId = @LoopCounter
    END

    SET @LoopCounter += 1
    END

    /* Insert the data into a table */
    SELECT
    @TableName AS [TableName]
    ,ColumnName AS [TableColumnName]
    ,@TableRecordCount AS [TableRowCount]
    ,ColumnNullCount AS [ColumnNullCount]
    ,ColumnNullPercentage AS [ColumnNullPercentage]
    ,ColumnSingleSpaceCount AS [ColumnSingleSpaceCount]
    ,ColumnSingleSpacePercentage AS [ColumnSingleSpacePercentage]
    ,ColumnMinusOneCount AS [ColumnMinusOneCount]
    ,ColumnMinusOnePercentage AS [ColumnMinusOnePercentage]
    ,ColumnDefaultDateCount AS [ColumnDefaultDateCount]
    ,ColumnDefaultDatePercentage AS [ColumnDefaultDatePercentage]
    ,(@TableRecordCount - (ISNULL(ColumnNullCount, 0) + ISNULL(ColumnSingleSpaceCount, 0) +
    ISNULL(ColumnMinusOneCount, 0) + ISNULL(ColumnDefaultDateCount, 0))) AS [TotalColumnFilledCount]
    INTO [dbo].[TableUsageMetrics]
    FROM #ColumnList
    ORDER BY [ColumnName]

    END

    =======================================================================

  • ratbak wrote:

    Is there a reason the app team can't tell you which columns are unused?

    I'm glad someone else said that.  If they're the ones that are going to review the findings, it must mean that they have a way to confirm it so let them use that method to find the columns to begin with.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I don't believe we allow for null in our data.  I would image it would be any that have all spaces(any character fields with the default space).

    Regards,
    SQLisAwe5oMe.

  • It could also have a default like GETDATE() or some other non-null, non-blank fixed value.  That's why I'm thinking that the folks that asked the question are the ones best equipped to answer the question.  Have THEM produce a list of all the columns that they know are good and then change the names of all the other columns to "XX" + the original column name as a "scream-test".  It beats the hell out of actually dropping an "unused" column that's actually used by something somewhere.  After at least one quarterly cycle has passed, then you're probably safe to actually drop the columns remaining that start with "XX".

    Don't forget that, at the very least, you're going to need to rebuild Heaps and Clustered Indexes to recover the space after the column drops.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff, yes, this is our plan....identify the columns first then rename it and monitor for sometime and if still not being used.....drop them as part of clean up.

    Regards,
    SQLisAwe5oMe.

  • Hi Emperor100, Can you plz explain this SP as to how it works....Do I need the pass the @TableName as value or DB name?

    Regards,
    SQLisAwe5oMe.

  • SQLisAwe5oMe wrote:

    Hi Emperor100, Can you plz explain this SP as to how it works....Do I need the pass the @TableName as value or DB name?

    Before creating the SP in a database,  Set the two variables @DatabaseName and @SchemaName to your custom names and then create the SP.   During the SP call, pass on the TableName.

    It loops through all the columns of a table, checks the data type (int, char/varchar, date) and captures the following and inserts into a temporary table and outputs the results:

    • Table row count
    • Nulls row count and its percentage.
    • Single space row count and its percentage.
    • Defaults row count and its percentage.
    • -1 (minus 1) or any other value row count

      Hope that helps!!

    =======================================================================

  • Thank you Emperor100 for details.

    Regards,
    SQLisAwe5oMe.

  • I got the below script to find out unused columns in a database and the output looks good to me.....can someone help in adding 'DatabaseName' to this script?

    DatabaseName, TableSchema, TableName and ColumnName

    If object_id('tempdb..#Results') is not null

    Drop Table #Results;

    GO

    Create Table #Results

    (

    TableSchema sysname not null

    , TableName sysname not null

    , ColumnName sysname not null

    );

    GO

    Declare @TableSchema sysname;

    Declare @TableName sysname;

    Declare @ColumnName sysname;

    Declare @DataType sysname;

    Declare @Columns Cursor;

    Declare @BaseSql nvarchar(max);

    Declare @Sql nvarchar(max);

    Declare @AdditionalFilter nvarchar(max);

    Set @BaseSql = 'Insert #Results( TableSchema, TableName, ColumnName )

    Select ''TABLE_SCHEMA'', ''TABLE_NAME'', ''COLUMN_NAME''

    From ( Select 1 As V ) As Z

    Where Exists (

    Select 1

    From [TABLE_SCHEMA].[TABLE_NAME]

    )

    And Not Exists (

    Select 1

    From [TABLE_SCHEMA].[TABLE_NAME]

    Where [COLUMN_NAME] Is Not Null

    ADDITIONAL_FILTER

    )';

    Set @Columns = Cursor Fast_Forward For

    Select C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE

    From INFORMATION_SCHEMA.COLUMNS As C

    Left Join INFORMATION_SCHEMA.VIEWS As V

    On V.TABLE_SCHEMA = C.TABLE_SCHEMA

    And V.TABLE_NAME = C.TABLE_NAME

    Where V.TABLE_NAME Is Null;

    Open @Columns;

    Fetch Next From @Columns Into @TableSchema, @TableName, @ColumnName, @DataType;

    While @@Fetch_Status = 0

    Begin

    If @DataType In('int','smallint','tinyint','bigint','numeric','bit','decimal','money','smallmoney','float','real')

    Set @AdditionalFilter = 'And [COLUMN_NAME] <> 0';

    Else If @DataType In('char','nchar','varchar','nvarchar','text','ntext')

    Set @AdditionalFilter = 'And Len([COLUMN_NAME]) > 0';

    Else

    Set @AdditionalFilter = '';

    Set @Sql = Replace(@BaseSql, 'ADDITIONAL_FILTER', @AdditionalFilter);

    Set @Sql = Replace(@Sql, 'TABLE_SCHEMA', @TableSchema);

    Set @Sql = Replace(@Sql, 'TABLE_NAME', @TableName);

    Set @Sql = Replace(@Sql, 'COLUMN_NAME', @ColumnName);

    --Print @Sql

    Exec(@Sql)

    Fetch Next From @Columns Into @TableSchema, @TableName, @ColumnName, @DataType;

    End

    Close @Columns;

    Deallocate @Columns;

    Select *

    From #Results

    Regards,
    SQLisAwe5oMe.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply