SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Getting unused table columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

beaker_
Starting Member

Denmark
3 Posts

Posted - 05/15/2013 :  04:35:44  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/15/2013 :  04:52:59  Show Profile  Reply with Quote
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
Go to Top of Page

beaker_
Starting Member

Denmark
3 Posts

Posted - 05/15/2013 :  04:58:00  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/15/2013 :  05:08:44  Show Profile  Reply with Quote
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
Go to Top of Page

beaker_
Starting Member

Denmark
3 Posts

Posted - 05/15/2013 :  05:24:11  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/15/2013 :  05:34:03  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
425 Posts

Posted - 05/15/2013 :  15:07:55  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000