TSQL Command to Generate a Table Script
as Won't Fix
8/4/2006 2:24:09 PM
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.
The user needs CREATE TABLE. If this is ignored, user is not interested in table structure, rather indexes or triggers are required.
The script must include CREATE INDEX statements.
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)