The Multifunctioning DBA

Aug 17 2012   12:00PM GMT

Powershell to Drop Procs

Colin Smith Colin Smith Profile: Colin Smith

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: