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

Question

Post reply

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

APA0876

Hall of Fame

Points: 3853
More actions
May 10, 2012 at 3:13 pm

#150938

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

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

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

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 More actions · Answer 2

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: 182276 More actions · Answer 3

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 More actions · Answer 4

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: 182276 More actions · Answer 5

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