How to script out in a single .sql file stored procedures containing specific reference on their code

  • APA0876

    Hall of Fame

    Points: 3853

    Hi,

    How to script out in a single .sql file stored procedures containing specific reference on their code.

    I have this scenario :

    I have to change at least 181 stored procedures that reference a name on their code.

    I was thinking on doing it through Windows Power Shell and SMO but I'm having issues putting the values of the names of the stored procs on a power shell variable.I have the names of thos stored proc by queriing the sys.comments table.

    What do you think will be the best way, this is a SQL Server 2005.

    Thanks

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    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

  • APA0876

    Hall of Fame

    Points: 3853

    The Database is too big to implement this option,

    Do you know how can I do this through PowerShell using SMO:

    I started something like this but I don't know how to put on $SPtoscript the result of the query:

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

    $server = New-Object Microsoft.SqlServer.Management.Smo.Server 'server'

    $database = $server.Databases["Database"]

    $Scripter = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("server")

    $Scripter.Options.DriAll=$False

    $Scripter.Options.IncludeHeaders=$False

    $Scripter.Options.ToFileOnly=$True

    $Scripter.Options.WithDependencies=$False

    $Scripter.Options.FileName = "c:\StoredProcedures_Ref_server.sql"

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlCmd.CommandText = "SELECT Object_Name(id) as Name FROM syscomments WHERE text LIKE '%server%'"

    Invoke-Sqlcmd -Query "SELECT Object_Name(id) as Name FROM syscomments WHERE text LIKE '%server%'" | Out-File -filePath

    "c:\StoredProcedures_Ref_server.sql" -ServerInstance "server" -Database Database

    foreach ($StoredProcName in $SPtoscript)

    {

    $MyStoredProc = $database.StoredProcedures["$StoredProcName"]

    {

    $Scripter.Options.AppendToFile=$True

    $Scripter.Options.ScriptDrops=$True;

    $Scripter.Options.IncludeIfNotExists=$True;

    $Scripter.Script($MyStoredProc)

    $Scripter.Options.ScriptDrops=$False;

    $Scripter.Options.IncludeIfNotExists=$False;

    $Scripter.Options.AppendToFile=$True

    $Scripter.Script($MyStoredProc)

    }

    }

    Thanks

    APA

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    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

  • APA0876

    Hall of Fame

    Points: 3853

    Hi I did what you recommended but on the management studio is returning me results but the bcp it creates the file but 0 rows copied below the script that I'm using:

    DECLARE @string varchar(1000)

    SET @String = 'bcp "SELECT definition 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 ''%Test%'' AND p.is_ms_shipped = 0;" queryout C:\MyCode.csv -SServerName -T -c'

    print @String

    exec xp_cmdshell @String

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    I would not use xp_cmdshell for this work. Have you tried using bcp directly from the command line?

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

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply