TSQL Command to Generate a Table Script - by Leila S

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


8
1
Sign in
to vote
ID 176429 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 8/4/2006 2:24:09 PM
Access Restriction Public

Description

Hi,
There are many times that we wish to be able to script a table in server side procedures (using TSQL commands). You might need to create exactly similar table in different database. This feature also enables third party developers to provide users with some facilities to develop part of their own database using GUIs (use existing tables as a template or etc).
A make table query is not efficient because it cannot create indexes or triggers. I imagine this feature with a TSQL statement like this:

DECLARE @Script NVARCHAR(MAX)

CREATE TABLE SCRIPT FROM Table1 AS Table2
 INTO @Script WITH (TABLE=ON, INDEXES=ON, TRIGGERS=ON)

Table1 AS Table2:
This is useful when you have Table1 but you need to have "CREATE TABLE Table2" in the script (the same table structure but having different names). If renaming is not required, "Table1 AS Table1" can be specified.

TABLE=ON:
The user needs CREATE TABLE. If this is ignored, user is not interested in table structure, rather indexes or triggers are required.

INDEXES=ON
The script must include CREATE INDEX statements.

TRIGGERS=ON
The script must include CREATE TRIGGER statements, but it must be aware of whether table renaming has been requested in script in order to use appropriate table name in CREATE TRIGGER.

Now the user is able to do whatever he/she needs with @Script. For example: EXEC (@Script)
Sign in to post a comment.
Posted by Microsoft on 3/10/2011 at 2:59 PM
Hello,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to reopen it and we will take another look.

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 8/17/2006 at 9:30 AM
Hi
Thanks for your feedback. Currently the best way to generate scripts is by using SMO. We will look into ways of achieving what you propose (scripting in the server) in a future release.

Thanks,

- Christian Kleinerman