SQL - How to find columns that have not been used

Lukus

Excellent
Good
Average
Bad
Terrible

Published on 2010-07-14 08:44 Number of Views: 1877

9 Comments

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

_{Tags :

sql}

9 Comments

tommy - 2010-07-14

Thanks :-)
- Reply

leonv - 2010-07-15

Nice one
- Reply

oscarferreira1 - 2010-07-15

Interesting

Question: How to subscribe to this, or other, article without haveing to comment?
- Reply

tommy - 2010-07-15

Originally Posted by oscarferreira1

Interesting

Question: How to subscribe to this, or other, article without haveing to comment?

Go here: http://www.sc-resources.net/forums/125-vBCms-Comments

Then in the dropdown menu Forum Tools select subscribe to this forum. Then you will get an email when a new article is published.
- Reply

bhutanigaurav - 2010-07-17

Tommy, this is what I get when I click on the link:

Invalid Forum specified. If you followed a valid link, please notify the administrator
- Reply

BethQ - 2010-07-23

This looks like it could be very helpful to us.

Question: Is this something that could be run against an Oracle table, or would I have to "translate it"? I only know as much sql as I have to, but the CREATE TABLE looks a little different them I'm used to.

Thanks!
- Reply

Lukus - 2010-07-26

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.
- Reply

BethQ - 2010-07-27

Thanks for the info, Lukus. I talked to our DBA and he's going to look at it. Thanks again for the info.
- Reply

AFR - 2011-08-04

Nice one Luke.
- Reply

Section Widget

Recent Article Comments Widget

New book by Mike Sanders: Service Manager 9 Tailoring Techniques

SQL - How to find columns that have not been used

Google search

Support ServiceCenter Resources - Make a donation