How to find the Un-Used DB object using T-SQL

Question

How to find the Un-Used DB object using T-SQL

prabhu.st

SSCrazy

Points: 2552
More actions
August 1, 2014 at 12:24 am

#397150

Hi,
could someone of you please guide me how to find the un-used SPs in a database through T-SQL.
I have a list of SPs, 200 > , now I got a task to find the un-used DB objects (tables & SPs as of now), since the list is huge in size, I would like to get the details of its usage programatically.
please help me ASAP. all your inputs are really helpful to me.
Thanks,
Prabhu

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

You would need to put a trace on your database for a considerable amount of time to gather what queries are run against it, then you will have a rough idea as to what objects are used. I say rough as you might have processes which happen once a month, quarter, year, decade etc that you might not capture and if you drop them, them processes will no longer work.

The best thing personally would be to go through all the applications code that talks to the databases, finding what it calls from the applications perspective and then you have a better understanding of what objects the app needs to function correctly.

I would create a separate databases and move the objects into that, that are not being used, then you have a fall back to quickly recreate the object should it be used.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Stored procs:

Add a table which records usage. Add a line to each of the procedures which inserts into that table when called (or updates an existing row) to show when it was called. Run the system that way for an entire business cycle (make sure you catch month end and year end processes)

Tables:

Query sys.dm_dm_index_usage_stats, I'd say daily, but depends on how often the server is restarted. Persist the information into a table. Continue doing this for an entire business cycle, make sure you include month end and year end in the monitoring.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

prabhu.st SSCrazy Points: 2552 More actions · Answer 3

Hi Gail Shaw,

thanks for the info, i would to the needful regarding the tables, could you please explain little bit more for SPs..

@anthony.Green, thanks for your suggestion, but it would takes much effort and time as well...

Thanks,

Prabhu

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Create a logging table.

Add a line into every procedure which inserts or updates that table (your choice) to indicate is has been called.

A year or so later (an entire business cycle) query that table, it will tell you what procedures have been used. From that you can tell which ones haven't.

All methods take time. There's no magic 'what hasn't been used' query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

prabhu.st SSCrazy Points: 2552 More actions · Answer 5

thanks Gail..

I will do it and let you know, mean while I try to write a code as below to get the usage of tables.

--Query to Get Dependency of each table in the database.

Use Database

go

set nocount on

--select * from sys.dm_db_index_usage_stats

declare @TableName varchar(200), @i int , @C int

create table #TableUsage (TableName varchar(200), ProcedureName varchar(200))

select distinct identity(int,1,1) id, table_name into #Tables

from INFORMATION_SCHEMA.tables

select @I=min(id), @C=max(id) from #Tables

while @i<=@c

begin

select @TableName=table_name from #Tables where id=@i

insert into #TableUsage(TableName,ProcedureName)

select @TableName TableName,routine_name

from INFORMATION_SCHEMA.routines

where routine_type='PROCEDURE'

and substring(routine_definition,charindex(@TableName,routine_definition),len(@TableName))=@TableName

order by routine_name

print @i print @tablename

set @i+=1

end

go

select *

from #TableUsage

go

if object_id('tempdb..#TableUsage') is not null drop table #TableUsage

if object_id('tempdb..#Tables') is not null drop table #Tables

go

set nocount off

Thanks,

Prabhu

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Good luck with that.... You're going to likely have a large number of false positives to clear out, not to mention you don't know whether or not the procedures are being used.

What about jobs?

What about SSIS packages?

What about ad-hoc queries from the application?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 7

You also need to check Application code. It may have some ad-hoc queries that is executing stored procedures or using tables.

What about database objects used in Functions (table value or scalar)?

Check RedGate - SQL Search (free tool) - this may helpful to you to search db objects usage.

Thanks

prabhu.st SSCrazy Points: 2552 More actions · Answer 8

Hi Gail,

What about jobs?
What about SSIS packages?
What about ad-hoc queries from the application?

this DB is purely for Analysis and there is no jobs are applications using this one, thats the reason I choosed the above query. but the only one dependency is there few SSIS packages that I need to check with.

Thanks a ton for all your valuable replies.

Hi Hardy,

as there is no applications depends on this DB, there is a huge time saving .

Thanks,

Prabhu