June 24, 2012 at 6:49 pm
Hi,
I am using the below powershell code to genarate a script for all the SPs in a database. Is there any way to change it so it does not script replication SPs, which start with either sp_MSdel_dbo, sp_MSins_dbo or sp_MSupd_dbo?
Thanks.
#DECLARE TIMESTAMP FOR THE FILES
$timestamp = Get-Date -Format yyyy-MM-dd
#SCRIPT
SL SQLSERVER:\SQL\"MyServer\Myinstance"\Databases\"MyDB"\StoredProcedures
$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.IncludeIfNotExists = 1
dir | %{$_.Script($so) + " GO " | Out-File "Z:\MyDB\08_SPs $timestamp.sql" -Append}
June 25, 2012 at 11:26 am
dir is an alias for Get-ChildItem
Get-Alias dir
One of the options for Get-ChildItem is -Exclude which accepts a pattern.
help Get-ChildItem -Detailed
If you devise the repl proc pattern and provide that to Get-ChildItem as an exclusion option in -Exclude you should be able to get only what you need.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 25, 2012 at 7:06 pm
I tried:
Get-ChildItem -exclude sp_MS*
But I am still getting those replication stored procs in the list. :crazy:
June 25, 2012 at 8:57 pm
Try it with -Name, like this:
Get-ChildItem -Name -Exclude dbo.sp_MS*
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 25, 2012 at 9:20 pm
opc.three (6/25/2012)
Try it with -Name, like this:
Get-ChildItem -Name -Exclude dbo.sp_MS*
and then I remembered the initial intent...
Try this one:
Get-ChildItem | Where-Object {$_.name -NotLike "sp_MS"} | %{$_.Script($so) + " GO " | Out-File "Z:\MyDB\08_SPs $timestamp.sql" -Append}
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2012 at 12:03 am
opc.three (6/25/2012)
opc.three (6/25/2012)
Try it with -Name, like this:
Get-ChildItem -Name -Exclude dbo.sp_MS*
and then I remembered the initial intent...
Try this one:
Get-ChildItem | Where-Object {$_.name -NotLike "sp_MS"} | %{$_.Script($so) + " GO " | Out-File "Z:\MyDB\08_SPs $timestamp.sql" -Append}
Doesn't work either. I am still getting the replication SPs in the list. Should I put some sort of wildcard character into the like statement?
June 26, 2012 at 2:09 am
opc.three (6/25/2012)
Try it with -Name, like this:
Get-ChildItem -Name -Exclude dbo.sp_MS*
This one worked, because it has "dbo."
Thanks!
June 26, 2012 at 5:38 am
Roust_m (6/26/2012)
opc.three (6/25/2012)
opc.three (6/25/2012)
Try it with -Name, like this:
Get-ChildItem -Name -Exclude dbo.sp_MS*
and then I remembered the initial intent...
Try this one:
Get-ChildItem | Where-Object {$_.name -NotLike "sp_MS"} | %{$_.Script($so) + " GO " | Out-File "Z:\MyDB\08_SPs $timestamp.sql" -Append}
Doesn't work either. I am still getting the replication SPs in the list. Should I put some sort of wildcard character into the like statement?
Yes, you would have to say sp_MS*.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2012 at 5:42 am
Roust_m (6/26/2012)
opc.three (6/25/2012)
Try it with -Name, like this:
Get-ChildItem -Name -Exclude dbo.sp_MS*
This one worked, because it has "dbo."
Thanks!
True, but I found it does not work in the rest of the pipeline. I assume this is because the -Name switch changes the output from an object to a simple string, and the .Script method is part of the database object.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2012 at 7:18 am
This will exclude all non-user objects. I assume they're probably not required anyway.
Get-ChildItem | where {$_.IsSystemObject -eq $false}
June 26, 2012 at 7:55 am
schleep (6/26/2012)
This will exclude all non-user objects. I assume they're probably not required anyway.Get-ChildItem | where {$_.IsSystemObject -eq $false}
That brings up a good point. I have replication enabled in my DB and see the repl procs under System Stored Procedures in SSMS but when I run just Get-ChildItem (i.e. without any filtering) at the StoredProcedures prompt in PS none of the repl procs are returned, in fact no system procs are returned, I only see my user-defined stored procedures.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2012 at 9:15 am
$Srv = New-object Microsoft.SqlServer.Management.SMO.Server "Mufasa"
$Srv.Databases["Abb"].StoredProcedures | where {$_.issystemobject -eq $true} | select name
This returns 1300+ system procs -- including repls, and I don't have replication enabled.
P
June 26, 2012 at 9:58 am
From the OP's code, modified for my machine for demo:
Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
Set-Location SQLSERVER:\SQL\MyComputer\MyInstanceName\Databases\MyDataseName\StoredProcedures
Get-ChildItem | Select Name
I only receive a list of user-defined procs.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2012 at 10:58 am
Confirmed.
Set-Location SQLSERVER:\SQL\MyServer\Default\Databases\MyDB\StoredProcedures
dir | select name
does not return any system sprocs. I wonder why the repl sprocs would fall into the user category.
Weird...
P
June 26, 2012 at 11:08 am
schleep (6/26/2012)
Confirmed.Set-Location SQLSERVER:\SQL\MyServer\Default\Databases\MyDB\StoredProcedures
dir | select name
does not return any system sprocs. I wonder why the repl sprocs would fall into the user category.
Weird...
P
Indeed. This was quite an entertaining exercise nonetheless
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy