Home Forums SQL Server 2005 SQL Server 2005 General Discussion How to script out in a single .sql file stored procedures containing specific reference on their code RE: How to script out in a single .sql file stored procedures containing specific reference on their code

  • May 11, 2012 at 11:03 am

    #1486464

    For a one-time need? Nah. How about something like this?

    bcp "SELECT definition + NCHAR(13) + NCHAR(10) + 'GO' + NCHAR(13) + NCHAR(10) FROM sys.sql_modules sm JOIN sys.procedures p ON sm.object_id = p.object_id JOIN sys.schemas s ON p.schema_id = s.schema_id WHERE sm.definition LIKE '%string your looking for%' AND p.is_ms_shipped = 0;" queryout C:\@\MyCode.sql -w -T -S ServerName -d DatabaseName

    Then open the file and replace CREATE PROC with ALTER PROC and do what you need to do with the code.

    Edit: changed "NOT LIKE" to "LIKE" since we reversed the approach from subtracting objects to grabbing what we want

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato