Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Import / Export data including keys and indexes Expand / Collapse
Posted Friday, February 10, 2012 3:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 10, 2012 3:37 PM
Points: 1, Visits: 0
As SSCrazy was saying, there is no need to buy a utility. SQL Server makes it quite easy to export all the tables and indexes (and triggers and foreign keys, and security settings etc) from one database to another.

1. Select the tables you'd like to export.
2. Tools--> Generate SQL Scripts
3. Go to the Options tab and select what you'd like to export (eg. indexes, triggers, security)
4. From here you can create a file with a script ready to run in another database.

5. Now, go into your NEW database, open the SQL Query Analyzer, import the file you just created and run it.


Brian Jasmer

Post #1250595
Posted Tuesday, March 18, 2014 3:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, June 25, 2015 6:54 AM
Points: 329, Visits: 247
I know this is an old thread but I recently had to do this and found that the following Powershell commands to be very useful. They only get the constraints that I needed but you can make mnor mods to add any other constraints or even objects that you want.

$sql.Databases["MyDB"].Tables | % {$_.Indexes} | % {$_.script() }
$sql.Databases["MyDB"].Tables | % {$_.ForeignKeys} | % {$_.script()}
$sql.Databases["MyDB"].Tables | % {$_.Columns} | ? {$_.DefaultConstraint -ne $null} | % {$_.DefaultConstraint.script()}

Of course you will need to load the the SMO object and connect to the server:

$smo = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$sql = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "YourServer"

These commands will output to the screen. If you want to dump the results to a file just add the following to the end of the commands above:

 | Out-File -FilePath "C:\YourPath\ScriptFilename.sql"

Post #1552419
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse