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
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
FROM sys.dm_exec_query_stats S CROSS APPLY sys.dm_exec_sql_text(S.sql_handle) T
The DMV columns
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.