HOW TO RETRIEVE THE STATEMENT USED TO CREATE THE TABLE

Question

HOW TO RETRIEVE THE STATEMENT USED TO CREATE THE TABLE

oageng_e_baruti

SSC Enthusiast

Points: 112
More actions
February 18, 2010 at 6:54 am

#215860

I want to see the statement that I used to create a table in sql server 2008. I've done this before, but I cannot remember how to do it. I've digging sources for a while and I'm not getting anywhere.
I want to get that CREATE TABLE statement that I used to build a table

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

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

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

Right click on the table in management studio select script table as then create to and lastly select option of where to write the script out.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

the original statement used to create a table is not saved/stored anywhere;

all you can do is generate a statement which would create the table; it probably would not be exactly the same as how you entered it, but it will create the exact same table.

you can right click on any table in SSMS..choose Script Table As....

If you want to do it via TSQL, I created a procedure called sp_getDDL that returns the table definition in TSQL you can give a spin and see if you like it.

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!

Paul White SSC Guru Points: 150468 More actions · Answer 3

Just to add a small thing to Carolyn's reply: you can customize the scripting output you get.

In SSMS go to Tools, then Options.

In the window that appears, navigate to SQL Server Object Explorer -> Scripting.

There are more options there than any sane person needs, but you can't say it isn't flexible.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

crazy4sql SSCoach Points: 19590 More actions · Answer 4

and if you want from SQL that it write script for you on the query window as well then click on any perticular table, drag it to query window and you will see the list of option for t-sql statements of create,alter,drop,select,insert,update,delete,execute.

hope it helps...

----------
Ashish

Paul White SSC Guru Points: 150468 More actions · Answer 5

ashish.kuriyal (2/19/2010)
and if you want from SQL that it write script for you on the query window as well then click on any perticular table, drag it to query window and you will see the list of option for t-sql statements of create,alter,drop,select,insert,update,delete,execute.

I don't quite understand what you mean.

In SSMS, when I drag a table name from Object Explorer to a query window, it just inserts the text in the form [schema].

Do you mean something else?

Or are you referring to another product / add-on?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

crazy4sql SSCoach Points: 19590 More actions · Answer 6

drag using right click, like we move our file in explorer from one location to other

----------
Ashish

Paul White SSC Guru Points: 150468 More actions · Answer 7

ashish.kuriyal (2/19/2010)
drag using right click, like we move our file in explorer from one location to other

From Object Explorer in SQL Server Management Studio to a query window?

Right-dragging, I get the usual 'Move Here/Copy Here/Cancel' window.

Do you have a reference for the behaviour you are describing - might be easier.

Sure you're not using a third-party tool?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

crazy4sql SSCoach Points: 19590 More actions · Answer 8

ok stepwise :-

1) click on any table in database.

2) Keep pressing the rightclick and move the table to query window.

3) leave the rightclick and you will be able to see the option.

----------
Ashish

Paul White SSC Guru Points: 150468 More actions · Answer 9

ashish.kuriyal (2/19/2010)
ok stepwise :-
1) click on any table in database.
2) Keep pressing the rightclick and move the table to query window.
3) leave the rightclick and you will be able to see the option.

:w00t: LOL :w00t:

I know how to right-drag!!! LOL

It doesn't work for me. Please provide a reference, or check to see whether you have used a third-party tool to extend SSMS functionality.

I don't think this is a built-in SQL Server Management Studio function.

:smooooth:

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

crazy4sql SSCoach Points: 19590 More actions · Answer 10

will send you stepwise screenshot

----------
Ashish

Paul White SSC Guru Points: 150468 More actions · Answer 11

ashish.kuriyal (2/19/2010)
will send you stepwise screenshot

Okay...;-)

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

oageng_e_baruti SSC Enthusiast Points: 112 More actions · Answer 12

I used SCRIPT TABLE AS ---> CREATE TO

It gave me the exact definition that I had used.

The RIGHT CLICK --->DRAG doesn't work. It didn't work. I think may be you might be having a third party tool or some kind of add-on ?

Thank you very much for your assistance

Paul White SSC Guru Points: 150468 More actions · Answer 13

oageng_e_baruti (2/19/2010)
I used SCRIPT TABLE AS ---> CREATE TO
It gave me the exact definition that I had used.
The RIGHT CLICK --->DRAG doesn't work. It didn't work. I think may be you might be having a third party tool or some kind of add-on ?
Thank you very much for your assistance

Thanks for the feedback 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

oageng_e_baruti SSC Enthusiast Points: 112 More actions · Answer 14

I was wondering if the rows (data) could be scripted out?

Hey I'm migrating a table from sql server express to sql server compact edition. Recreating everything from scratch is absolutely a tremendous amount of work !! By-the-way you know compact edition has so many limitations and constraints. I just needed the CREATE TABLE and INSERT INTO scripts for now. Then I could modify these to fit in the boundaries of sql compact edition.

So can this be done? Scripting out the INSERT? Or is there a work around solution?