CODEWITHSUNDEEP
c#sqlsql-servervisual-studio
Emil Filip
Emil Filip

Reputation: 221

Clean SQL Server database from Visual Studio 2013 database project

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?

Upvotes: 2

Views: 254

Answers (2)

David Atkinson
David Atkinson

Reputation: 5899

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!

Upvotes: 1

zappa
zappa

Reputation: 910

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.

Upvotes: 1

Related Questions