Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to exclude replication stored procedures from a script? Expand / Collapse
Author
Message
Posted Sunday, June 24, 2012 6:49 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 6:51 PM
Points: 987, Visits: 1,631
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}



Post #1320411
Posted Monday, June 25, 2012 11:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1320805
Posted Monday, June 25, 2012 7:06 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 6:51 PM
Points: 987, Visits: 1,631
I tried:
Get-ChildItem -exclude sp_MS*

But I am still getting those replication stored procs in the list.



Post #1320961
Posted Monday, June 25, 2012 8:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1320976
Posted Monday, June 25, 2012 9:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1320982
Posted Tuesday, June 26, 2012 12:03 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 6:51 PM
Points: 987, Visits: 1,631
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?



Post #1321028
Posted Tuesday, June 26, 2012 2:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 6:51 PM
Points: 987, Visits: 1,631
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!



Post #1321084
Posted Tuesday, June 26, 2012 5:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1321157
Posted Tuesday, June 26, 2012 5:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1321160
Posted Tuesday, June 26, 2012 7:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 428, Visits: 926
This will exclude all non-user objects. I assume they're probably not required anyway.

Get-ChildItem | where {$_.IsSystemObject -eq $false}



Post #1321216
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse