Getting unused table columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting unused table columns

Author

Topic

beaker_
Starting Member

3 Posts

Posted - 2013-05-15 : 04:35:44

Hi All

I have been searching all over for at solution to this one, and I'm sure that I found a solution once, but I have lost it :(

I have a SQL Server 2008 database with x Tables and Y Stored Procedures and I'm in the process of doing some spring cleanup...

I need to identify all the table columns that are NOT used by any of the Stored Procedures...

Is there a way to do this?

Thanks in advance
Allan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 04:52:59

http://sqlservergeeks.com/blogs/riteshmedhe/sql-server-bi/422/sql-server-identify-unused-tables-indexes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

beaker_
Starting Member

3 Posts

Posted - 2013-05-15 : 04:58:00

I'm sorry but this only gives me unsused tables

/
Allan

quote:
Originally posted by visakh16

http://sqlservergeeks.com/blogs/riteshmedhe/sql-server-bi/422/sql-server-identify-unused-tables-indexes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 05:08:44

oh ok in that case make it like this


SELECT table_name
from information_schema.tables t
outer apply (select count(*) as cnt
             from sys.sql_modules m
             join sys.objects o
             on o.object_id = m.object_id
             where o.type_desc ='SQL_STORED_PROCEDURE'
             and m.definition like '% ' + t.table_name + ' %'
            )t1
where coalesce(t1.cnt,0) = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

beaker_
Starting Member

3 Posts

Posted - 2013-05-15 : 05:24:11

Hi Thanks for reply.

I'm not able to completely understand this query, but as far as I see it still only gives me Table names :)

I tried to execute it, but it draws all life out of the SQl server so I had to cancel it before I got the result...

quote:
Originally posted by visakh16

oh ok in that case make it like this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 05:34:03

quote:
Originally posted by beaker_

Hi Thanks for reply.

I'm not able to completely understand this query, but as far as I see it still only gives me Table names :)

I tried to execute it, but it draws all life out of the SQl server so I had to cancel it before I got the result...

quote:
Originally posted by visakh16

oh ok in that case make it like this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

you'll get table details from the given query

then you need to join it to columns view to get the columns

you can try this alternative too if you're interested in columns


SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE NOT EXISTS (SELECT 1
                  FROM sys.sql_modules m
                  INNER JOIN sys.objects o
                  ON o.object_id = m.object_id
                  WHERE o.type_desc ='SQL_STORED_PROCEDURE'
                  AND m.definition like '% ' + c.TABLE_NAME + ' %'
                 )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-05-15 : 15:07:55

SQL provides the view:

sys.sql_expression_dependencies

to help with that, but it is not guaranteed to be 100% accurate.

And of course if dynamic SQL is used, there's no way to know what tables or columns it will reference.

Finally, you can search the code itself for a reference to the column name:

SELECT *
FROM sys.sql_modules
WHERE definition LIKE N'%[^a-z0-9_]your_column_name_here[^a-z0-9_]%'

but naturally you will have to review those results to verify the matches.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources