Announcement

Collapse
No announcement yet.

SQL - How to find columns that have not been used

Collapse
X
Collapse
  •  

  • SQL - How to find columns that have not been used

    Have you ever been asked to add a new column to a table and wondered if there is a column already there that is not being used?

    I have come across this a few times and not been able to find anything that answers my question.
    So i have written some T-SQL code that looks over your table and asks if any column contains only NULL values.
    This will help to decide whether you can use an existing column, instead of expanding your schema.

    Note: Of course, you should always test using an existing field thoroughly in a test environment to make sure there are no adverse effects.

    Firstly you need a table to store your results, so select your test SM7 database and open a new query window:

    Code:
        CREATE TABLE temp2 (
        [columnName] [varchar](50) PRIMARY KEY NOT NULL,
        [type] [varchar](50) NULL,
        [length] [int] NULL)
    Then you can run this code to populate the table:

    Code:
    Set nocount on
    
    DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT [name], [xtype], [length] FROM syscolumns WHERE id=OBJECT_ID('dbo.CM3RM1')
    OPEN crs
    DECLARE @name sysname
    DECLARE @xtype int
    DECLARE @length int
    
    FETCH NEXT FROM crs INTO @name, @xtype, @length
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO temp2 ([columnName],[type],[length])
            EXEC('SELECT ''' + @name + ''',(select [name] from sys.systypes where xtype = ' + @xtype + '),' + @length + ' WHERE NOT EXISTS (SELECT * FROM dbo.CM3RM1 WHERE [' + @name + '] IS NOT NULL)');
    
        FETCH NEXT FROM crs INTO @name, @xtype, @length
    END
    CLOSE crs
    DEALLOCATE crs
    Note: i've queried dbo.CM3RM1 (one of the change module tables) in this example, you can replace dbo.CM3RM1 with any table you like. You need to replace it in two places in the above script.

    Now you can review the results anytime by running:

    Code:
    SELECT *
    FROM temp2
    Hope this is useful!
    Cheers,
    Lukus

    • Lukus
      #7
      Lukus commented
      Editing a comment
      Sorry BethQ, i don't have any Oracle experience, but yeah, i'd assume there would be some translations needed.
      i'm not sure how the system tables work in Oracle either, so where i'm getting the type of field and it's size probably won't work in Oracle.

    • BethQ
      #8
      BethQ commented
      Editing a comment
      Thanks for the info, Lukus. I talked to our DBA and he's going to look at it. Thanks again for the info.

    • AFR
      #9
      AFR commented
      Editing a comment
      Nice one Luke.
    Posting comments is disabled.

Categories

Collapse

Article Tags

Collapse

Latest Articles

Collapse

Working...
X