• smartCodar.com

  • Categories

    Code Snippets (2)
    Data Access (0)
    Error Handling (0)

  • Recent Articles

  • How to Find a Text in SQL Server Stored Procedure

    by
    SmartCodar
    Published on 06-28-2012 07:55 PM      Number of Views: 696  
    0 Comments Comments
    Introduction

    This article describes How to find a Text in Stored Procedure (Checked on SQL Server 2008)

    Using the code

    sys.procedures Contains a row for each object that is a procedure of some kind, with sys.objects.type = P, X, RF, and PC. see http://msdn.microsoft.com/en-us/library/ms188737.aspx
    Steps:

    • Open SQL Server Management Studio
    • Make sure that you have permission to access the database objects
    • Select the Database which has Stored Procedure in which you need to find Text
    • Click on New Query
    • Below query searches for Text Product and Order [You can use AND or OR and more]


    Code: [View]
    SELECT Name as [Stored Procedure Name]
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Product%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%Order%'
    This may not be needed in a day to day development work. But very useful when you need to find the Tables, Columns, Text used in Stored Procedure in a huge database
    Screen Shot:



    Enjoy