CODEWITHSUNDEEP
sql-servert-sqlpowershellsql-server-2012
Question3CPO
Question3CPO

Reputation: 1202

Finding Obsolete Code For SQL Server 2012 Upgrade

I'm currently trying to find obsolete or deprecated code on a SQL Server 2008 R2 in stored procedures, views, triggers, etc. I can think of two different approaches: use TSQL and search through the objects in syscomments (what is the sys table needed for the INNER JOIN needed to determine what database the procs belong to?) or use Powershell (I had a PS script for finding obsolete code, but lost it).

TSQL:

SELECT OBJECT_NAME(id)
     , text
FROM syscomments
WHERE text LIKE '%[OBSOLETECODE]%'

When it comes to this process, which of these would be more effective for performance, or is there a superior approach?

Upvotes: 2

Views: 370

Answers (1)

Ian P
Ian P

Reputation: 1724

Well there is the unused index queries: http://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/ and then there is http://www.sqlservercentral.com/articles/SQL+Server/69676/ for unused stored procedures -- of course the essential report the MD allways runs 10 minutes before the AGM may appear unused in 11 months.... pays your money and takes your chances....

Upvotes: 1

Related Questions