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

45 comments:

  1. 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
  2. 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
  3. Beauty is one's ability to generate light through character. Light requires energy. Energy requires movement. Character implies self confidence and self esteem. Character also implies wisdom gained through experience. Beauty is, therefore, character in motion. Ecommerce savona

    ReplyDelete
  4. I have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation. Thanks a lot for keeping great stuff. I am very much thankful for this site. Ecommerce Liguria

    ReplyDelete
  5. This is actually the kind of information I have been trying to find. Thank you for writing this information. buy 500 instagram likes uk

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. I truly welcome the sort of points you post here. A debt of gratitude is in order for sharing us an incredible data that is really useful. Great day! 192.168.1.254

    ReplyDelete
  9. This is my first time visit to your blog and I am very interested in the articles that you serve. Provide enough knowledge for me. Thank you for sharing useful and don't forget, keep sharing useful info: shopify dropshipping websites for sale

    ReplyDelete
  10. Clinique de haut standing pluridisciplinaire qui dispose de toutes les spécialités médico-chirurgicales et d'un plateau technique de pointe conforme aux normes du Ministère de la Santé Publique.
    Votre chirurgie esthétique bariatrique orthopedique en tunisie, information des types de chirurgies, le voyage et les séjour.
    Chirurgie esthétique, de l'obésité, proctologique, orthopédique en traumatologique en Tunisie avec Tunisie Esthetic.
    website: https://www.tunisie-esthetic.com/

    ReplyDelete
  11. I’m glad to find another amazing app development blogger.
    online meal kit delivery services

    ReplyDelete
  12. That is amazing very good Thank you so much for sharing this post. สมัคร joker slot

    ReplyDelete
  13. This is an amazing Blog and very informative and useful Thanks.
    virx

    ReplyDelete
  14. I really appreciate the kind of topics you post here. Thanks for sharing us a great information that is actually helpful.
    ทางเข้าเล่น 123BETTING

    ReplyDelete
  15. Great Article it its really informative and innovative keep us posted with new updates. its was really valuable. thanks a lot.
    create taxi app

    ReplyDelete
  16. Never though of being an grateful and nice way to conclude all the managing point according to the clients.
    Sarojini Nagar Market Near Me Girls Delhi
    News 18 Mumbai High Profile Girls

    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. Medical Equipment Manufacturers in Medical Equipment Manufacturers in Kolkata should also consider shortening the distance between their factories and their customers as much as possible. If this is not possible, then a company should consider a central distribution point to concentrate shipments to allocate in an efficient manner.
    출장샵
    출장샵
    출장샵
    출장샵
    출장샵
    출장샵

    ReplyDelete
  19. massage again increases blood flow, this time to help remove the build up of toxins from within the muscles. Recent research has shown deep muscle massage after a workout can cause muscles to enlarge and grow new mitochondria사천출장샵
    양산출장샵
    진주출장샵
    창원출장샵
    통영출장샵
    거제출장샵
    김천출장샵

    ReplyDelete
  20. Thanks for sharing this post. I got information on this site. Keep sharing.
    divorce law in new jersey

    ReplyDelete
  21. Notwithstanding its helpful impacts, rub treatment can likewise support injury recuperation and restoration. By focusing on unambiguous areas of pressure or injury, rub methods, for example, profound tissue knead or myofascial delivery can advance recuperating and further develop scope of movement. Competitors, specifically, frequently integrate rub into their preparation schedules to forestall wounds and improve execution. body to body massage centre in hyderabad Whether you look for unwinding, stress help, or recuperation from injury, knead treatment offers a comprehensive way to deal with wellbeing that tends to both the physical and profound parts of wellbeing.

    ReplyDelete