2

We have a SQL Server database which has quite a few tables, stored procedures, views which are not in use. The database was recently imported into a Visual Studio database project. We are using ADO.NET.

What would be the quickest way to identify all of these?

I can use the code below to manually look for each table, view etc from SSMS:

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%putYouTextHere%'

or use "Find all references" on each item in visual studio's database project to check if it's used anywhere and then manually search for each table name, stored procedure name, view name etc to check if it's used anywhere in the solution.

Is there a better way to do it?

Improve this question
2
  • If you don't want to use any external tools (for example RedGate, DevArt etc.), so you have to do as described in question... Commented Apr 20, 2016 at 13:44
  • I might take into consideration using an external tool if it finds all the references from Visual Studio as well, not only from SQL Server. Commented Apr 20, 2016 at 13:47

2 Answers 2

Reset to default
1

There may be a better way but I usually use the SQL Search plugin from redgate: http://www.red-gate.com/products/sql-development/sql-search/ to see what database objects are being used. This will only show you references in the database of course not their references from visual studio solutions.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, but I really need to find all the references from Visual Studio as well, not only from SQL Server.
1

Even if you have a tool that identified the application code references to your objects, it wouldn't guarantee that these code paths are actually being used in reality.

A different approach would be to use code coverage reports, at the very least to get a feel for what might not be in use, but this works best if you have a comprehensive set of automated tests.

See this article for Visual Studio.

You can try SQL Cover to do the same for SQL Server procedures and functions.

If you don't have tests that cover enough of your application, maybe it's worth considering running this on UAT, or even your active production database (with caution) for a representative time. Of course you should make the necessary checks before deleting code you suspect isn't being used!

Improve this answer

1 Comment

Thanks a lot David

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.