Lets say you have inherited a MS SQL 2000 or 2005 database, and you know that some of the Tables, Views, Procs, and Functions are not actually used in the final product.
Is there some kind of internal logging or another mechanism that could tell me what objects are NOT being called? or have only been called a few times versus thousands of times.
This SO question, Identifying Unused Objects In Microsoft SQL Server 2005, might be relevant.
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,
name
from
sys.sysdepends sd
inner join sys.sysobjects so
on so.id = sd.id
where
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 for
select name, id from sysobjects where xtype ='U'
open find_table_dependency
fetch find_table_dependency into @name, @id
while @@fetch_Status = 0
begin
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, @id
end
close find_table_dependency
deallocate find_table_dependency
select * from @temptable
select * from @temp_column_name
