How Can I Generate SQL CREATE Scripts for Existing Tables Using a Query?

Generate SQL CREATE Statements for Existing Tables Using a Query

Need to quickly generate CREATE TABLE statements for your existing SQL Server tables? This guide shows how to achieve this using a query, leveraging system tables like sys.tables.

One method involves building a dynamic SQL statement. The basic structure starts like this:

<code class="language-sql">DECLARE @sql NVARCHAR(MAX) = 'CREATE TABLE [' + @table_name + '] (';</code>

This dynamically constructs the initial part of the CREATE TABLE statement, replacing @table_name with the actual table name.

Next, you'll append column details: name, data type, length, nullability, and any constraints. Here's an example for a VARCHAR column named CustomerName:

<code class="language-sql">@sql += CHAR(9) + ', [' + c.name + '] ' + UPPER(tp.name) + ' (' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')' + CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END + CHAR(13);</code>

This snippet adds the column definition, handling MAX lengths appropriately and specifying nullability. You would repeat this for each column.

Finally, you'll add primary and foreign key constraints. After constructing the complete CREATE TABLE statement, use:

<code class="language-sql">PRINT CAST(@sql AS NTEXT);</code>

to display the generated script. Note that the exact script will vary depending on your table's specific structure and constraints.

The above is the detailed content of How Can I Generate SQL CREATE Scripts for Existing Tables Using a Query?. For more information, please follow other related articles on the PHP Chinese website!