July 10, 2011 at 12:35 am
Hi All,
I have 30 columns in one of the table. I want to find out the columns that are never been used...
Is there any possible way to display this information??
Thanks
July 10, 2011 at 5:20 am
It depends.
Is all the business logic implemented inside SQL Server (using stored procedures, views, functions and the like)?
If so, you could analyze the object definitions:
SELECT DISTINCT so.name, so.[object_id], so.type
FROM sys.objects so
JOIN sys.sql_modules sm ON sm.[object_id]=so.[object_id]
WHERE sm.definition LIKE '%<yourTable>%'
Once you have all objects referencing the table in question, you can check each object and the referenced columns.
Even though it's possible to find the column name that's not used in any of the objects, you'd still have to deal with the "false positive" issue, e.g. the column name exists in another table that is used in that object or the column name is iused in a comment.
Something like
-- MyTable.UnusedColumn is no longer required for this sproc
In this case the column UnusedColumn will be found inside the sproc and therefore won't be detected as an "obsolete candidate". Therefore, you'll have to analyze each referenced object.
But if there are 3rd party apps running SELECT statements directly against the source tables, you won't have a chance to find an unused column. Even a Profiler trace won't be able to capture it since this column might only be used once in a quarter or even once in a year.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply