Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How do I find in use db objects from all application pages is there any tool or trick I can use, just like we do search in Visual studio for an object in entire solution

share|improve this question
    
There is nothing in SQL Server that will tell you if something is or isn't used. Objects range from UDFs, SPs, Tables, Indexes, Statistics, etc. That's quite a lot of objects. Can you be more specific in your question? –  Sean Gallardy Jul 6 at 18:31
    
If I only talk about two type of Database objects, tables and Stored procedures –  AA.SC Jul 7 at 8:02

2 Answers 2

Even limited to just tables and stored procedures there isn't anything built in that will give you the information you are looking for. But there are things that will get you close.

Tables - run a query that will give you the table name and the row count, then wait and run it again. Stackoverflow Question - get all table names and row counts. But this will only tell you which tables are written to, not read.

Stored Procedures - You could query the execution plan cache and see which stored procedures are listed in it and compare that against a list of all stored procedures. But this will only tell you the ones that are in the cache.

SELECT *
, DatabaseName = DB_NAME(T.dbid)
, ObjectName = CASE WHEN T.objectid IS NOT NULL AND T.dbid > 0 THEN OBJECT_NAME(T.objectid, T.dbid) 
                    ELSE NULL 
                    END 

FROM sys.dm_exec_query_stats S CROSS APPLY sys.dm_exec_sql_text(S.sql_handle) T

share|improve this answer

The DMV columns sys.dm_db_index_usage_stats.last_user_*xxx* will tell you which tables were accessed since the instance last started, assuming you have a typical indexing strategy. Other DMVs will tell you which SPs are in use, but only while their plans are in cache; again, only since the instance started. If you have a relatively small application and large memory this may be the whole workload (unlikely though). sys.sysdepends can help tease out the links between various objects. Running a trace can help, too.

Many applications will have special-purpose routines which execute rarely. Examples are accountancy year-end or customer on-boarding. These will not show up in the caches or traces. Your life may become significantly more stressful should you delete them, however.

A trawl of your source code may be the only solution. Dynamic SQL is your nemesis here (another good reason to avoid it). Garbage programmables are not much overhead in the grand scheme of things. Large, unused tables you would want to remove for speed of restores. Even then you could move them to a different filegroup and have a piecemeal restore strategy. If you have reliable source control in place that can help. There are various tools out there which will compare two databases (say, production and UAT), listing differences. This will help distinguish application-required objects from ad hoc cruft.

Ultimately you can rename an object and wait to see what fails. It's not pretty, but it works.

share|improve this answer
    
Michael, let me work on that DMV then I will get back to you. –  AA.SC Sep 16 at 7:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.