Complete definition of a full copy of SQL Server

Question

In the Enterprise version of SQL Server, you can create a create statement for a previously existing table using Script Table As, Create In. This generates a complete create statement for the table, including indexes. Is there a way to do this in T-SQL and therefore be able to copy the table definition into a new table?

Answer 1

Nothing simple out of the box in direct T-SQL, which exactly repeats what SSMS does.

However, you can get to this through metadata ( INFORMATION_SCHEMA.COLUMNS , etc.), and I did it quite successfully. Note that indexes are not in INFORMATION_SCHEMA - they are in sys.indexes.

Check out this article , which probably has most of what you want.

Answer 2

Create a script in a new query window, then change the database name and run it