SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
APA0876
APA0876
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3521 Visits: 421
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
Orlando Colamatteo
SSC Guru
SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)

Group: General Forum Members
Points: 170939 Visits: 14640
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
APA0876
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3521 Visits: 421
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
Orlando Colamatteo
SSC Guru
SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)

Group: General Forum Members
Points: 170939 Visits: 14640
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
APA0876
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3521 Visits: 421
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
Orlando Colamatteo
SSC Guru
SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)SSC Guru (170K reputation)

Group: General Forum Members
Points: 170939 Visits: 14640
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search