SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Automating Creation Scripts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MattC
Starting Member

USA
9 Posts

Posted - 10/09/2012 :  07:43:07  Show Profile  Reply with Quote
Hi,

I have a (very long) list of table names. For each of these tables I need to generate an SQL creation script and save that script to a file.

Since I have hundreds of these to do I would like to automate the process.

Is there a simple way to accomplish this? Any suggestions are appreciated.

Thanks,
Matt

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/09/2012 :  07:47:45  Show Profile  Reply with Quote
Depending on the details of your requirement, couple of possibilities:

1. Use code such as the one on this page: http://scriptsqlconfig.codeplex.com/ (Thanks to KH for pointing to this link in another thread. That thread also may be of interest to you: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=179540)

2. In SSMS, in object explorer, right-click on the database name and Tasks -> Generate Scripts and follow through the wizard.
Go to Top of Page

MattC
Starting Member

USA
9 Posts

Posted - 10/10/2012 :  10:32:05  Show Profile  Reply with Quote
Thanks for the help. Unfortunately the codecomplex program would not run on my pc and the other thread was interesting but not quite what I need.

Let me restate the question in a simpler form - that way I'll have a better chance of understanding the answer. :)

Given a table name how can I use SQL to produce a creation script for that table?

Many Thanks,
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/10/2012 :  11:54:33  Show Profile  Reply with Quote
If generating the scripts using the menu option in object explorer is not a possiblility, the next best thing I can think of is to sue a powershell script. But, that comes with its own issues - nonetheless you can give it a try.

In the script below, you can change the first four lines and run it.

If you want to script all the tables, just remove the Where-Object filter on the line before the foreach loop.

If you have never run powershell scripts before on the server, you will need to change the execution policy from a powershell window using:
set-executionpolicy remotesigned

I tested this code on my local box with 3 tables and it works. This is my own unsophisticated script with very little error checking; but it is tailored to what you described.
$servername = '(local)';  # Your ServerName
$databasename = 'YourDatabaseNameHere'; # Your Databasename
$OutputFolderName = 'C:\Temp'; # Folder where you want the scripts to be written to
$InputTableList = 'C:\Temp\tables.txt' # File that lists the tables that you want to script

$tableList = Get-Content $InputTableList;

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
$server = new-object Microsoft.SqlServer.Management.Smo.Server($servername);
$database = $server.databases[$databasename];
$tables = $database.tables | Where-Object { $tableList -contains ($_.name) }
foreach ($table in $tables)
{
	$table.script() | out-file ([IO.Path]::Combine($OutputFolderName,$table.name+'.sql'));
}

Edited by - sunitabeck on 10/10/2012 11:55:19
Go to Top of Page

MattC
Starting Member

USA
9 Posts

Posted - 10/12/2012 :  13:18:32  Show Profile  Reply with Quote
Many thanks for your thoughtful response. I need to take some time and look into PowerShell scripts.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000