Find Text in All objects definition in SQL Server - SQL Server Tutorial

TSQL Tutorial Video Scenario:
How to find String in All Stored Procedures in SQL Server Database
?

We often have the situations where we want to find string in all the stored procedures in a database. Some of the situations are listed below

Find out how many stored procedure are using Cross Database Queries, so we want to search for Database name in all the stored procedures in a database.
Find out in how many stored procedure a table/view is used in a Database? This can be situation where we are planning to change the name of table/view and want to make sure if any Stored Procedure/s will be effected.
Find out a column name used in Stored Procedure/s in SQL Server Database.
Find out some comments in all the Stored Procedure to see who created those Stored Procedure, if Creator name is used in the comments.

and there could be more situations. SQL Server provide us system tables and views those store information and definition of objects for us so we can get information related to the objects such as tables,views,Stored Procedures,Triggers,Functions etc.

SELECT OBJECT_NAME(OBJECT_ID) AS ObjectName,
definition AS ObjectDefinition
WHERE definition LIKE '%Creator Name=Aamir%'

SELECT OBJECT_NAME(id) AS ObjectName,
TEXT AS ObjectDefinition
WHERE TEXT LIKE '%Creator Name=Aamir%'

You can visit our blog

[krismaly]

Thanks for clearly explaining sys.sql_modules and syscomments with an example.

Keep doing some more such short videos on sys...
Thanks