Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import / Export data including keys and indexes


Import / Export data including keys and indexes

Author
Message
bjasmer
bjasmer
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.


Thanks,

Brian Jasmer
buddy__a
buddy__a
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 257
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"





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search