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

  • 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

  • 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.

  • 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
  • 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

  • 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
  • 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

  • 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
  • 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

  • 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

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

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