Creates an easily comparable database schema script.
Includes tables, views, constraints, indexes, stored procedures, functions, and triggers. Ignores creation date of objects to avoid false positives.
This stored procedure is useful for:
- Creating database documentation
- Comparing database schemas between environments
- Version control of database structure
- Database migration scripts
- Backup and restore procedures
There are other tools that do the same, but this is for environments and situations where you cannot install tooling. It also creates a consistent output.
Install in database
CREATE OR ALTER PROCEDURE [dbo].[GenerateDatabaseScript]
AS
BEGIN
SET NOCOUNT ON;
-- Print header
PRINT '-- =============================================';
PRINT '-- Database Schema Scripts';
PRINT '-- Generated: ' + CONVERT(VARCHAR, GETDATE(), 120);
PRINT '-- Server: ' + @@SERVERNAME;
PRINT '-- Database: ' + DB_NAME();
PRINT '-- =============================================';
PRINT '';
-- =============================================
-- Generate CREATE scripts for TABLES
-- =============================================
PRINT '-- =============================================';
PRINT '-- TABLES';
PRINT '-- =============================================';
PRINT '';
DECLARE @TableName NVARCHAR(128);
DECLARE @Schema NVARCHAR(128);
DECLARE @TableScript NVARCHAR(MAX);
DECLARE table_cursor CURSOR FOR
SELECT s.name, t.name
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
ORDER BY s.name, t.name;
OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @Schema, @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Generate table script
SET @TableScript = 'CREATE TABLE [' + @Schema + '].[' + @TableName + '] (' + CHAR(13) + CHAR(10);
-- Add columns
SELECT @TableScript = @TableScript +
' [' + c.name + '] ' +
CASE
WHEN c.is_computed = 1 THEN 'AS ' + OBJECT_DEFINITION(c.object_id, c.column_id)
ELSE
tp.name +
CASE
WHEN tp.name IN ('varchar', 'char', 'nvarchar', 'nchar')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END + ')'
WHEN tp.name IN ('decimal', 'numeric')
THEN '(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')'
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
END + ',' + CHAR(13) + CHAR(10)
FROM sys.columns c
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
WHERE c.object_id = OBJECT_ID('[' + @Schema + '].[' + @TableName + ']')
ORDER BY c.column_id;
-- Remove trailing comma and add closing parenthesis
SET @TableScript = LEFT(@TableScript, LEN(@TableScript) - 3) + CHAR(13) + CHAR(10) + ')';
PRINT @TableScript + ' GO';
PRINT '';
FETCH NEXT FROM table_cursor INTO @Schema, @TableName;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;
-- =============================================
-- Generate CREATE scripts for VIEWS
-- =============================================
PRINT '-- =============================================';
PRINT '-- VIEWS';
PRINT '-- =============================================';
PRINT '';
DECLARE @ViewName NVARCHAR(128);
DECLARE @ViewSchema NVARCHAR(128);
DECLARE @ViewScript NVARCHAR(MAX);
DECLARE view_cursor CURSOR FOR
SELECT s.name, v.name
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
ORDER BY s.name, v.name;
OPEN view_cursor;
FETCH NEXT FROM view_cursor INTO @ViewSchema, @ViewName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ViewScript = OBJECT_DEFINITION(OBJECT_ID('[' + @ViewSchema + '].[' + @ViewName + ']'));
-- Replace CREATE VIEW with CREATE OR ALTER VIEW
SET @ViewScript = REPLACE(@ViewScript, 'CREATE VIEW', 'CREATE OR ALTER VIEW');
PRINT @ViewScript + ' GO';
PRINT '';
FETCH NEXT FROM view_cursor INTO @ViewSchema, @ViewName;
END
CLOSE view_cursor;
DEALLOCATE view_cursor;
-- =============================================
-- Generate CREATE scripts for CONSTRAINTS
-- =============================================
PRINT '-- =============================================';
PRINT '-- CONSTRAINTS';
PRINT '-- =============================================';
PRINT '';
DECLARE @ConstraintName NVARCHAR(128);
DECLARE @ConstraintTableName NVARCHAR(128);
DECLARE @ConstraintSchema NVARCHAR(128);
DECLARE @ConstraintType CHAR(2);
DECLARE @ConstraintScript NVARCHAR(MAX);
DECLARE @ConstraintDefinition NVARCHAR(MAX);
DECLARE constraint_cursor CURSOR FOR
SELECT s.name, t.name, c.name, c.type
FROM sys.objects c
INNER JOIN sys.tables t ON c.parent_object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.type IN ('PK', 'UQ', 'F', 'C', 'D')
ORDER BY s.name, t.name, c.name;
OPEN constraint_cursor;
FETCH NEXT FROM constraint_cursor INTO @ConstraintSchema, @ConstraintTableName, @ConstraintName, @ConstraintType;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ConstraintScript = 'ALTER TABLE [' + @ConstraintSchema + '].[' + @ConstraintTableName + '] ADD CONSTRAINT [' + @ConstraintName + '] ';
IF @ConstraintType = 'PK'
BEGIN
SET @ConstraintScript = @ConstraintScript + 'PRIMARY KEY CLUSTERED (' +
STUFF((
SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = OBJECT_ID('[' + @ConstraintSchema + '].[' + @ConstraintTableName + ']')
AND ic.index_id = (SELECT index_id FROM sys.indexes WHERE object_id = OBJECT_ID('[' + @ConstraintSchema + '].[' + @ConstraintTableName + ']') AND is_primary_key = 1)
AND ic.key_ordinal > 0
ORDER BY ic.key_ordinal
FOR XML PATH('')
), 1, 2, '') + ')';
END
ELSE IF @ConstraintType = 'UQ'
BEGIN
SET @ConstraintScript = @ConstraintScript + 'UNIQUE CLUSTERED (' +
STUFF((
SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = OBJECT_ID('[' + @ConstraintSchema + '].[' + @ConstraintTableName + ']')
AND ic.index_id = (SELECT index_id FROM sys.indexes WHERE object_id = OBJECT_ID('[' + @ConstraintSchema + '].[' + @ConstraintTableName + ']') AND name = @ConstraintName)
AND ic.key_ordinal > 0
ORDER BY ic.key_ordinal
FOR XML PATH('')
), 1, 2, '') + ')';
END
ELSE IF @ConstraintType = 'F'
BEGIN
DECLARE @ReferencedTable NVARCHAR(128);
DECLARE @ReferencedSchema NVARCHAR(128);
SELECT @ReferencedTable = OBJECT_NAME(fk.referenced_object_id),
@ReferencedSchema = OBJECT_SCHEMA_NAME(fk.referenced_object_id)
FROM sys.foreign_keys fk
WHERE fk.object_id = OBJECT_ID('[' + @ConstraintSchema + '].[' + @ConstraintName + ']');
SET @ConstraintScript = @ConstraintScript + 'FOREIGN KEY (' +
STUFF((
SELECT ', [' + c.name + ']'
FROM sys.foreign_key_columns fkc
INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
WHERE fkc.constraint_object_id = OBJECT_ID('[' + @ConstraintSchema + '].[' + @ConstraintName + ']')
ORDER BY fkc.constraint_column_id
FOR XML PATH('')
), 1, 2, '') + ') REFERENCES [' +
@ReferencedSchema + '].[' +
@ReferencedTable + '] (' +
STUFF((
SELECT ', [' + c.name + ']'
FROM sys.foreign_key_columns fkc
INNER JOIN sys.columns c ON fkc.referenced_object_id = c.object_id AND fkc.referenced_column_id = c.column_id
WHERE fkc.constraint_object_id = OBJECT_ID('[' + @ConstraintSchema + '].[' + @ConstraintName + ']')
ORDER BY fkc.constraint_column_id
FOR XML PATH('')
), 1, 2, '') + ')';
END
ELSE IF @ConstraintType = 'C'
BEGIN
SELECT @ConstraintDefinition = cc.definition
FROM sys.check_constraints cc
WHERE cc.object_id = OBJECT_ID('[' + @ConstraintSchema + '].[' + @ConstraintName + ']');
SET @ConstraintScript = @ConstraintScript + 'CHECK (' + @ConstraintDefinition + ')';
END
ELSE IF @ConstraintType = 'D'
BEGIN
SELECT @ConstraintDefinition = dc.definition
FROM sys.default_constraints dc
WHERE dc.object_id = OBJECT_ID('[' + @ConstraintSchema + '].[' + @ConstraintName + ']');
SET @ConstraintScript = @ConstraintScript + 'DEFAULT ' + @ConstraintDefinition + ' FOR [' +
(SELECT c.name FROM sys.columns c
INNER JOIN sys.default_constraints dc ON c.object_id = dc.parent_object_id AND c.column_id = dc.parent_column_id
WHERE dc.object_id = OBJECT_ID('[' + @ConstraintSchema + '].[' + @ConstraintName + ']')) + ']';
END
PRINT @ConstraintScript + ' GO';
PRINT '';
FETCH NEXT FROM constraint_cursor INTO @ConstraintSchema, @ConstraintTableName, @ConstraintName, @ConstraintType;
END
CLOSE constraint_cursor;
DEALLOCATE constraint_cursor;
-- =============================================
-- Generate CREATE scripts for INDEXES
-- =============================================
PRINT '-- =============================================';
PRINT '-- INDEXES';
PRINT '-- =============================================';
PRINT '';
DECLARE @IndexName NVARCHAR(128);
DECLARE @IndexTableName NVARCHAR(128);
DECLARE @IndexSchema NVARCHAR(128);
DECLARE @IndexScript NVARCHAR(MAX);
DECLARE @IndexUnique BIT;
DECLARE @IndexType TINYINT;
DECLARE @IndexFilter NVARCHAR(MAX);
DECLARE @IndexPadded BIT;
DECLARE @IndexRowLocks BIT;
DECLARE @IndexPageLocks BIT;
DECLARE index_cursor CURSOR FOR
SELECT s.name, t.name, i.name, i.is_unique, i.type, i.filter_definition, i.is_padded, i.allow_row_locks, i.allow_page_locks
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0 -- Exclude primary keys and unique constraints
AND i.type IN (1, 2) -- Clustered and Nonclustered indexes
ORDER BY s.name, t.name, i.name;
OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @IndexSchema, @IndexTableName, @IndexName, @IndexUnique, @IndexType, @IndexFilter, @IndexPadded, @IndexRowLocks, @IndexPageLocks;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @IndexScript = 'CREATE ' +
CASE WHEN @IndexUnique = 1 THEN 'UNIQUE ' ELSE '' END +
CASE WHEN @IndexType = 1 THEN 'CLUSTERED ' WHEN @IndexType = 2 THEN 'NONCLUSTERED ' ELSE '' END +
'INDEX [' + @IndexName + '] ON [' + @IndexSchema + '].[' + @IndexTableName + '] (' +
STUFF((
SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = OBJECT_ID('[' + @IndexSchema + '].[' + @IndexTableName + ']')
AND ic.index_id = (SELECT index_id FROM sys.indexes WHERE object_id = OBJECT_ID('[' + @IndexSchema + '].[' + @IndexTableName + ']') AND name = @IndexName)
AND ic.key_ordinal > 0
ORDER BY ic.key_ordinal
FOR XML PATH('')
), 1, 2, '') + ')';
-- Add INCLUDE clause if exists
IF EXISTS (
SELECT 1 FROM sys.index_columns ic
WHERE ic.object_id = OBJECT_ID('[' + @IndexSchema + '].[' + @IndexTableName + ']')
AND ic.index_id = (SELECT index_id FROM sys.indexes WHERE object_id = OBJECT_ID('[' + @IndexSchema + '].[' + @IndexTableName + ']') AND name = @IndexName)
AND ic.key_ordinal = 0
)
BEGIN
SET @IndexScript = @IndexScript + ' INCLUDE (' +
STUFF((
SELECT ', [' + c.name + ']'
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = OBJECT_ID('[' + @IndexSchema + '].[' + @IndexTableName + ']')
AND ic.index_id = (SELECT index_id FROM sys.indexes WHERE object_id = OBJECT_ID('[' + @IndexSchema + '].[' + @IndexTableName + ']') AND name = @IndexName)
AND ic.key_ordinal = 0
ORDER BY ic.column_id
FOR XML PATH('')
), 1, 2, '') + ')';
END
-- Add filter if exists
IF @IndexFilter IS NOT NULL
SET @IndexScript = @IndexScript + ' WHERE ' + @IndexFilter;
-- Add options
SET @IndexScript = @IndexScript + ' WITH (' +
'PAD_INDEX = ' + CASE WHEN @IndexPadded = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
'STATISTICS_NORECOMPUTE = OFF, ' +
'SORT_IN_TEMPDB = OFF, ' +
'DROP_EXISTING = OFF, ' +
'ONLINE = OFF, ' +
'ALLOW_ROW_LOCKS = ' + CASE WHEN @IndexRowLocks = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
'ALLOW_PAGE_LOCKS = ' + CASE WHEN @IndexPageLocks = 1 THEN 'ON' ELSE 'OFF' END + ')';
PRINT @IndexScript + ' GO';
PRINT '';
FETCH NEXT FROM index_cursor INTO @IndexSchema, @IndexTableName, @IndexName, @IndexUnique, @IndexType, @IndexFilter, @IndexPadded, @IndexRowLocks, @IndexPageLocks;
END
CLOSE index_cursor;
DEALLOCATE index_cursor;
-- =============================================
-- Generate CREATE scripts for STORED PROCEDURES
-- =============================================
PRINT '-- =============================================';
PRINT '-- STORED PROCEDURES';
PRINT '-- =============================================';
PRINT '';
DECLARE @ProcName NVARCHAR(128);
DECLARE @ProcSchema NVARCHAR(128);
DECLARE @ProcScript NVARCHAR(MAX);
DECLARE proc_cursor CURSOR FOR
SELECT s.name, p.name
FROM sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
ORDER BY s.name, p.name;
OPEN proc_cursor;
FETCH NEXT FROM proc_cursor INTO @ProcSchema, @ProcName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ProcScript = OBJECT_DEFINITION(OBJECT_ID('[' + @ProcSchema + '].[' + @ProcName + ']'));
-- Replace CREATE PROCEDURE with CREATE OR ALTER PROCEDURE
SET @ProcScript = REPLACE(@ProcScript, 'CREATE PROCEDURE', 'CREATE OR ALTER PROCEDURE');
PRINT @ProcScript + ' GO';
PRINT '';
FETCH NEXT FROM proc_cursor INTO @ProcSchema, @ProcName;
END
CLOSE proc_cursor;
DEALLOCATE proc_cursor;
-- =============================================
-- Generate CREATE scripts for FUNCTIONS
-- =============================================
PRINT '-- =============================================';
PRINT '-- FUNCTIONS';
PRINT '-- =============================================';
PRINT '';
DECLARE @FuncName NVARCHAR(128);
DECLARE @FuncSchema NVARCHAR(128);
DECLARE @FuncScript NVARCHAR(MAX);
DECLARE func_cursor CURSOR FOR
SELECT s.name, f.name
FROM sys.objects f
INNER JOIN sys.schemas s ON f.schema_id = s.schema_id
WHERE f.type IN ('FN', 'IF', 'TF') -- Scalar, Inline Table-Valued, Table-Valued Functions
ORDER BY s.name, f.name;
OPEN func_cursor;
FETCH NEXT FROM func_cursor INTO @FuncSchema, @FuncName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FuncScript = OBJECT_DEFINITION(OBJECT_ID('[' + @FuncSchema + '].[' + @FuncName + ']'));
-- Replace CREATE FUNCTION with CREATE OR ALTER FUNCTION
SET @FuncScript = REPLACE(@FuncScript, 'CREATE FUNCTION', 'CREATE OR ALTER FUNCTION');
PRINT @FuncScript + ' GO';
PRINT '';
FETCH NEXT FROM func_cursor INTO @FuncSchema, @FuncName;
END
CLOSE func_cursor;
DEALLOCATE func_cursor;
-- =============================================
-- Generate CREATE scripts for TRIGGERS
-- =============================================
PRINT '-- =============================================';
PRINT '-- TRIGGERS';
PRINT '-- =============================================';
PRINT '';
DECLARE @TriggerName NVARCHAR(128);
DECLARE @TriggerTableName NVARCHAR(128);
DECLARE @TriggerSchema NVARCHAR(128);
DECLARE @TriggerScript NVARCHAR(MAX);
DECLARE trigger_cursor CURSOR FOR
SELECT s.name, t.name, tr.name
FROM sys.triggers tr
INNER JOIN sys.tables t ON tr.parent_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
ORDER BY s.name, t.name, tr.name;
OPEN trigger_cursor;
FETCH NEXT FROM trigger_cursor INTO @TriggerSchema, @TriggerTableName, @TriggerName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TriggerScript = OBJECT_DEFINITION(OBJECT_ID('[' + @TriggerSchema + '].[' + @TriggerName + ']'));
-- Replace CREATE TRIGGER with CREATE OR ALTER TRIGGER
SET @TriggerScript = REPLACE(@TriggerScript, 'CREATE TRIGGER', 'CREATE OR ALTER TRIGGER');
PRINT @TriggerScript + ' GO';
PRINT '';
FETCH NEXT FROM trigger_cursor INTO @TriggerSchema, @TriggerTableName, @TriggerName;
END
CLOSE trigger_cursor;
DEALLOCATE trigger_cursor;
-- Footer
PRINT '-- =============================================';
PRINT '-- End of Scripts';
PRINT '-- =============================================';
END
GO
Usage
EXEC dbo.GenerateDatabaseScript
Output
The procedure generates a complete database schema script with the following sections:
- TABLES - CREATE TABLE statements with all columns, data types, nullability, and defaults
- VIEWS - CREATE OR ALTER VIEW statements
- CONSTRAINTS - Primary keys, unique constraints, foreign keys, check constraints, and default constraints
- INDEXES - All non-primary key indexes with their properties
- STORED PROCEDURES - CREATE OR ALTER PROCEDURE statements
- FUNCTIONS - CREATE OR ALTER FUNCTION statements (scalar, inline, and table-valued)
- TRIGGERS - CREATE OR ALTER TRIGGER statements
Features
- Generates scripts in dependency order (tables first, then constraints, then indexes)
- Uses CREATE OR ALTER for views, procedures, functions, and triggers
- Includes all index properties (clustering, uniqueness, included columns, filters)
- Handles computed columns and default constraints
- Supports multiple schemas
- Includes metadata header with generation timestamp and server information
Notes
- The script excludes system objects (MSShipped = 0)
- Indexes exclude primary keys and unique constraints as they're handled in the constraints section
- All generated scripts use proper bracketing for object names
- The output can be redirected to a file for version control or documentation