CODEWITHSUNDEEP
sqlsql-servert-sql
Danivn91
Danivn91

Reputation: 63

How to remove all prints in stored procedures / triggers?

I have several databases with lots of triggers, stored procedures, etc. that contain PRINT statements.

Once the tests are finished, I want to remove all these PRINT statements that I used for debug issues.

The problem is that I have to check object by object in all databases to find and delete the line that contains the PRINT statement. This is non-viable due to the amount of objects that exist.

So I am looking for a way to achieve this without having to check object by object.

Upvotes: 0

Views: 352

Answers (1)

Ilyes
Ilyes

Reputation: 14928

You can do that by joining the system catalog views sys.sql_modules and sys.objects as

SELECT SCHEMA_NAME(O.schema_id) [Schema], 
       O.Name ObjectName,
       O.Type ObjectType
FROM sys.sql_modules M
INNER JOIN sys.objects O
ON O.object_id = M.object_id
WHERE M.definition LIKE '%PRINT%'

See how it's working on this Live Demo

And if you want to return the definition, you can just add definition column in your SELECT statement as

SELECT SCHEMA_NAME(O.schema_id) [Schema], 
       O.Name ObjectName,
       O.Type ObjectType,
       M.definition
FROM sys.sql_modules M
INNER JOIN sys.objects O
ON O.object_id = M.object_id
WHERE M.definition LIKE '%PRINT%'

Upvotes: 5

Related Questions