SQL Server: List all tables in a specific schema

SELECT * 
FROM sys.objects 
WHERE schema_id = SCHEMA_ID('schemaName') and type='U'


'U' stands for User table.

Use PowerShell to capture database schema

Here is a PowerShell script to capture a database schema. Output is written to a directory/datetime file. Multiple databases/servers can be specified via the XML input file.

To run: ./CaptureSchema.ps1 databases.xml

Here is the PowerShell code:

param ([string]$xmlConfig = $(throw '%argument 1 must be XML configuration file path'))

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Core") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Linq") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml.Linq") | out-null

function ScriptDatabase([string]$serverName, [string]$dbName)
{
   $fileName = [String]::Format("{0} {1}.sql", [DateTime]::Now.ToString("yyyyMMdd_HHmmss"), $dbName)
   [Console]::Write("Server: $serverName, Database: $dbName, Output: `"$fileName`" . . . ")

   $srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
   $db = new-object "Microsoft.SqlServer.Management.SMO.Database"
   $scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"

   $db = $srv.Databases[$dbName]

   $scr.Server = $srv
   $options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
   
   $options.ClusteredIndexes = $true
   $options.Default = $true
   $options.DriAll = $true
   $options.Indexes = $true
   $options.IncludeHeaders = $true
   $options.Triggers = $true
   $options.AppendToFile = $false
   $options.FileName = "$pwd\$fileName"
   $options.ToFileOnly = $true
   
   # output all db tables
   $scr.Options = $options
   $tables = $db.Tables
   if ($tables -ne $null)
   {
      $scr.Script($db.Tables)
   }

   # output all sprocs
   $options.AppendToFile = $true
   $sprocs = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
   if ($sprocs -ne $null)
   {
      $scr.Script($sprocs)
   }
   
   # output all db views
   $views = $db.Views | where {$_.IsSystemObject -eq $false}
   if ($views -ne $null)
   {
      $scr.Script($views)
   }
   
   "done."
   
}

function SaveSchema($xmlDb)
{
   # make folder if not exists yet
   $dbName = $xmlDb.Element("Name").Value
   $dirName = ".\$dbName"
   if ((Test-Path -path $dirName) -eq $False)
   {
      "Creating directory $dirName..."
      ni -type directory $dirName | out-null
   }
   
   # save the schema
   $serverName = $xmlDb.Element("Server").Value
   
   $prevDir = $pwd
   $prevDir
   set-location $dirName
   ScriptDatabase $serverName $dbName
   set-location $prevDir
}

#
# main
#

$xml = [System.Xml.Linq.XElement]::Load((Resolve-Path "$xmlConfig"))

foreach($db in $xml.Elements("Database"))
{
   if ($db.Attribute("Enabled").Value -eq $true)
   {
      SaveSchema $db
   }
}

exit

Here is the XML input file:

<Databases>
   <Database Enabled="true">
      <Name>DatabaseName</Name>
      <Server>ServerName</Server>
   </Database>
   <!-- repeat the Database element if more
        than one database schema to capture -->
</Databases>