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.

System.sql_modules and sys.syscomments system views can be used to dig into the definition of objects and search if any other object is used in the definition or not. Let's suppose if I am looking for comments in objects Creator Name=Aamir. I can use both system views to find out required information by using below queries.


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


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

Fig 1: Search String in all Stored Procedures in SQL Server Database

4 comments:

  1. Steve BerkeOctober 7, 2019 at 8:45 AM

    I enjoyed reading your article. Please make more interesting topics like this on.
    I'll come back for more :)

    From Japs a researcher from AOC, a company whois into ecommerce website design Sydney

    ReplyDelete
  2. Alice AnnerOctober 15, 2019 at 2:59 AM

    Even when you are looking at inexpensive materials, an L-shaped desk is still an investment! It is crucial that the desk you choose fits your needs and be able to provide you with the storage as well as workspace necessary to carry out your goals.

    ReplyDelete
  3. midnDecember 2, 2019 at 10:42 PM

    replica watches uk, combining elegant style and cutting-edge technology, a variety of styles of replica cartier watches, the pointer walks between your exclusive taste style.

    ReplyDelete
  4. faizanDecember 10, 2019 at 12:02 PM

    This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. find out more

    ReplyDelete

Subscribe to: Post Comments (Atom)