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

  • PowerShell is an option, and one I would go for if this will be a recurring need.

    Here is an option using some T-SQL and the SSMS GUI scripting features that could get you there pretty fast:

    1. Backup your database and restore it to a test instance.

    2. Run this SQL on the restored database to get rid of objects you will not be modifying.

    DECLARE @sql NVARCHAR(MAX);

    SET @sql = N'';

    SELECT @sql = @sql + 'DROP PROC ' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name) + ';

    '

    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 NOT LIKE '%string your looking for%'

    AND p.is_ms_shipped = 0;

    PRINT @sql;

    -- uncomment when ready to do work

    --EXEC(@sql);

    3. Script all remaining procs as ALTER using the Object Explorer (Press F7 in SSMS).

    edit: add "AND p.is_ms_shipped = 0" to query

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