Filtering the list of tables to script


I have this script, that scripts all tables in a database into one file.  How can I change it so it scripts only tables, which match the masks of 'Table1%' or 'Table2%' into individual files named [tablename].sql?



If (!(Test-Path A:)) {net use A: \\myserver\scripts}

$timestamp = Get-Date -Format yyyy-MM-dd

SL SQLSERVER:\SQL\"myserver\myinstance"\Databases\"MyDB"\Tables

$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions

$so.IncludeIfNotExists = 1

$so.DriPrimaryKey = 1                        

$so.DriForeignKeys = 0                      

$so.DriUniqueKeys = 1                      

$so.DriClustered = 1                          

$so.DriNonClustered = 1                   

$so.DriChecks = 1                            

$so.DriDefaults = 1                           

$so.Triggers = 1  

$so.FullTextIndexes = 1        

$so.ClusteredIndexes = 1

$so.NonClusteredIndexes = 1

 #remove the script if it exists                  

If (test-path "A:\Tables $timestamp.sql")


remove-item "A:\Tables $timestamp.sql"


dir | %{$_.Script($so) + " GO "  | Out-File "A:\Tables $timestamp.sql" -Append}


No Data
  • Found a problem: It works from powershell command, but fails as part of sql server job.

    Unable to start execution of step 1 (reason: line(1): Syntax error).  The step failed.

    The bit it doesn't like is: $($_.Name), if I replace it with some text like 'Tables' it works.


No Data