Home Dashboard Directory Help
Search

TSQL Command to Generate a Table Script by Leila S


Status: 

Closed
 as Won't Fix Help for as Won't Fix


8
1
Sign in
to vote
Type: Suggestion
ID: 176429
Opened: 8/4/2006 2:24:09 PM
Access Restriction: Public
0
Workaround(s)
view

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)
Details
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
Sign in to post a workaround.