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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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