Generating CREATE Scripts for Existing Tables in SQL Server

Introduction

In database management, there is often a need to generate CREATE scripts for existing tables, which can be useful for documentation, migration, or version control purposes. However, querying system views like sys.tables only returns basic metadata and does not provide the full CREATE script, including detailed column definitions, constraints, and indexes.

Method Overview

To generate a complete CREATE script, one can utilize SQL Server's system catalog views, such as sys.objects, sys.columns, sys.types, sys.key_constraints, sys.foreign_keys, and others. By dynamically constructing a SQL statement, the script can be assembled piece by piece to include table structure, primary keys, foreign keys, default values, computed columns, and indexes.

Code Implementation

The following T-SQL script demonstrates how to generate the CREATE script for a specified table. It handles columns with their data types, nullability, default values, identity properties, computed columns, primary key constraints, foreign key constraints, and non-clustered indexes.

DECLARE @table_name SYSNAME = 'dbo.WorkOut';
DECLARE @object_id INT;
SELECT @object_id = OBJECT_ID(@table_name);

IF @object_id IS NULL
BEGIN
    PRINT 'Table not found.';
    RETURN;
END

DECLARE @SQL NVARCHAR(MAX) = '';

-- Build column definitions
SELECT @SQL = @SQL + 
    CASE WHEN c.column_id = 1 THEN '' ELSE ',' END + 
    ' [' + c.name + '] ' + 
    CASE 
        WHEN c.is_computed = 1 THEN 'AS ' + cc.definition
        ELSE 
            UPPER(t.name) + 
            CASE 
                WHEN t.name IN ('varchar', 'char', 'varbinary', 'binary') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END + ')'
                WHEN t.name IN ('nvarchar', 'nchar') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length/2 AS VARCHAR) END + ')'
                WHEN t.name IN ('decimal', 'numeric') THEN '(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')'
                ELSE ''
            END +
            CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
            CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
            CASE WHEN dc.definition IS NOT NULL THEN ' DEFAULT ' + dc.definition ELSE '' END +
            CASE WHEN ic.seed_value IS NOT NULL THEN ' IDENTITY(' + CAST(ic.seed_value AS VARCHAR) + ',' + CAST(ic.increment_value AS VARCHAR) + ')' ELSE '' END
    END + CHAR(13)
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN sys.computed_columns cc ON c.object_id = cc.object_id AND c.column_id = cc.column_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN sys.identity_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE c.object_id = @object_id
ORDER BY c.column_id;

-- Add primary key constraint
SELECT @SQL = @SQL + 
    ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' + 
    STUFF((
        SELECT ', [' + col.name + ']' + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
        FROM sys.index_columns ic
        JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
        WHERE ic.object_id = k.parent_object_id AND ic.index_id = k.unique_index_id AND ic.is_included_column = 0
        FOR XML PATH('')), 1, 2, '') + 
    ')' + CHAR(13)
FROM sys.key_constraints k
WHERE k.parent_object_id = @object_id AND k.type = 'PK';

-- Set the full CREATE TABLE statement
SET @SQL = 'CREATE TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(@object_id)) + '.' + QUOTENAME(OBJECT_NAME(@object_id)) + CHAR(13) + '(' + CHAR(13) + @SQL + CHAR(13) + ');';

-- Add foreign keys and indexes (simplified version; full implementation refers to original answer)
PRINT @SQL;
-- EXEC sp_executesql @SQL; -- Uncomment to execute

This script first retrieves the object ID for the specified table. It then constructs column definitions by querying sys.columns and related views, handling data types, nullability, defaults, identity, and computed columns. Next, it adds the primary key constraint using STUFF and FOR XML PATH for column concatenation. Foreign keys and indexes can be added in a similar manner.

Example Output

For the table dbo.WorkOut, the generated script might look like:

CREATE TABLE [dbo].[WorkOut] (
    [WorkOutID] BIGINT NOT NULL IDENTITY(1,1),
    [TimeSheetDate] DATETIME NOT NULL,
    [DateOut] DATETIME NOT NULL,
    [EmployeeID] INT NOT NULL,
    [IsMainWorkPlace] BIT NOT NULL DEFAULT ((1)),
    [DepartmentUID] UNIQUEIDENTIFIER NOT NULL,
    [WorkPlaceUID] UNIQUEIDENTIFIER NULL,
    [TeamUID] UNIQUEIDENTIFIER NULL,
    [WorkShiftCD] NVARCHAR(10) COLLATE Cyrillic_General_CI_AS NULL,
    [WorkHours] REAL NULL,
    [AbsenceCode] VARCHAR(25) COLLATE Cyrillic_General_CI_AS NULL,
    [PaymentType] CHAR(2) COLLATE Cyrillic_General_CI_AS NULL,
    [CategoryID] INT NULL,
    [Year] AS (DATEPART(year, [TimeSheetDate])),
    CONSTRAINT [PK_WorkOut] PRIMARY KEY ([WorkOutID] ASC)
);

Additional statements for foreign keys and indexes would be generated if present.

Conclusion

Generating CREATE scripts for existing tables in SQL Server is essential for various database management tasks. The provided script offers a flexible approach that can be customized as needed. Always test the generated scripts in a safe environment before applying them to production databases to ensure accuracy.