Anyone have a process to save a procedure to network location, then drop it?

Question

Anyone have a process to save a procedure to network location, then drop it?

MyDoggieJessie

SSC-Forever

Points: 44486
More actions
October 31, 2013 at 2:45 pm

#279539

I have a bunch of developers that currently use a production database to develop their reporting procedures. When they've finished with the ones they're working on that rename the procedure "z_del_ProcedureName" - this way I know it's okay toast them.
I'd like to know if anyone has a ready-to-go process that will fetch all the procedures in a database named "z_del_*", script them out to a valid network location as a create/drop, then physically drop them from the database.
I'm thinking there has to be a power shell script or something similar for this - Anyone?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

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

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

durai nagarajan SSCoach Points: 15778 More actions · Answer 1

Use Generate scripts , scroll and select only the necessary SP's.

By the way why are the developing it on a prod server , don't you have dev server fro this and where will the testing happens and how will you check the deployment then?

Regards
Durai Nagarajan

durai nagarajan SSCoach Points: 15778 More actions · Answer 2

one more question , Why developers have access to production?

Regards
Durai Nagarajan

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

Thanks for the suggestion, much appreciated - I know I could do that but I'm looking for an automated way it can be done (something I can place in a recurring job).

In a perfect world they wouldn't have access to production. However, this is not the case and it's something I have to live with for the foreseeable future as their DEV server is being built.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

dogramone Hall of Fame Points: 3216 More actions · Answer 4

You could select the process from the information_schema.routines view then loop through them with a drop procedure TSQL statement.

Edit. Additional details.

Then just select out the procedure details from the routine_definition field into a table, push that table to the file system then delete the procedure if required.

Sean Pearce SSCoach Points: 15999 More actions · Answer 5

What about creating a stored procedure database on your server.

1. Loop through sys.procedures for _del_

2. Fetch ~~text from syscomments~~ definition from sql_modules

3. Create proc on stored procedure database

4. Delete procedure from prod database

5. Backup stored procedure database to network location

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

Through Google searching I've come up with a great way to automatically script out the procedures, but currently am having trouble getting the delete portion to work

Powershell script$rootDrive = "C:\Temp\"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

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

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "NAS2-RPTPROD1"

$dbName = $srv.databases["SSRS_Run_Reports"]

$procs = $dbName.StoredProcedures

$MyScripter.Server=$srv

#STORED PROCEDURES

if($procs -ne $null)

{

foreach ($proc in $procs)

{

#Assuming that all non-system stored procs have proper naming convention and don't use prefixes like "sp_"

if ( $proc.Name.IndexOf("z_del_") -eq 0)

{

$fileName = $proc.name.Replace("/", "")

"Scripting SP $fileName"

$scriptfile = "$rootDrive\$filename.sql"

$MyScripter.Options.FileName = $scriptfile

$MyScripter.Options.AppendToFile = "False"

$MyScripter.Script($proc)|out-null

$proc.drop()

}

Generates this error on the $proc.drop():

An error occurred while enumerating through a collection: Collection was modifi

ed; enumeration operation may not execute..

At C:\Temp\test.ps1:16 char:12

+ foreach <<<< ($proc in $procs)

Any suggestions?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience