Generating CREATE TABLE Scripts for a large number of tables

Question

Generating CREATE TABLE Scripts for a large number of tables

Doug J.

SSC Eights!

Points: 832
More actions
February 11, 2014 at 9:56 am

#304308

Hi all.
Other than right-clicking on each individual table in SSMS and generating a CREATE script, is there a simple way to generate CREATE TABLE scripts for tables within a given database?
Background:
I have a bunch of tables in one database, and I would like to add tables to a second database that have the same names and basic structures of some of the tables from the first database.
I do not need to transfer any data from the tables, this is a seperate project that will use a similar data structure. I just want to generate the CREATE TABLE scripts for 30ish tables within the first database, and then I'll tweak the scripts as appropriate and run them against the new database.
Thanks in advance for any and all help on this matter.
-Doug
http://xkcd.com/327/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i slapped together a TSQL way to script out a table , so something like this might help; you'll run into issues if the generated scripts are not in foreign key hierarchy.

I haven't updated it in a while, so it's kind of 2005 centric; it doesn't script indexes with WHERE statements or SPARSE columns, i'm still testing soem substantial updates that myself and other folks here on SSC have contributed to make it better

you could insert the results of the proc into a temp table for all your procedures.

user ben-1066434 made a very recent and nice enhancement suggestion here:

http://www.sqlservercentral.com/Forums/Topic751783-566-7.aspx

and this is the last version i've tested and currently use.

sp_GetDDL_Latest.txt

something like this would get you a table full of scripts, so you cna select them, paste them, and edit them:

CREATE TABLE #Results(ResultsId int identity(1,1) not null primary key, ResultsText Varchar(max))

select 'INSERT INTO #Results(ResultsText) EXEC sp_GetDDLa ' + quotename(name) + ';' from sys.tables

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Doug J. SSC Eights! Points: 832 More actions · Answer 2

Lowell,

This is brilliant. Thanks very much for putting this together and for passing it along. This saves me a lot of work today and in the future.

Thanks,

-Doug

http://xkcd.com/327/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

You could as well right-click the database, go to Tasks-> Generate Scripts... and it will help you to script all the objects you need from the db.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

That is one LONG way around the pond, Lowell.

SSMS. Open database, click on tables. Go to Object Explorer Details window on right. If you don't see it, hit F7. Ctrl-Click or Shift-Click to highlight your tables. Rt-Click, Script Table As, Create, New window. Voila.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Lowell SSC Guru Points: 323518 More actions · Answer 5

Evil Kraig F (2/11/2014)
That is one LONG way around the pond, Lowell.
SSMS. Open database, click on tables. Go to Object Explorer Details window on right. If you don't see it, hit F7. Ctrl-Click or Shift-Click to highlight your tables. Rt-Click, Script Table As, Create, New window. Voila.

oh yeah no doubt about it Craig; but it does have a narrow use window for me, and it's faster than SMO generating the scripts.

It all started because i wanted to be able to script a table via TSQL; it gathered a life of it's own after that, from a proof of concept to some monster that i didn't know would get bigger than the bathtub i put it in.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Lowell (2/11/2014)
oh yeah no doubt about it Craig; but it does have a narrow use window for me, and it's faster than SMO generating the scripts.
It all started because i wanted to be able to script a table via TSQL; it gathered a life of it's own after that, from a proof of concept to some monster that i didn't know would get bigger than the bathtub i put it in.

I've got a piece of code that looks like that. It started with me wanting to know what some switch was via code for some reason in the system tables. I think I was trying to look for heaps or something in a vendor system. Add three years of simple curiousity later....

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

sagar.kumar Grasshopper Points: 19 More actions · Answer 7

right click on database-->Tasks -->Generate Scripts--> a new window is opend. click next-->check on "select specific db objects"--> check tables you want to generate scripts-->click next.give the path where you want to save the script.-->next-->next.

hope this will work. 🙂