InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 22:49:07 GMT20http://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspxI remember it well. Hilarious!Tue, 26 Jun 2012 11:43:07 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspxYes, it is a picture of Robert Hays as Ted Striker. I am not sure if you can see the detail in the picture but it's from the scene when he's trying to land the plane and the sweat is pouring off him in buckets.Tue, 26 Jun 2012 11:41:15 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspxPS: Is your picture Robert Hays in Airplane?Tue, 26 Jun 2012 11:12:29 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspx[quote][b]schleep (6/26/2012)[/b][hr]Confirmed.Set-Location SQLSERVER:\SQL\MyServer\Default\Databases\MyDB\StoredProceduresdir | select namedoes not return any system sprocs. I wonder why the repl sprocs would fall into the user category.Weird...P[/quote]Indeed. This was quite an entertaining exercise nonetheless :-PTue, 26 Jun 2012 11:08:03 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspxConfirmed.Set-Location SQLSERVER:\SQL\MyServer\Default\Databases\MyDB\StoredProceduresdir | select namedoes not return any system sprocs. I wonder why the repl sprocs would fall into the user category.Weird...PTue, 26 Jun 2012 10:58:02 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspxFrom the OP's code, modified for my machine for demo:[code="other"]Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinueAdd-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinueSet-Location SQLSERVER:\SQL\MyComputer\MyInstanceName\Databases\MyDataseName\StoredProceduresGet-ChildItem | Select Name[/code]I only receive a list of user-defined procs.Tue, 26 Jun 2012 09:58:37 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspx$Srv = New-object Microsoft.SqlServer.Management.SMO.Server "Mufasa"$Srv.Databases["Abb"].StoredProcedures | where {$_.issystemobject -eq $true} | select nameThis returns 1300+ system procs -- including repls, and I don't have replication enabled.PTue, 26 Jun 2012 09:15:46 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspx[quote][b]schleep (6/26/2012)[/b][hr]This will exclude all non-user objects. I assume they're probably not required anyway.Get-ChildItem | where {$_.IsSystemObject -eq $false}[/quote]That brings up a good point. I have replication enabled in my DB and see the repl procs under [i]System Stored Procedures[/i] in SSMS but when I run just [i]Get-ChildItem[/i] (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.Tue, 26 Jun 2012 07:55:59 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspxThis will exclude all non-user objects. I assume they're probably not required anyway.Get-ChildItem | where {$_.IsSystemObject -eq $false}Tue, 26 Jun 2012 07:18:03 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspx[quote][b]Roust_m (6/26/2012)[/b][hr][quote][b]opc.three (6/25/2012)[/b][hr]Try it with -Name, like this:[code="other"]Get-ChildItem -Name -Exclude dbo.sp_MS*[/code][/quote]This one worked, because it has "dbo."Thanks![/quote]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.Tue, 26 Jun 2012 05:42:48 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspx[quote][b]Roust_m (6/26/2012)[/b][hr][quote][b]opc.three (6/25/2012)[/b][hr][quote][b]opc.three (6/25/2012)[/b][hr]Try it with -Name, like this:[code="other"]Get-ChildItem -Name -Exclude dbo.sp_MS*[/code][/quote]:-P and then I remembered the initial intent...Try this one:[code="other"]Get-ChildItem | Where-Object {$_.name -NotLike "sp_MS"} | %{$_.Script($so) + " GO " | Out-File "Z:\MyDB\08_SPs $timestamp.sql" -Append}[/code][/quote]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?[/quote]Yes, you would have to say [i]sp_MS*[/i].Tue, 26 Jun 2012 05:38:41 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspx[quote][b]opc.three (6/25/2012)[/b][hr]Try it with -Name, like this:[code="other"]Get-ChildItem -Name -Exclude dbo.sp_MS*[/code][/quote]This one worked, because it has "dbo."Thanks!Tue, 26 Jun 2012 02:08:59 GMTRoust_mhttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspx[quote][b]opc.three (6/25/2012)[/b][hr][quote][b]opc.three (6/25/2012)[/b][hr]Try it with -Name, like this:[code="other"]Get-ChildItem -Name -Exclude dbo.sp_MS*[/code][/quote]:-P and then I remembered the initial intent...Try this one:[code="other"]Get-ChildItem | Where-Object {$_.name -NotLike "sp_MS"} | %{$_.Script($so) + " GO " | Out-File "Z:\MyDB\08_SPs $timestamp.sql" -Append}[/code][/quote]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?Tue, 26 Jun 2012 00:03:44 GMTRoust_mhttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspx[quote][b]opc.three (6/25/2012)[/b][hr]Try it with -Name, like this:[code="other"]Get-ChildItem -Name -Exclude dbo.sp_MS*[/code][/quote]:-P and then I remembered the initial intent...Try this one:[code="other"]Get-ChildItem | Where-Object {$_.name -NotLike "sp_MS"} | %{$_.Script($so) + " GO " | Out-File "Z:\MyDB\08_SPs $timestamp.sql" -Append}[/code]Mon, 25 Jun 2012 21:20:04 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspxTry it with -Name, like this:[code="other"]Get-ChildItem -Name -Exclude dbo.sp_MS*[/code]Mon, 25 Jun 2012 20:57:25 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspxI tried:Get-ChildItem -exclude sp_MS*But I am still getting those replication stored procs in the list. :crazy:Mon, 25 Jun 2012 19:06:19 GMTRoust_mhttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspxdir is an alias for Get-ChildItem[code="other"]Get-Alias dir[/code]One of the options for Get-ChildItem is -Exclude which accepts a pattern. [code="other"]help Get-ChildItem -Detailed[/code]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.Mon, 25 Jun 2012 11:26:21 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1320411-1351-1.aspxHi,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#SCRIPTSL SQLSERVER:\SQL\"MyServer\Myinstance"\Databases\"MyDB"\StoredProcedures$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions$so.IncludeIfNotExists = 1dir | %{$_.Script($so) + " GO " | Out-File "Z:\MyDB\08_SPs $timestamp.sql" -Append}Sun, 24 Jun 2012 18:49:17 GMTRoust_m