Script table into a single create statement

Question

Post reply

Script table into a single create statement

JeremyE

SSCoach

Points: 16199
More actions
November 27, 2007 at 3:40 pm

#118982

Is there a way to script tables into a single create statement instead of a create and then a few alter statements? I want the scripted table to come out like this:
CREATE TABLE [dbo].
(
[Date] [smalldatetime] CHECK ([Date] >= '2008-01-01' and [Date] <= '2008-12-31') ,
[ID] [int],
[Item] [int]
PRIMARY KEY (Date, ID, Item)
) ON [PRIMARY]
GO
Instead of like this:
CREATE TABLE [dbo].
(
[Date] [smalldatetime] NOT NULL ,
[ID] [int] NOT NULL ,
[Item] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].
WITH NOCHECK ADD
CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED
(
[Date],
[ID],
[Item]
) WITH FILLFACTOR = 85 ON [PRIMARY]
GO
ALTER TABLE [dbo].
ADD
CONSTRAINT [CC_check] CHECK ([Date] >= '2008-01-01' and [Date] <= '2008-12-31')
GO

Viewing 15 posts - 1 through 15 (of 15 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

See 'Create Table' in BOL. Specifically, look under section F: Complete table definitions.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

JeremyE SSCoach Points: 16199 More actions · Answer 2

What I am looking for is to generate a script from an existing table. You can script a table by right clicking on the table in EM, All Tasks, Generate SQL Script.

Using this method generates a script with create table and alter table statements. I need this to be a single create statement.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

I need this to be a single create statement.

Why?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JeremyE SSCoach Points: 16199 More actions · Answer 4

I need to create new tables for updatable partitioned views and would rather do a find and replace 2007 with 2008 than have to re-write all the create table statements.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Start in Enterprise Mangeler... uh... Enterprise Manager. 😀 Drill down to the database tables you want and select them all. Right click on any of the selected tables, select {All Tasks}, then select {Generate Script}... follow your nose after that.

Be advised that if there are dependencies between the tables, you may have to change the order of the scripts created (or generate single scripts and control the order with a batch file).

Also, be advised that if you have any named constraints that are not changed due to your Search'n'Replace, they will fail in the new script as already being present.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JeremyE SSCoach Points: 16199 More actions · Answer 6

Thanks Jeff. In a post above I stated you can create scripts from EM. However, it generates scripts with alter statements for the PK and the constraints. The constraints must be in table's create statement and you cannot use alter statements to add constraints when using partitioned updateable views. Unfortunately, I have first hand experience on how this breaks the view. Looks like a long day of manipulating scripts. 🙁

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Sorry about the EM thing... still trying to flatten out a crease in my brain with coffee...

If you set the scripting options correctly in Query Analyzer, you can generate such scripts, albeit one at a time, there. In case you don't know how to start that process, press the {f8} key and follow your nose. Right click on any of the tables and a scripting menu will appear. Be sure to look at and set the desired scripting options in the [Scripting Options...} selection of that same menu.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JeremyE SSCoach Points: 16199 More actions · Answer 8

Jeff you are a genius! Don't know why scripting from QA didn't cross my mind :crazy:

I tried many different ways to script a table but missed QA. It works like a charm. Thanks.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Outstanding, Edogg! Thank you for the feedback!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EdVassie SSC Guru Points: 60472 More actions · Answer 10

Both EM and QA do their scripting using calls to a SQL scripting API. My experience is this can be buggy when you try to get everything scripted out as a single CREATE statement. It does not give an error, but some types of constraint syntax are just not scripted.

You should cross-check the results of your scripting against the original tables to confirm everything you need is there.

I found the only way to reliably get the entire table definition scripted was to use multiple calls to the scripting API, requesting a) the main table definition, b) constraints, c) indexes, d) triggers. This causes the constraints to be generated as ALTER statements, but at least you get them...

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 11

Use 3rd party tools or see the below link, it may helps to you.

http://www.microsoft.com/downloads/details.aspx?FamilyID=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

🙂

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

EdVassie (11/29/2007)
Both EM and QA do their scripting using calls to a SQL scripting API. My experience is this can be buggy when you try to get everything scripted out as a single CREATE statement.

Ed,

Are you talking about the sp_OA* routines or something else? If it something else, is it possible to run that "something else" either from T-SQL or at the command prompt using VBS? If so, I sure would like to learn how. Thanks...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EdVassie SSC Guru Points: 60472 More actions · Answer 13

I used to make available a bunch of scripts from my old place that helped in migrating from SQL 2000 to 2005, one of which scripted tables. As they have my old employer's name all over them I no longer feel free to distribute them. The COM routines needed to do the scripting can all be called from the sp_OA* procedures, but I called tham direct from VB.

I am working on making them more generic and publishing them on this site but life is getting in the way of this work. I'll see if I can look at them at home and give more details.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

Thanks, Ed... and I fully understand about the "company name" thing... I gave almost the same answer on a similar request for code. Nice to see some "ethics".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)