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 17 total)
You must be logged in to reply to this topic. Login to reply