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.
Execute
SET NOCOUNT ON;
DECLARE @UsePrintOutput BIT = 1;
DECLARE @PrintChunkSize INT = 3900;
DECLARE @Output TABLE
(
SortOrder INT IDENTITY(1,1) PRIMARY KEY,
ScriptLine NVARCHAR(MAX) NOT NULL
);
INSERT INTO @Output (ScriptLine)
VALUES
('-- ============================================='),
('-- Database Schema Scripts'),
('-- Generated: ' + CONVERT(VARCHAR(19), GETDATE(), 120)),
('-- Server: ' + @@SERVERNAME),
('-- Database: ' + DB_NAME()),
('-- ============================================='),
('');
-- =============================================
-- Generate CREATE scripts for TABLES
-- =============================================
INSERT INTO @Output (ScriptLine)
VALUES
('-- ============================================='),
('-- TABLES'),
('-- ============================================='),
('');
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) + ')';
INSERT INTO @Output (ScriptLine) VALUES (@TableScript);
INSERT INTO @Output (ScriptLine) VALUES ('GO');
INSERT INTO @Output (ScriptLine) VALUES ('');
FETCH NEXT FROM table_cursor INTO @Schema, @TableName;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;
-- =============================================
-- Generate CREATE scripts for VIEWS
-- =============================================
INSERT INTO @Output (ScriptLine)
VALUES
('-- ============================================='),
('-- VIEWS'),
('-- ============================================='),
('');
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 + ']'));
IF @ViewScript IS NOT NULL
BEGIN
DECLARE @UpperViewScript NVARCHAR(MAX) = UPPER(@ViewScript);
DECLARE @CreateViewPos INT = PATINDEX('%CREATE VIEW%', @UpperViewScript);
IF @CreateViewPos > 0
SET @ViewScript = STUFF(@ViewScript, @CreateViewPos, LEN('CREATE VIEW'), 'CREATE OR ALTER VIEW');
INSERT INTO @Output (ScriptLine) VALUES (@ViewScript);
INSERT INTO @Output (ScriptLine) VALUES ('GO');
INSERT INTO @Output (ScriptLine) VALUES ('');
END
FETCH NEXT FROM view_cursor INTO @ViewSchema, @ViewName;
END
CLOSE view_cursor;
DEALLOCATE view_cursor;
-- =============================================
-- Generate CREATE scripts for CONSTRAINTS
-- =============================================
INSERT INTO @Output (ScriptLine)
VALUES
('-- ============================================='),
('-- CONSTRAINTS'),
('-- ============================================='),
('');
DECLARE @ConstraintName NVARCHAR(128);
DECLARE @ConstraintTableName NVARCHAR(128);
DECLARE @ConstraintSchema NVARCHAR(128);
DECLARE @ConstraintType CHAR(2);
DECLARE @ConstraintObjectId INT;
DECLARE @ConstraintScript NVARCHAR(MAX);
DECLARE @ConstraintDefinition NVARCHAR(MAX);
DECLARE @ConstraintColumns NVARCHAR(MAX);
DECLARE @ReferencedColumns NVARCHAR(MAX);
DECLARE @DefaultColumnName NVARCHAR(128);
DECLARE @DefaultParentObjectId INT;
DECLARE @DefaultParentColumnId INT;
DECLARE @GenerationErrorMessage NVARCHAR(2048);
DECLARE @SkipConstraint BIT;
DECLARE @ConstraintSkipReason NVARCHAR(2048);
DECLARE constraint_cursor CURSOR FOR
SELECT s.name, t.name, c.name, c.type, c.object_id
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, @ConstraintObjectId;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SkipConstraint = 0;
SET @ConstraintSkipReason = NULL;
SET @ConstraintScript = CONCAT(
'ALTER TABLE [', @ConstraintSchema, '].[', @ConstraintTableName, '] ADD CONSTRAINT [', @ConstraintName, '] '
);
IF @ConstraintType = 'PK'
BEGIN
SET @ConstraintColumns = ISNULL(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, ''), N'');
IF LEN(@ConstraintColumns) = 0
BEGIN
SET @SkipConstraint = 1;
SET @ConstraintSkipReason = 'Unable to build PK columns.';
END
IF @SkipConstraint = 0
SET @ConstraintScript = CONCAT(@ConstraintScript, 'PRIMARY KEY CLUSTERED (', @ConstraintColumns, ')');
END
ELSE IF @ConstraintType = 'UQ'
BEGIN
SET @ConstraintColumns = ISNULL(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, ''), N'');
IF LEN(@ConstraintColumns) = 0
BEGIN
SET @SkipConstraint = 1;
SET @ConstraintSkipReason = 'Unable to build UQ columns.';
END
IF @SkipConstraint = 0
SET @ConstraintScript = CONCAT(@ConstraintScript, 'UNIQUE CLUSTERED (', @ConstraintColumns, ')');
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 = @ConstraintObjectId;
IF @ReferencedSchema IS NULL OR @ReferencedTable IS NULL
BEGIN
SET @SkipConstraint = 1;
SET @ConstraintSkipReason = 'Unable to resolve referenced table for FK.';
END
SET @ConstraintColumns = ISNULL(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 = @ConstraintObjectId
ORDER BY fkc.constraint_column_id
FOR XML PATH('')
), 1, 2, ''), N'');
SET @ReferencedColumns = ISNULL(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 = @ConstraintObjectId
ORDER BY fkc.constraint_column_id
FOR XML PATH('')
), 1, 2, ''), N'');
IF LEN(@ConstraintColumns) = 0 OR LEN(@ReferencedColumns) = 0
BEGIN
SET @SkipConstraint = 1;
IF @ConstraintSkipReason IS NULL
SET @ConstraintSkipReason = 'Unable to build FK columns.';
END
IF @SkipConstraint = 0
SET @ConstraintScript = CONCAT(
@ConstraintScript,
'FOREIGN KEY (', @ConstraintColumns, ') REFERENCES [',
@ReferencedSchema, '].[', @ReferencedTable, '] (', @ReferencedColumns, ')'
);
END
ELSE IF @ConstraintType = 'C'
BEGIN
SET @ConstraintDefinition = NULL;
SELECT @ConstraintDefinition = cc.definition
FROM sys.check_constraints cc
WHERE cc.object_id = @ConstraintObjectId;
IF @ConstraintDefinition IS NULL
BEGIN
SET @SkipConstraint = 1;
SET @ConstraintSkipReason = 'Unable to resolve CHECK definition.';
END
IF @SkipConstraint = 0
SET @ConstraintScript = CONCAT(@ConstraintScript, 'CHECK (', @ConstraintDefinition, ')');
END
ELSE IF @ConstraintType = 'D'
BEGIN
SET @ConstraintDefinition = NULL;
SET @DefaultColumnName = NULL;
SET @DefaultParentObjectId = NULL;
SET @DefaultParentColumnId = NULL;
SELECT
@ConstraintDefinition = dc.definition,
@DefaultColumnName = c.name,
@DefaultParentObjectId = dc.parent_object_id,
@DefaultParentColumnId = dc.parent_column_id
FROM sys.default_constraints dc
LEFT JOIN sys.columns c ON c.object_id = dc.parent_object_id AND c.column_id = dc.parent_column_id
WHERE dc.object_id = @ConstraintObjectId;
IF @DefaultColumnName IS NULL AND @DefaultParentObjectId IS NOT NULL AND @DefaultParentColumnId IS NOT NULL
SET @DefaultColumnName = COL_NAME(@DefaultParentObjectId, @DefaultParentColumnId);
IF @ConstraintDefinition IS NULL OR @DefaultColumnName IS NULL
BEGIN
SET @SkipConstraint = 1;
SET @ConstraintSkipReason = 'Unable to resolve DEFAULT details.';
END
IF @SkipConstraint = 0
SET @ConstraintScript = CONCAT(@ConstraintScript, 'DEFAULT ', @ConstraintDefinition, ' FOR [', @DefaultColumnName, ']');
END
IF @SkipConstraint = 1
BEGIN
SET @ConstraintScript = CONCAT(
'-- SKIPPED CONSTRAINT [', @ConstraintSchema, '].[', @ConstraintTableName, '].[', @ConstraintName, '] (', @ConstraintType, '): ',
@ConstraintSkipReason
);
END
ELSE IF @ConstraintScript IS NULL OR LEN(@ConstraintScript) = 0
BEGIN
SET @ConstraintScript = CONCAT(
'-- SKIPPED CONSTRAINT [', @ConstraintSchema, '].[', @ConstraintTableName, '].[', @ConstraintName, '] (', @ConstraintType, '): Generated empty script.'
);
END
INSERT INTO @Output (ScriptLine) VALUES (@ConstraintScript);
INSERT INTO @Output (ScriptLine) VALUES ('GO');
INSERT INTO @Output (ScriptLine) VALUES ('');
FETCH NEXT FROM constraint_cursor INTO @ConstraintSchema, @ConstraintTableName, @ConstraintName, @ConstraintType, @ConstraintObjectId;
END
CLOSE constraint_cursor;
DEALLOCATE constraint_cursor;
-- =============================================
-- Generate CREATE scripts for INDEXES
-- =============================================
INSERT INTO @Output (ScriptLine)
VALUES
('-- ============================================='),
('-- INDEXES'),
('-- ============================================='),
('');
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 @ConstraintColumns = ISNULL(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, ''), N'');
IF LEN(@ConstraintColumns) = 0
BEGIN
SET @GenerationErrorMessage = CONCAT('Unable to build key columns for index [', @IndexSchema, '].[', @IndexName, '].');
THROW 50001, @GenerationErrorMessage, 1;
END
SET @IndexScript = CONCAT(
'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, '] (', @ConstraintColumns, ')'
);
-- 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 @ReferencedColumns = ISNULL(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, ''), N'');
IF LEN(@ReferencedColumns) = 0
BEGIN
SET @GenerationErrorMessage = CONCAT('Unable to build INCLUDE columns for index [', @IndexSchema, '].[', @IndexName, '].');
THROW 50001, @GenerationErrorMessage, 1;
END
SET @IndexScript = CONCAT(@IndexScript, ' INCLUDE (', @ReferencedColumns, ')');
END
-- Add filter if exists
IF @IndexFilter IS NOT NULL
SET @IndexScript = CONCAT(@IndexScript, ' WHERE ', @IndexFilter);
-- Add options
SET @IndexScript = CONCAT(
@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, ')'
);
IF @IndexScript IS NULL OR LEN(@IndexScript) = 0
BEGIN
SET @GenerationErrorMessage = CONCAT('Generated empty script for index [', @IndexSchema, '].[', @IndexName, '].');
THROW 50001, @GenerationErrorMessage, 1;
END
INSERT INTO @Output (ScriptLine) VALUES (@IndexScript);
INSERT INTO @Output (ScriptLine) VALUES ('GO');
INSERT INTO @Output (ScriptLine) VALUES ('');
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
-- =============================================
INSERT INTO @Output (ScriptLine)
VALUES
('-- ============================================='),
('-- STORED PROCEDURES'),
('-- ============================================='),
('');
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 + ']'));
IF @ProcScript IS NOT NULL
BEGIN
DECLARE @UpperProcScript NVARCHAR(MAX) = UPPER(@ProcScript);
DECLARE @ProcedurePos INT = PATINDEX('%PROCEDURE%', @UpperProcScript);
DECLARE @ProcPos INT = PATINDEX('%PROC%', @UpperProcScript);
DECLARE @TargetProcKeywordPos INT = CASE WHEN @ProcedurePos > 0 THEN @ProcedurePos ELSE @ProcPos END;
DECLARE @PrefixBeforeProc NVARCHAR(MAX);
DECLARE @CreatePos INT;
DECLARE @AlterPos INT;
DECLARE @NextPos INT;
DECLARE @TokenPos INT;
DECLARE @TokenLen INT;
DECLARE @TextBetweenTokenAndProc NVARCHAR(MAX);
DECLARE @UpperBetweenTokenAndProc NVARCHAR(MAX);
IF @TargetProcKeywordPos > 0
BEGIN
SET @PrefixBeforeProc = SUBSTRING(@UpperProcScript, 1, @TargetProcKeywordPos - 1);
SET @CreatePos = CHARINDEX('CREATE', @PrefixBeforeProc);
SET @NextPos = @CreatePos;
WHILE @NextPos > 0
BEGIN
SET @NextPos = CHARINDEX('CREATE', @PrefixBeforeProc, @NextPos + 1);
IF @NextPos > 0
SET @CreatePos = @NextPos;
END
SET @AlterPos = CHARINDEX('ALTER', @PrefixBeforeProc);
SET @NextPos = @AlterPos;
WHILE @NextPos > 0
BEGIN
SET @NextPos = CHARINDEX('ALTER', @PrefixBeforeProc, @NextPos + 1);
IF @NextPos > 0
SET @AlterPos = @NextPos;
END
IF @CreatePos > @AlterPos
BEGIN
SET @TokenPos = @CreatePos;
SET @TokenLen = LEN('CREATE');
END
ELSE IF @AlterPos > 0
BEGIN
SET @TokenPos = @AlterPos;
SET @TokenLen = LEN('ALTER');
END
ELSE
BEGIN
SET @TokenPos = 0;
SET @TokenLen = 0;
END
IF @TokenPos > 0
BEGIN
SET @TextBetweenTokenAndProc = SUBSTRING(
@UpperProcScript,
@TokenPos + @TokenLen,
@TargetProcKeywordPos - (@TokenPos + @TokenLen)
);
SET @UpperBetweenTokenAndProc = UPPER(LTRIM(RTRIM(@TextBetweenTokenAndProc)));
IF @TokenLen = LEN('ALTER')
BEGIN
SET @ProcScript = STUFF(@ProcScript, @TokenPos, LEN('ALTER'), 'CREATE OR ALTER');
END
ELSE IF LEFT(@UpperBetweenTokenAndProc, LEN('OR ALTER')) <> 'OR ALTER'
BEGIN
SET @ProcScript = STUFF(@ProcScript, @TokenPos + LEN('CREATE'), 0, ' OR ALTER');
END
END
END
INSERT INTO @Output (ScriptLine) VALUES (@ProcScript);
INSERT INTO @Output (ScriptLine) VALUES ('GO');
INSERT INTO @Output (ScriptLine) VALUES ('');
END
FETCH NEXT FROM proc_cursor INTO @ProcSchema, @ProcName;
END
CLOSE proc_cursor;
DEALLOCATE proc_cursor;
-- =============================================
-- Generate CREATE scripts for FUNCTIONS
-- =============================================
INSERT INTO @Output (ScriptLine)
VALUES
('-- ============================================='),
('-- FUNCTIONS'),
('-- ============================================='),
('');
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 + ']'));
IF @FuncScript IS NOT NULL
BEGIN
DECLARE @UpperFuncScript NVARCHAR(MAX) = UPPER(@FuncScript);
DECLARE @FunctionPos INT = PATINDEX('%FUNCTION%', @UpperFuncScript);
DECLARE @PrefixBeforeFunction NVARCHAR(MAX);
DECLARE @CreateFunctionPos INT;
DECLARE @AlterFunctionPos INT;
DECLARE @NextFunctionPos INT;
DECLARE @FunctionTokenPos INT;
DECLARE @FunctionTokenLen INT;
DECLARE @TextBetweenTokenAndFunction NVARCHAR(MAX);
DECLARE @UpperBetweenTokenAndFunction NVARCHAR(MAX);
IF @FunctionPos > 0
BEGIN
SET @PrefixBeforeFunction = SUBSTRING(@UpperFuncScript, 1, @FunctionPos - 1);
SET @CreateFunctionPos = CHARINDEX('CREATE', @PrefixBeforeFunction);
SET @NextFunctionPos = @CreateFunctionPos;
WHILE @NextFunctionPos > 0
BEGIN
SET @NextFunctionPos = CHARINDEX('CREATE', @PrefixBeforeFunction, @NextFunctionPos + 1);
IF @NextFunctionPos > 0
SET @CreateFunctionPos = @NextFunctionPos;
END
SET @AlterFunctionPos = CHARINDEX('ALTER', @PrefixBeforeFunction);
SET @NextFunctionPos = @AlterFunctionPos;
WHILE @NextFunctionPos > 0
BEGIN
SET @NextFunctionPos = CHARINDEX('ALTER', @PrefixBeforeFunction, @NextFunctionPos + 1);
IF @NextFunctionPos > 0
SET @AlterFunctionPos = @NextFunctionPos;
END
IF @CreateFunctionPos > @AlterFunctionPos
BEGIN
SET @FunctionTokenPos = @CreateFunctionPos;
SET @FunctionTokenLen = LEN('CREATE');
END
ELSE IF @AlterFunctionPos > 0
BEGIN
SET @FunctionTokenPos = @AlterFunctionPos;
SET @FunctionTokenLen = LEN('ALTER');
END
ELSE
BEGIN
SET @FunctionTokenPos = 0;
SET @FunctionTokenLen = 0;
END
IF @FunctionTokenPos > 0
BEGIN
SET @TextBetweenTokenAndFunction = SUBSTRING(
@UpperFuncScript,
@FunctionTokenPos + @FunctionTokenLen,
@FunctionPos - (@FunctionTokenPos + @FunctionTokenLen)
);
SET @UpperBetweenTokenAndFunction = UPPER(LTRIM(RTRIM(@TextBetweenTokenAndFunction)));
IF @FunctionTokenLen = LEN('ALTER')
BEGIN
SET @FuncScript = STUFF(@FuncScript, @FunctionTokenPos, LEN('ALTER'), 'CREATE OR ALTER');
END
ELSE IF LEFT(@UpperBetweenTokenAndFunction, LEN('OR ALTER')) <> 'OR ALTER'
BEGIN
SET @FuncScript = STUFF(@FuncScript, @FunctionTokenPos + LEN('CREATE'), 0, ' OR ALTER');
END
END
END
INSERT INTO @Output (ScriptLine) VALUES (@FuncScript);
INSERT INTO @Output (ScriptLine) VALUES ('GO');
INSERT INTO @Output (ScriptLine) VALUES ('');
END
FETCH NEXT FROM func_cursor INTO @FuncSchema, @FuncName;
END
CLOSE func_cursor;
DEALLOCATE func_cursor;
-- =============================================
-- Generate CREATE scripts for TRIGGERS
-- =============================================
INSERT INTO @Output (ScriptLine)
VALUES
('-- ============================================='),
('-- TRIGGERS'),
('-- ============================================='),
('');
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 + ']'));
IF @TriggerScript IS NOT NULL
BEGIN
DECLARE @UpperTriggerScript NVARCHAR(MAX) = UPPER(@TriggerScript);
DECLARE @CreateTriggerPos INT = PATINDEX('%CREATE TRIGGER%', @UpperTriggerScript);
IF @CreateTriggerPos > 0
SET @TriggerScript = STUFF(@TriggerScript, @CreateTriggerPos, LEN('CREATE TRIGGER'), 'CREATE OR ALTER TRIGGER');
INSERT INTO @Output (ScriptLine) VALUES (@TriggerScript);
INSERT INTO @Output (ScriptLine) VALUES ('GO');
INSERT INTO @Output (ScriptLine) VALUES ('');
END
FETCH NEXT FROM trigger_cursor INTO @TriggerSchema, @TriggerTableName, @TriggerName;
END
CLOSE trigger_cursor;
DEALLOCATE trigger_cursor;
INSERT INTO @Output (ScriptLine)
VALUES
('-- ============================================='),
('-- End of Scripts'),
('-- =============================================');
IF @UsePrintOutput = 1
BEGIN
DECLARE @PrintLine NVARCHAR(MAX);
DECLARE @PrintRemaining NVARCHAR(MAX);
DECLARE @PrintLogicalLine NVARCHAR(MAX);
DECLARE @LineBreakPos INT;
DECLARE @PrintOffset INT;
DECLARE @PrintLineLength INT;
DECLARE print_cursor CURSOR FOR
SELECT ScriptLine
FROM @Output
ORDER BY SortOrder;
OPEN print_cursor;
FETCH NEXT FROM print_cursor INTO @PrintLine;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PrintRemaining = REPLACE(@PrintLine, CHAR(13), '');
WHILE 1 = 1
BEGIN
SET @LineBreakPos = CHARINDEX(CHAR(10), @PrintRemaining);
IF @LineBreakPos > 0
BEGIN
SET @PrintLogicalLine = SUBSTRING(@PrintRemaining, 1, @LineBreakPos - 1);
SET @PrintRemaining = SUBSTRING(@PrintRemaining, @LineBreakPos + 1, LEN(@PrintRemaining));
END
ELSE
BEGIN
SET @PrintLogicalLine = @PrintRemaining;
SET @PrintRemaining = N'';
END
SET @PrintOffset = 1;
SET @PrintLineLength = LEN(@PrintLogicalLine);
IF @PrintLineLength = 0
BEGIN
PRINT '';
END
ELSE
BEGIN
WHILE @PrintOffset <= @PrintLineLength
BEGIN
PRINT SUBSTRING(@PrintLogicalLine, @PrintOffset, @PrintChunkSize);
SET @PrintOffset = @PrintOffset + @PrintChunkSize;
END
END
IF @LineBreakPos = 0
BEGIN
BREAK;
END
END
FETCH NEXT FROM print_cursor INTO @PrintLine;
END
CLOSE print_cursor;
DEALLOCATE print_cursor;
END
ELSE
BEGIN
SELECT ScriptLine
FROM @Output
ORDER BY SortOrder;
END
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
- No install step required; execute the script directly
- The output can be redirected to a file for version control or documentation
Split output into files
After saving the generated schema SQL to disk, use the PowerShell splitter script in this folder:
.\Split-DatabaseSchema.ps1
Defaults:
- Input file:
database-schema.sql(same folder as the script) - Output folder:
split-schema(same folder as the script)
Example with explicit paths and overwrite:
.\Split-DatabaseSchema.ps1 -InputFile ".\database-schema.sql" -OutputFolder ".\split-schema" -Overwrite
The script creates one file per object type and object name. Table files include their related indexes. Type folders are only created when they contain items.
Splitter script:
[CmdletBinding()]
param(
[Parameter(Mandatory = $false)]
[string]$InputFile = (Join-Path -Path $PSScriptRoot -ChildPath 'database.sql'),
[Parameter(Mandatory = $false)]
[string]$OutputFolder = (Join-Path -Path $PSScriptRoot -ChildPath '.'),
[Parameter(Mandatory = $false)]
[switch]$Overwrite
)
Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'
if (-not (Test-Path -Path $InputFile -PathType Leaf)) {
throw "Input file was not found: $InputFile"
}
$resolvedInputFile = (Resolve-Path -Path $InputFile).Path
$resolvedInputFolder = Split-Path -Path $resolvedInputFile -Parent
$resolvedOutputFolder = if ([System.IO.Path]::IsPathRooted($OutputFolder)) {
[System.IO.Path]::GetFullPath($OutputFolder)
}
else {
[System.IO.Path]::GetFullPath((Join-Path -Path (Get-Location).Path -ChildPath $OutputFolder))
}
$normalizedInputFolder = $resolvedInputFolder.TrimEnd('\', '/')
$normalizedOutputFolder = $resolvedOutputFolder.TrimEnd('\', '/')
$isOutputSameAsInputFolder = $normalizedInputFolder.Equals($normalizedOutputFolder, [System.StringComparison]::OrdinalIgnoreCase)
if ((Test-Path -Path $OutputFolder) -and (-not $isOutputSameAsInputFolder)) {
if (-not $Overwrite) {
throw "Output folder already exists: $OutputFolder. Use -Overwrite to replace it."
}
Remove-Item -Path $OutputFolder -Recurse -Force
}
$null = New-Item -Path $OutputFolder -ItemType Directory -Force
function Get-SafeFileName {
param(
[Parameter(Mandatory = $true)]
[string]$Name
)
$invalidChars = [System.IO.Path]::GetInvalidFileNameChars()
$safeName = $Name
foreach ($invalidChar in $invalidChars) {
$safeName = $safeName.Replace($invalidChar, '_')
}
return $safeName
}
function Resolve-UniqueFilePath {
param(
[Parameter(Mandatory = $true)]
[string]$BasePath
)
if (-not (Test-Path -Path $BasePath)) {
return $BasePath
}
$directory = [System.IO.Path]::GetDirectoryName($BasePath)
$nameWithoutExtension = [System.IO.Path]::GetFileNameWithoutExtension($BasePath)
$extension = [System.IO.Path]::GetExtension($BasePath)
$index = 2
do {
$candidate = Join-Path -Path $directory -ChildPath "$nameWithoutExtension-$index$extension"
$index++
} while (Test-Path -Path $candidate)
return $candidate
}
function Get-ObjectNameFromBlock {
param(
[Parameter(Mandatory = $true)]
[string]$BlockText,
[Parameter(Mandatory = $true)]
[ValidateSet('tables', 'views', 'constraints', 'indexes', 'procedures', 'functions', 'triggers')]
[string]$Type
)
switch ($Type) {
'tables' {
$match = [regex]::Match($BlockText, '(?is)\bCREATE\s+TABLE\s+\[([^\]]+)\]\.\[([^\]]+)\]')
if ($match.Success) { return "$($match.Groups[1].Value).$($match.Groups[2].Value)" }
}
'views' {
$match = [regex]::Match($BlockText, '(?is)\b(?:CREATE|ALTER)\s+(?:OR\s+ALTER\s+)?VIEW\s+\[([^\]]+)\]\.\[([^\]]+)\]')
if ($match.Success) { return "$($match.Groups[1].Value).$($match.Groups[2].Value)" }
}
'procedures' {
$match = [regex]::Match(
$BlockText,
'(?is)\b(?:CREATE|ALTER)\s+(?:OR\s+ALTER\s+)?(?:PROCEDURE|PROC)\s+(?:\[(?<schema>[^\]]+)\]|(?<schema>[A-Za-z0-9_]+))\s*\.\s*(?:\[(?<name>[^\]]+)\]|(?<name>[A-Za-z0-9_]+))'
)
if ($match.Success) { return "$($match.Groups['schema'].Value).$($match.Groups['name'].Value)" }
}
'functions' {
$match = [regex]::Match(
$BlockText,
'(?is)\b(?:CREATE|ALTER)\s+(?:OR\s+ALTER\s+)?FUNCTION\s+(?:\[(?<schema>[^\]]+)\]|(?<schema>[A-Za-z0-9_]+))\s*\.\s*(?:\[(?<name>[^\]]+)\]|(?<name>[A-Za-z0-9_]+))'
)
if ($match.Success) { return "$($match.Groups['schema'].Value).$($match.Groups['name'].Value)" }
}
'triggers' {
$match = [regex]::Match($BlockText, '(?is)\b(?:CREATE|ALTER)\s+(?:OR\s+ALTER\s+)?TRIGGER\s+\[([^\]]+)\]\.\[([^\]]+)\]')
if ($match.Success) { return "$($match.Groups[1].Value).$($match.Groups[2].Value)" }
}
'constraints' {
$match = [regex]::Match($BlockText, '(?is)\bALTER\s+TABLE\s+\[([^\]]+)\]\.\[([^\]]+)\]\s+ADD\s+CONSTRAINT\s+\[([^\]]+)\]')
if ($match.Success) { return "$($match.Groups[1].Value).$($match.Groups[2].Value).$($match.Groups[3].Value)" }
}
'indexes' {
$match = [regex]::Match($BlockText, '(?is)\bCREATE\s+(?:UNIQUE\s+)?(?:CLUSTERED\s+|NONCLUSTERED\s+)?INDEX\s+\[([^\]]+)\]\s+ON\s+\[([^\]]+)\]\.\[([^\]]+)\]')
if ($match.Success) { return "$($match.Groups[2].Value).$($match.Groups[3].Value).$($match.Groups[1].Value)" }
}
}
return $null
}
function Format-BlockText {
param(
[Parameter(Mandatory = $true)]
[string]$BlockText
)
$normalizedLines = [System.Collections.Generic.List[string]]::new()
foreach ($line in ($BlockText -split "\r?\n")) {
$normalizedLines.Add($line)
}
while ($normalizedLines.Count -gt 0) {
$first = $normalizedLines[0].Trim()
if (
$first -match '^--\s*=+\s*$' -or
$first -match '^--\s*(TABLES|VIEWS|CONSTRAINTS|INDEXES|STORED PROCEDURES|FUNCTIONS|TRIGGERS)\s*$' -or
$first -match '^GO$'
) {
$normalizedLines.RemoveAt(0)
continue
}
break
}
while ($normalizedLines.Count -gt 0 -and [string]::IsNullOrWhiteSpace($normalizedLines[0])) {
$normalizedLines.RemoveAt(0)
}
while ($normalizedLines.Count -gt 0 -and [string]::IsNullOrWhiteSpace($normalizedLines[$normalizedLines.Count - 1])) {
$normalizedLines.RemoveAt($normalizedLines.Count - 1)
}
return ($normalizedLines -join "`r`n")
}
$lines = [System.IO.File]::ReadAllLines((Resolve-Path -Path $InputFile))
$tables = @{}
$views = @{}
$constraints = @{}
$procedures = @{}
$functions = @{}
$triggers = @{}
$pendingIndexesByTable = @{}
$unassignedIndexes = [System.Collections.Generic.List[string]]::new()
$pendingConstraintsByTable = @{}
$unassignedConstraints = [System.Collections.Generic.List[string]]::new()
$currentSection = $null
$currentBlockLines = [System.Collections.Generic.List[string]]::new()
$sectionHeaderCount = 0
function Complete-CurrentBlock {
param(
[Parameter(Mandatory = $true)]
[string]$Section,
[Parameter()]
[AllowEmptyCollection()]
[string[]]$BlockLines
)
if ($null -eq $BlockLines) {
return
}
$blockText = ($BlockLines -join "`r`n").Trim()
if ([string]::IsNullOrWhiteSpace($blockText)) {
return
}
if ([regex]::IsMatch($blockText, '(?im)^(?:\s*--[^\r\n]*(?:\r?\n|$))+$')) {
if (
$Section -ne 'constraints' -or
-not [regex]::IsMatch($blockText, '(?im)^\s*--\s*SKIPPED\s+CONSTRAINT\b')
) {
return
}
}
$blockText = Format-BlockText -BlockText $blockText
if ([string]::IsNullOrWhiteSpace($blockText)) {
return
}
switch ($Section) {
'tables' {
$tableKey = Get-ObjectNameFromBlock -BlockText $blockText -Type 'tables'
if ([string]::IsNullOrWhiteSpace($tableKey)) {
if (-not [regex]::IsMatch($blockText, '(?is)\bCREATE\s+TABLE\b')) { return }
throw "Could not parse table object name from block."
}
if ($tables.ContainsKey($tableKey)) {
throw "Duplicate table definition found for [$tableKey]."
}
$tables[$tableKey] = [System.Collections.Generic.List[string]]::new()
$tables[$tableKey].Add($blockText)
if ($pendingIndexesByTable.ContainsKey($tableKey)) {
foreach ($pendingIndexBlock in $pendingIndexesByTable[$tableKey]) {
$tables[$tableKey].Add($pendingIndexBlock)
}
$pendingIndexesByTable.Remove($tableKey)
}
if ($pendingConstraintsByTable.ContainsKey($tableKey)) {
foreach ($pendingConstraintBlock in $pendingConstraintsByTable[$tableKey]) {
$tables[$tableKey].Add($pendingConstraintBlock)
}
$pendingConstraintsByTable.Remove($tableKey)
}
}
'views' {
$key = Get-ObjectNameFromBlock -BlockText $blockText -Type 'views'
if ([string]::IsNullOrWhiteSpace($key)) {
if (-not [regex]::IsMatch($blockText, '(?is)\b(?:CREATE|ALTER)\b.*\bVIEW\b')) { return }
throw "Could not parse view object name from block."
}
if ($views.ContainsKey($key)) { throw "Duplicate view definition found for [$key]." }
$views[$key] = $blockText
}
'constraints' {
$skippedMatch = [regex]::Match(
$blockText,
'(?im)^\s*--\s*SKIPPED\s+CONSTRAINT\s+\[(?<schema>[^\]]+)\]\.\[(?<table>[^\]]+)\]\.\[(?<name>[^\]]+)\]'
)
if ($skippedMatch.Success) {
$key = "$($skippedMatch.Groups['schema'].Value).$($skippedMatch.Groups['table'].Value).$($skippedMatch.Groups['name'].Value)"
}
else {
$key = Get-ObjectNameFromBlock -BlockText $blockText -Type 'constraints'
}
if ([string]::IsNullOrWhiteSpace($key)) {
if (-not [regex]::IsMatch($blockText, '(?is)\bALTER\s+TABLE\b.*\bADD\s+CONSTRAINT\b')) { return }
throw "Could not parse constraint name from block."
}
if ($constraints.ContainsKey($key)) { throw "Duplicate constraint definition found for [$key]." }
$constraints[$key] = $blockText
$parts = $key.Split('.')
if ($parts.Length -lt 3) {
$unassignedConstraints.Add($blockText)
return
}
$tableKey = "$($parts[0]).$($parts[1])"
if ($tables.ContainsKey($tableKey)) {
$tables[$tableKey].Add($blockText)
}
else {
if (-not $pendingConstraintsByTable.ContainsKey($tableKey)) {
$pendingConstraintsByTable[$tableKey] = [System.Collections.Generic.List[string]]::new()
}
$pendingConstraintsByTable[$tableKey].Add($blockText)
}
}
'indexes' {
$indexKey = Get-ObjectNameFromBlock -BlockText $blockText -Type 'indexes'
if ([string]::IsNullOrWhiteSpace($indexKey)) {
if (-not [regex]::IsMatch($blockText, '(?is)\bCREATE\b.*\bINDEX\b')) { return }
$unassignedIndexes.Add($blockText)
return
}
$parts = $indexKey.Split('.')
if ($parts.Length -lt 3) {
$unassignedIndexes.Add($blockText)
return
}
$tableKey = "$($parts[0]).$($parts[1])"
if ($tables.ContainsKey($tableKey)) {
$tables[$tableKey].Add($blockText)
}
else {
if (-not $pendingIndexesByTable.ContainsKey($tableKey)) {
$pendingIndexesByTable[$tableKey] = [System.Collections.Generic.List[string]]::new()
}
$pendingIndexesByTable[$tableKey].Add($blockText)
}
}
'procedures' {
$key = Get-ObjectNameFromBlock -BlockText $blockText -Type 'procedures'
if ([string]::IsNullOrWhiteSpace($key)) {
if (-not [regex]::IsMatch($blockText, '(?is)\b(?:CREATE|ALTER)\b.*\b(?:PROCEDURE|PROC)\b')) { return }
throw "Could not parse procedure object name from block."
}
if ($procedures.ContainsKey($key)) { throw "Duplicate procedure definition found for [$key]." }
$procedures[$key] = $blockText
}
'functions' {
$key = Get-ObjectNameFromBlock -BlockText $blockText -Type 'functions'
if ([string]::IsNullOrWhiteSpace($key)) {
if (-not [regex]::IsMatch($blockText, '(?is)\b(?:CREATE|ALTER)\b.*\bFUNCTION\b')) { return }
throw "Could not parse function object name from block."
}
if ($functions.ContainsKey($key)) { throw "Duplicate function definition found for [$key]." }
$functions[$key] = $blockText
}
'triggers' {
$key = Get-ObjectNameFromBlock -BlockText $blockText -Type 'triggers'
if ([string]::IsNullOrWhiteSpace($key)) {
if (-not [regex]::IsMatch($blockText, '(?is)\b(?:CREATE|ALTER)\b.*\bTRIGGER\b')) { return }
throw "Could not parse trigger object name from block."
}
if ($triggers.ContainsKey($key)) { throw "Duplicate trigger definition found for [$key]." }
$triggers[$key] = $blockText
}
default {
return
}
}
}
foreach ($line in $lines) {
$trimmed = $line.Trim()
switch -Regex ($trimmed) {
'^--\s*TABLES$' {
if ($null -ne $currentSection) { Complete-CurrentBlock -Section $currentSection -BlockLines $currentBlockLines.ToArray(); $currentBlockLines.Clear() }
$currentSection = 'tables'
$sectionHeaderCount++
continue
}
'^--\s*VIEWS$' {
if ($null -ne $currentSection) { Complete-CurrentBlock -Section $currentSection -BlockLines $currentBlockLines.ToArray(); $currentBlockLines.Clear() }
$currentSection = 'views'
$sectionHeaderCount++
continue
}
'^--\s*CONSTRAINTS$' {
if ($null -ne $currentSection) { Complete-CurrentBlock -Section $currentSection -BlockLines $currentBlockLines.ToArray(); $currentBlockLines.Clear() }
$currentSection = 'constraints'
$sectionHeaderCount++
continue
}
'^--\s*INDEXES$' {
if ($null -ne $currentSection) { Complete-CurrentBlock -Section $currentSection -BlockLines $currentBlockLines.ToArray(); $currentBlockLines.Clear() }
$currentSection = 'indexes'
$sectionHeaderCount++
continue
}
'^--\s*STORED PROCEDURES$' {
if ($null -ne $currentSection) { Complete-CurrentBlock -Section $currentSection -BlockLines $currentBlockLines.ToArray(); $currentBlockLines.Clear() }
$currentSection = 'procedures'
$sectionHeaderCount++
continue
}
'^--\s*FUNCTIONS$' {
if ($null -ne $currentSection) { Complete-CurrentBlock -Section $currentSection -BlockLines $currentBlockLines.ToArray(); $currentBlockLines.Clear() }
$currentSection = 'functions'
$sectionHeaderCount++
continue
}
'^--\s*TRIGGERS$' {
if ($null -ne $currentSection) { Complete-CurrentBlock -Section $currentSection -BlockLines $currentBlockLines.ToArray(); $currentBlockLines.Clear() }
$currentSection = 'triggers'
$sectionHeaderCount++
continue
}
'^GO$' {
if ($null -ne $currentSection) {
Complete-CurrentBlock -Section $currentSection -BlockLines $currentBlockLines.ToArray()
$currentBlockLines.Clear()
}
continue
}
}
if ($null -ne $currentSection) {
$currentBlockLines.Add($line)
}
}
if ($null -ne $currentSection) {
Complete-CurrentBlock -Section $currentSection -BlockLines $currentBlockLines.ToArray()
$currentBlockLines.Clear()
}
foreach ($pendingTableKey in $pendingIndexesByTable.Keys) {
foreach ($indexBlock in $pendingIndexesByTable[$pendingTableKey]) {
$unassignedIndexes.Add($indexBlock)
}
}
foreach ($pendingTableKey in $pendingConstraintsByTable.Keys) {
foreach ($constraintBlock in $pendingConstraintsByTable[$pendingTableKey]) {
$unassignedConstraints.Add($constraintBlock)
}
}
if ($sectionHeaderCount -eq 0) {
throw "No schema section headers were found in input file [$InputFile]. Expected headers like '-- TABLES' and '-- STORED PROCEDURES'."
}
function Write-ObjectFiles {
param(
[Parameter(Mandatory = $true)]
[string]$TypeFolder,
[Parameter(Mandatory = $true)]
[hashtable]$Items
)
if ($Items.Count -eq 0) {
return 0
}
$targetFolder = Join-Path -Path $OutputFolder -ChildPath $TypeFolder
$null = New-Item -Path $targetFolder -ItemType Directory -Force
$written = 0
foreach ($itemKey in ($Items.Keys | Sort-Object)) {
$fileName = (Get-SafeFileName -Name $itemKey) + '.sql'
$path = Join-Path -Path $targetFolder -ChildPath $fileName
$path = Resolve-UniqueFilePath -BasePath $path
$content = $Items[$itemKey]
if ($content -is [System.Collections.Generic.List[string]]) {
$segments = foreach ($block in $content) {
$trimmedBlock = $block.Trim()
if (-not [string]::IsNullOrWhiteSpace($trimmedBlock)) {
$trimmedBlock + "`r`nGO"
}
}
$finalText = ($segments -join "`r`n`r`n") + "`r`n"
}
else {
$finalText = $content.Trim() + "`r`nGO`r`n"
}
Set-Content -Path $path -Value $finalText -Encoding utf8
$written++
}
return $written
}
$writtenTables = Write-ObjectFiles -TypeFolder 'tables' -Items $tables
$writtenViews = Write-ObjectFiles -TypeFolder 'views' -Items $views
$writtenConstraints = if ($unassignedConstraints.Count -gt 0) {
$constraintFallback = @{}
$constraintCounter = 1
foreach ($constraintBlock in $unassignedConstraints) {
$constraintFallback["unassigned-constraint-$constraintCounter"] = $constraintBlock
$constraintCounter++
}
Write-ObjectFiles -TypeFolder 'constraints' -Items $constraintFallback
}
else {
0
}
$writtenProcedures = Write-ObjectFiles -TypeFolder 'procedures' -Items $procedures
$writtenFunctions = Write-ObjectFiles -TypeFolder 'functions' -Items $functions
$writtenTriggers = Write-ObjectFiles -TypeFolder 'triggers' -Items $triggers
Write-Host "Schema split complete."
Write-Host "Input: $InputFile"
Write-Host "Output: $OutputFolder"
Write-Host "Tables: $writtenTables"
Write-Host "Views: $writtenViews"
Write-Host "Constraints (standalone files): $writtenConstraints"
Write-Host "Procedures: $writtenProcedures"
Write-Host "Functions: $writtenFunctions"
Write-Host "Triggers: $writtenTriggers"
Write-Host "Unassigned indexes: $($unassignedIndexes.Count)"
Write-Host "Unassigned constraints: $($unassignedConstraints.Count)"