How to exclude replication stored procedures from a script?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Programming » Powershell » How to exclude replication stored procedures...

Add to briefcase

18 posts, Page 1 of 2

1 2 »»

How to exclude replication stored procedures from a script?

Expand / Collapse

Author

Message

Posted Sunday, June 24, 2012 6:49 PM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:18 PM
Points: 965, Visits: 1,549

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

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1320805

Posted Monday, June 25, 2012 7:06 PM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:18 PM
Points: 965, Visits: 1,549

I tried:
Get-ChildItem -exclude sp_MS*

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

Crazy

Post #1320961

Posted Monday, June 25, 2012 8:57 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1320976

Posted Monday, June 25, 2012 9:20 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,826, Visits: 11,950

opc.three (6/25/2012)

Try it with -Name, like this:

Get-ChildItem -Name -Exclude dbo.sp_MS*

Tongue

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1320982

Posted Tuesday, June 26, 2012 12:03 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:18 PM
Points: 965, Visits: 1,549

opc.three (6/25/2012)

opc.three (6/25/2012)

Try it with -Name, like this:

Get-ChildItem -Name -Exclude dbo.sp_MS*

Tongue

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!

Group: General Forum Members
Last Login: Yesterday @ 10:18 PM
Points: 965, Visits: 1,549

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

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,826, Visits: 11,950

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*

Tongue

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1321157

Posted Tuesday, June 26, 2012 5:42 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1321160

Posted Tuesday, June 26, 2012 7:18 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 347, Visits: 620

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

18 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse