#PSTip Generate T-SQL script to clone database tables using SMO

Note: This tip requires PowerShell 2.0 or above.

In an earlier tip, we looked at how we can use SMO in PowerShell to generate T-SQL scripts for cloning databases. Today, we shall see how we can clone database tables. The approach is very similar.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName
$server.Databases['DBName']

Once we have the database object, we can access the ‘Tables’ property and retrieve all the tables in the database.

$server.Databases['DBName'].Tables

Now, if we want to clone a table in a database, we can do that using:

$server.Databases['DBName'].Tables['TableName'].Script()