The Multifunctioning DBA

Aug 17 2012   12:00PM GMT

Powershell to Drop Procs

Colin Smith Colin Smith Profile: Colin Smith

Tags:

I was asked by a developer if I could drop all the procs in a database except the ones that he knew were being used. I thought Powershell can do that. So I told him that I could if he provided me a list of the ones that he did not want to be droped. So he sent me a list and I got to work. I thought it would be a good idea to script out all the procs before I dropped them just in case. So below is the code that I came up with.

connect-sql "name"
sqlscript-options

$db = $s.Databases | where{$_.Name -like "*DEV"}
$db.name
$procs = $db.StoredProcedures  | where{$_.IsSystemObject -eq 0}
$procs | select name
"###################################"#
"#####################################"
foreach($proc in $procs)
{
$pname = $proc.name
$pname
$proc.script($so) | Out-File C:\Procs\$pname.sql -Force
if(($pname -notlike "dba*") -and ($pname -ne "procname"))
{
$proc.Drop()
}
}

So lets take a look at this. First you will notice that I am saying connect-sql, and you probably do not have this cmdlet. Well that is because I wrote it as a function that I import in my profile so that it is available. Here is what that function does.

function connect-sql ($arg)
{
if($arg -eq $null)
{
$server = Read-Host "enter instance name to connect to"
}
else
{
$server = $arg
}
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
}

So really it is just a shortcut for me to instatiate a SMO connection.

You also see a sqlscript-options and that is also not a real cmdlet. Also a function and here is what that does.

function sqlscript-options
{
$so = new-object (‘Microsoft.SqlServer.Management.Smo.ScriptingOptions’)
$so.DriIndexes = $TRUE
$so.IncludeIfNotExists = 1
$so.SchemaQualify = 1
$so.AllowSystemObjects = 0
$so.DriPrimaryKey = $TRUE
$so.DriForeignKeys = $TRUE
$so.driallconstraints = $TRUE
$so.IncludeDatabaseContext = $TRUE
$so.Indexes = $true
$so.AllowSystemObjects = $False
$so.triggers = $true
}

So it sets up some options for when I script out objects. Give this a shot and see what you think.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: