MSSQL Database Cleanup - How do you find unused objects (Tables, Views, Procs, Functions)
The answer will depend a little on how the database has been put together, but my approach to a similar problem was 3-fold:
Figure out which objects have no internal dependencies. You can work this out from queries against sysdepends such as:
select id, namefrom sys.sysdepends sdinner join sys.sysobjects so on so.id = sd.idwhere not exists ( select 1 from sysdepends sd2 where sd2.depid = so.id )You should combine this with collecting the type of object (sysobjects.xtype) as you'll only want to isolate the tables, functions, stored procs and views. Also ignore any procedures starting "sp_", unless people have been creating procedures with those names for your application!
Many of the returned procedures may be your application's entry points. That is to say the procedures that are called from your application layer or from some other remote call and don't have any objects that depend on them within the database.
Assuming the process won't be too invasive (it will create some additional load, though not too much) you can now switch on some profiling of the SP:Starting, SQL:BatchStarting and / or SP:StmtStarting events. Run this for as long as you see fit, ideally logging into a sql table for easy cross referencing. You should be able to eliminate many of the procedures that are called directly from your application.
By cross referencing the text data from this log and your dependent object list you will hopefully have isolated most of the unused procedures.
Finally, you may want to take your candidate list resulting from this process and grep your sourcecode base against them. This is a cumbersome task and just because you find references in your code doesn't mean you need them! It may simply be that the code hasn't been removed though it's now logically inaccessible.
This is far from a perfect process. A relatively clean alternative is to set up far more detailed (and therefore invasive) profiling on the server to monitor all the activity. This can include every SQL statement called during the time the log is active. You can then work back through the dependent tables or even cross-database dependencies from this text data. I've found the reliability of the log detail (too many rows per second attempting to be parsed) and the sheer quanitity of data difficult to deal with. If your application is less likely to suffer from this then it may be a good approach.
Caveat:
Because, so far as I'm aware, there isn't a perfect answer to this be particularly wary of removing tables. Procedures, functions and views are easily replaced if something goes wrong (though make sure you have them in source control before burning them of course!). If you're feeling really nervous why not rename the table and create a view with the old name, you've then got an easy out.
We can also find unused columns and table using following query. I tired to write cursor. Cursor will give you information aboout each column n each table.
declare @name varchar(200), @id bigint, @columnname varchar(500)declare @temptable table( table_name varchar(500), Status bit)declare @temp_column_name table ( table_name varchar(500), column_name varchar(500), Status bit)declare find_table_dependency cursor forselect name, id from sysobjects where xtype ='U'open find_table_dependencyfetch find_table_dependency into @name, @idwhile @@fetch_Status = 0begin if exists(select top 1 name from sysobjects where id in (select id from syscomments where text like '%'+@name +'%')) insert into @temptable select @name, 1 else insert into @temptable select @name, 0 declare find_column_dependency cursor for select name from syscolumns where id = @id open find_column_dependency fetch find_column_dependency into @columnname while @@fetch_Status = 0 begin if exists(select top 1 name from sysobjects where id in (select id from syscomments where text like '%'+@columnname +'%')) insert into @temp_column_name select @name,@columnname, 1 else insert into @temp_column_name select @name,@columnname, 0 fetch find_column_dependency into @columnname end close find_column_dependency deallocate find_column_dependency fetch find_table_dependency into @name, @idendclose find_table_dependencydeallocate find_table_dependencyselect * from @temptableselect * from @temp_column_name