Managing database schema changes manually becomes difficult as projects grow. Tables change, columns get added, constraints evolve, and documentation quickly becomes outdated.
To solve this problem, a dynamic metadata capture framework can be created in SQL Server that automatically tracks database structures, compares schema changes, and generates deployment-ready scripts.
The uploaded script builds a complete metadata management system using SQL Server system catalog views like sys.tables, sys.columns, sys.indexes, sys.foreign_key_columns, and extended properties.
This blog explains the architecture, workflow, benefits, and real-world usage of this framework.
Problem Statement
In enterprise applications:
Database structures change frequently
Developers forget to document changes
Deployment scripts become inconsistent
Missing columns create production issues
Schema comparison between environments becomes difficult
MS_Description documentation gets lost
Existing tables require safe ALTER handling
Manual tracking is error-prone and time-consuming.
The solution is automated metadata capture and script generation.
What This Framework Does
This framework automatically:
Captures all user tables
Captures all columns and metadata
Detects schema changes
Tracks new/modified columns
Generates CREATE TABLE scripts
Generates ALTER TABLE ADD scripts
Handles Primary Keys
Handles Foreign Keys metadata
Handles Identity columns
Handles Computed Columns
Handles Unique columns
Handles Default Constraints
Captures MS_Description
Generates extended property scripts
Maintains schema history
The core procedure responsible for this is:
dbo.usp_CaptureDBMetadata
Architecture Overview
The framework uses two master metadata tables:
1. ADTDB_SysTables
Stores table-level metadata.
CREATE TABLE dbo.ADTDB_SysTables
It tracks:
TableID
SchemaName
TableName
ModifyDate
IsActive
HasChanged
2. ADTDB_SysColumns
Stores detailed column-level metadata.
CREATE TABLE dbo.ADTDB_SysColumns
Tracks:
Data Type
Length
Precision
Scale
Nullable
PK/FK
Identity
Computed
Default Constraint
MS_Description
Unique Constraints
This becomes a centralized schema repository.
Step-by-Step Processing Flow
Step 1 – Capture Table Metadata
The procedure reads all user tables from:
sys.tables
sys.schemas
and stores them in:
#SrcTables
This captures:
Table name
Schema
Modify date
Active status
Step 2 – Detect Table Changes
The procedure compares source metadata with stored metadata.
It identifies:
New tables
Modified tables
Existing tables
using UPDATE + INSERT logic instead of MERGE.
This is safer and more controllable in SQL Server.
Why Avoid MERGE?
MERGE can cause:
Deadlocks
Incorrect row matching
Trigger issues
Concurrency bugs
Using separate:
UPDATE
INSERT
operations improves reliability.
Step 3 – Capture Column Metadata
The framework deeply analyzes all columns using:
sys.columnssys.typessys.default_constraintssys.computed_columnssys.foreign_key_columnssys.indexessys.index_columns
This is the most powerful section of the framework.
Information Captured Per Column
For every column, the framework stores:
Property | Description |
|---|---|
Column Name | Actual column |
Data Type | varchar, int, decimal etc |
Length | varchar size |
Precision | decimal precision |
Scale | decimal scale |
Nullable | NULL / NOT NULL |
Default Constraint | Default values |
PK Flag | Primary key indicator |
FK Flag | Foreign key indicator |
Identity Flag | Identity columns |
Computed Flag | Computed columns |
Unique Flag | Unique index participation |
MS_Description | Documentation/comments |
Step 4 – Detect Column-Level Changes
The framework compares old and new metadata.
It identifies changes in:
Data type
Length
Precision
Nullability
Defaults
PK/FK changes
Identity changes
Computed definitions
Descriptions
This creates a full schema diff engine.
Dynamic CREATE TABLE Generation
The framework dynamically generates:
CREATE TABLE
scripts for every table.
Generated scripts include:
Data types
Lengths
Decimal precision
Identity
Default constraints
Nullability
Primary keys
Smart ALTER TABLE Logic
One of the best features is:
ALTER TABLE ADD
generation only for missing columns.
Example logic:
IF NOT EXISTS (
SELECT 1 FROM sys.columns
)
ALTER TABLE ...
This makes deployments:
Safe
Repeatable
Idempotent
Why This Is Important
Without existence checks:
Deployment scripts fail
Re-running scripts becomes impossible
Production deployments become risky
This framework avoids all those issues.
Handling NOT NULL Columns Safely
A very smart implementation exists here.
If a column is:
NOT NULL
No default value
Existing table contains rows
then SQL Server will reject:
ALTER TABLE ADD Column NOT NULL
The framework automatically converts it temporarily to:
NULL
and adds a warning comment.
This prevents deployment failures.
MS_Description Automation
Most systems ignore SQL Server documentation properties.
This framework automatically generates:
sp_addextendedproperty
sp_updateextendedproperty
scripts.
This keeps:
Column documentation
Tooltips
Data dictionary
BI metadata
fully synchronized.
SQL Server 2012 Compatibility
The framework is carefully written for SQL Server 2012 compatibility.
Examples:
Uses
FOR XML PATHinstead ofSTRING_AGGAvoids unsupported syntax
Uses safe PRINT chunking for 4000-char limitation
This is very useful for legacy enterprise systems.
PRINT Handling for Large Scripts
SQL Server PRINT has a limitation:
PRINT supports only 4000 NVARCHAR chars
The framework intelligently splits long scripts into chunks.
IF OBJECT_ID('dbo.ADTDB_SysTables', 'U') IS NULL
BEGIN
CREATE TABLE dbo.ADTDB_SysTables
(
TableID BIGINT PRIMARY KEY,
SchemaName NVARCHAR(128) NOT NULL,
TableName NVARCHAR(128) NOT NULL,
ModifyDate DATETIME NOT NULL,
IsActive BIT NOT NULL DEFAULT 1,
HasChanged BIT NOT NULL DEFAULT 0
);
PRINT 'Table [dbo].[ADTDB_SysTables] created successfully.';
END
IF OBJECT_ID('dbo.ADTDB_SysColumns', 'U') IS NULL
BEGIN
CREATE TABLE dbo.ADTDB_SysColumns
(
ColumnID BIGINT PRIMARY KEY,
TableID BIGINT NOT NULL,
ColumnName NVARCHAR(128) NOT NULL,
OrdinalPosition INT NOT NULL,
DataType NVARCHAR(128) NOT NULL,
MaxLength INT NULL,
NumericPrecision TINYINT NULL,
NumericScale INT NULL,
IsNullable BIT NOT NULL,
DefaultValue NVARCHAR(500) NULL,
DefaultConstraintName NVARCHAR(128) NULL,
IsPrimaryKey BIT NOT NULL DEFAULT 0,
IsForeignKey BIT NOT NULL DEFAULT 0,
FKReferencedSchema NVARCHAR(128) NULL,
FKReferencedTable NVARCHAR(128) NULL,
FKReferencedColumn NVARCHAR(128) NULL,
IsIdentity BIT NOT NULL DEFAULT 0,
IsComputed BIT NOT NULL DEFAULT 0,
ComputedDefinition NVARCHAR(MAX) NULL,
IsUnique BIT NOT NULL DEFAULT 0,
MS_Description NVARCHAR(MAX) NULL,
IsActive BIT NOT NULL DEFAULT 1,
ModifyDate DATETIME NOT NULL DEFAULT GETDATE(),
HasChanged BIT NOT NULL DEFAULT 0
);
END
GO
IF OBJECT_ID('dbo.usp_CaptureDBMetadata', 'P') IS NOT NULL
DROP PROCEDURE dbo.usp_CaptureDBMetadata;
GO
CREATE PROCEDURE dbo.usp_CaptureDBMetadata
@TableName NVARCHAR(128) = '',
@TableSearch VARCHAR(50)='ATS_%'
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @TableName = '' SET @TableName = NULL;
IF @TableSearch = '' SET @TableSearch = NULL;
DECLARE
@StartTime DATETIME = GETDATE(),
@StepTime DATETIME,
@ErrorMsg NVARCHAR(2000),
@TblInserted INT = 0,
@TblUpdated INT = 0,
@TblDeactivated INT = 0,
@ColInserted INT = 0,
@ColUpdated INT = 0,
@ColDeactivated INT = 0,
-- Mode 2 variables
@TableID BIGINT,
@SchemaName NVARCHAR(128),
@CreateScript NVARCHAR(MAX),
@ColumnDef NVARCHAR(MAX),
@PKColumns NVARCHAR(MAX),
@HasBaseline BIT = 0,
@ElseScript NVARCHAR(MAX) = N''; -- ← NEW: ELSE block builder
IF OBJECT_ID('tempdb..#SrcTables') IS NOT NULL DROP TABLE #SrcTables;
IF OBJECT_ID('tempdb..#SrcColumns') IS NOT NULL DROP TABLE #SrcColumns;
IF OBJECT_ID('tempdb..#ChangedTables') IS NOT NULL DROP TABLE #ChangedTables;
IF OBJECT_ID('tempdb..#ChangedColumns') IS NOT NULL DROP TABLE #ChangedColumns;
CREATE TABLE #ChangedTables (TableID BIGINT NOT NULL, ChangeType NVARCHAR(10) NOT NULL);
CREATE TABLE #ChangedColumns (ColumnID BIGINT NOT NULL, TableID BIGINT NOT NULL, ChangeType NVARCHAR(10) NOT NULL);
SELECT @HasBaseline = CASE
WHEN EXISTS (SELECT 1 FROM dbo.ADTDB_SysTables t
WHERE @TableName IS NULL OR t.TableName = @TableName)
THEN 1 ELSE 0 END;
BEGIN TRY
-- ====================================================================
-- STEP 1 : Collect Table-Level Metadata
-- ====================================================================
SELECT
t.object_id AS TableID,
s.name AS SchemaName,
t.name AS TableName,
1 AS IsActive,
t.modify_date AS ModifyDate
INTO #SrcTables
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
AND t.type = 'U'
AND (@TableName IS NULL OR t.name = @TableName)
AND (@TableSearch IS NULL OR t.name LIKE @TableSearch)
-- ====================================================================
-- STEP 2 : MERGE ADTDB_SysTables
-- ====================================================================
SET @StepTime = GETDATE();
UPDATE dbo.ADTDB_SysTables
SET HasChanged = 0
WHERE @TableName IS NULL OR TableName = @TableName;
UPDATE c
SET c.HasChanged = 0
FROM dbo.ADTDB_SysColumns c
INNER JOIN dbo.ADTDB_SysTables t ON c.TableID = t.TableID
WHERE @TableName IS NULL OR t.TableName = @TableName;
UPDATE tgt
SET
tgt.SchemaName = src.SchemaName,
tgt.TableName = src.TableName,
tgt.ModifyDate = src.ModifyDate,
tgt.IsActive = src.IsActive,
tgt.HasChanged = 1
OUTPUT inserted.TableID, 'ALTER' INTO #ChangedTables (TableID, ChangeType)
FROM dbo.ADTDB_SysTables tgt
JOIN #SrcTables src ON tgt.TableID = src.TableID AND tgt.TableName = src.TableName
WHERE tgt.ModifyDate <> src.ModifyDate;
INSERT INTO dbo.ADTDB_SysTables
(TableID, SchemaName, TableName, ModifyDate, IsActive, HasChanged)
OUTPUT inserted.TableID, 'ADD' INTO #ChangedTables (TableID, ChangeType)
SELECT src.TableID, src.SchemaName, src.TableName, src.ModifyDate, src.IsActive, 1
FROM #SrcTables src
LEFT JOIN dbo.ADTDB_SysTables tgt
ON tgt.TableID = src.TableID AND tgt.TableName = src.TableName
WHERE tgt.TableID IS NULL;
-- ====================================================================
-- STEP 3 : Collect Column-Level Metadata
-- ====================================================================
IF OBJECT_ID('tempdb..#SrcColumns') IS NOT NULL DROP TABLE #SrcColumns;
SELECT
(CAST(t.object_id AS BIGINT) * 100000 + c.column_id) AS ColumnID,
t.object_id AS TableID,
c.name AS ColumnName,
c.column_id AS OrdinalPosition,
ty.name AS DataType,
CASE WHEN c.max_length = -1 THEN -1 ELSE c.max_length END AS MaxLength,
c.precision AS NumericPrecision,
c.scale AS NumericScale,
c.is_nullable AS IsNullable,
dc.definition AS DefaultValue,
dc.name AS DefaultConstraintName,
(SELECT TOP 1 CAST(ep.value AS NVARCHAR(MAX))
FROM sys.extended_properties ep
WHERE ep.major_id = t.object_id
AND ep.minor_id = c.column_id
AND ep.name = 'MS_Description') AS MS_Description,
CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey,
CASE WHEN EXISTS (
SELECT 1 FROM sys.foreign_key_columns fk2
WHERE fk2.parent_object_id = c.object_id AND fk2.parent_column_id = c.column_id
) THEN 1 ELSE 0 END AS IsForeignKey,
(SELECT TOP 1 s2.name FROM sys.foreign_key_columns fk
JOIN sys.tables tref ON fk.referenced_object_id = tref.object_id
JOIN sys.schemas s2 ON tref.schema_id = s2.schema_id
WHERE fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id) AS FKReferencedSchema,
(SELECT TOP 1 tref.name FROM sys.foreign_key_columns fk
JOIN sys.tables tref ON fk.referenced_object_id = tref.object_id
WHERE fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id) AS FKReferencedTable,
(SELECT TOP 1 cref.name FROM sys.foreign_key_columns fk
JOIN sys.columns cref ON fk.referenced_object_id = cref.object_id
AND fk.referenced_column_id = cref.column_id
WHERE fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id) AS FKReferencedColumn,
c.is_identity AS IsIdentity,
c.is_computed AS IsComputed,
cc.definition AS ComputedDefinition,
CASE WHEN uq.column_id IS NOT NULL THEN 1 ELSE 0 END AS IsUnique,
GETDATE() AS CapturedOn,
1 AS IsActive
INTO #SrcColumns
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN (
SELECT ic.object_id, ic.column_id
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
) pk ON c.object_id = pk.object_id AND c.column_id = pk.column_id
LEFT JOIN sys.computed_columns cc
ON c.object_id = cc.object_id AND c.column_id = cc.column_id
LEFT JOIN (
SELECT ic.object_id, ic.column_id
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_unique = 1 AND i.is_primary_key = 0
) uq ON c.object_id = uq.object_id AND c.column_id = uq.column_id
WHERE t.is_ms_shipped = 0
AND (@TableName IS NULL OR t.name = @TableName);
-- ====================================================================
-- STEP 4 : MERGE -> ADTDB_SysColumns
-- ====================================================================
UPDATE tgt
SET
tgt.OrdinalPosition = src.OrdinalPosition,
tgt.DataType = src.DataType,
tgt.MaxLength = src.MaxLength,
tgt.NumericPrecision = src.NumericPrecision,
tgt.NumericScale = src.NumericScale,
tgt.IsNullable = src.IsNullable,
tgt.DefaultValue = src.DefaultValue,
tgt.DefaultConstraintName = src.DefaultConstraintName,
tgt.MS_Description = src.MS_Description,
tgt.IsPrimaryKey = src.IsPrimaryKey,
tgt.IsForeignKey = src.IsForeignKey,
tgt.FKReferencedSchema = src.FKReferencedSchema,
tgt.FKReferencedTable = src.FKReferencedTable,
tgt.FKReferencedColumn = src.FKReferencedColumn,
tgt.IsIdentity = src.IsIdentity,
tgt.IsComputed = src.IsComputed,
tgt.ComputedDefinition = src.ComputedDefinition,
tgt.IsUnique = src.IsUnique,
tgt.IsActive = 1,
tgt.ModifyDate = GETDATE(),
tgt.HasChanged = 1
OUTPUT inserted.ColumnID, inserted.TableID, 'ALTER'
INTO #ChangedColumns (ColumnID, TableID, ChangeType)
FROM dbo.ADTDB_SysColumns tgt
INNER JOIN #SrcColumns src
ON tgt.ColumnName = src.ColumnName
AND src.TableID = tgt.TableID
AND src.ColumnID = tgt.ColumnID
WHERE
tgt.DataType <> src.DataType
OR ISNULL(tgt.MaxLength,0) <> ISNULL(src.MaxLength,0)
OR ISNULL(tgt.NumericPrecision,0) <> ISNULL(src.NumericPrecision,0)
OR ISNULL(tgt.NumericScale,0) <> ISNULL(src.NumericScale,0)
OR tgt.IsNullable <> src.IsNullable
OR ISNULL(tgt.DefaultValue,'') <> ISNULL(src.DefaultValue,'')
OR ISNULL(tgt.DefaultConstraintName,'') <> ISNULL(src.DefaultConstraintName,'')
OR ISNULL(tgt.MS_Description,'') <> ISNULL(src.MS_Description,'')
OR tgt.IsPrimaryKey <> src.IsPrimaryKey
OR tgt.IsForeignKey <> src.IsForeignKey
OR tgt.IsIdentity <> src.IsIdentity
OR tgt.IsComputed <> src.IsComputed
OR ISNULL(tgt.ComputedDefinition,'') <> ISNULL(src.ComputedDefinition,'')
OR tgt.IsUnique <> src.IsUnique;
INSERT INTO dbo.ADTDB_SysColumns (
ColumnID, TableID, ColumnName, OrdinalPosition,
DataType, MaxLength, NumericPrecision, NumericScale,
IsNullable, DefaultValue, DefaultConstraintName, MS_Description,
IsPrimaryKey, IsForeignKey,
FKReferencedSchema, FKReferencedTable, FKReferencedColumn,
IsIdentity, IsComputed, ComputedDefinition, IsUnique,
IsActive, ModifyDate, HasChanged
)
OUTPUT inserted.ColumnID, inserted.TableID, 'ADD'
INTO #ChangedColumns (ColumnID, TableID, ChangeType)
SELECT
src.ColumnID, src.TableID, src.ColumnName, src.OrdinalPosition,
src.DataType, src.MaxLength, src.NumericPrecision, src.NumericScale,
src.IsNullable, src.DefaultValue, src.DefaultConstraintName, src.MS_Description,
src.IsPrimaryKey, src.IsForeignKey,
src.FKReferencedSchema, src.FKReferencedTable, src.FKReferencedColumn,
src.IsIdentity, src.IsComputed, src.ComputedDefinition, src.IsUnique,
1, GETDATE(), 1
FROM #SrcColumns src
LEFT JOIN dbo.ADTDB_SysColumns tgt
ON tgt.ColumnName = src.ColumnName
AND src.TableID = tgt.TableID
AND src.ColumnID = tgt.ColumnID
WHERE tgt.ColumnID IS NULL;
-- ====================================================================
-- MODE-BASED OUTPUT : CREATE TABLE + ELSE ALTER TABLE ADD scripts
-- ====================================================================
DECLARE tbl_cursor CURSOR LOCAL FOR
SELECT TableID, SchemaName, TableName
FROM dbo.ADTDB_SysTables
WHERE IsActive = 1
AND (@TableName IS NULL OR TableName = @TableName)
AND (@TableSearch IS NULL OR TableName LIKE @TableSearch) -- ← YE ADD KARO
ORDER BY SchemaName, TableName;
OPEN tbl_cursor;
FETCH NEXT FROM tbl_cursor INTO @TableID, @SchemaName, @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Reset for every table
SET @CreateScript = '';
SET @ColumnDef = '';
SET @PKColumns = '';
SET @ElseScript = N'';
-- ----------------------------------------------------------------
-- Build column definitions (shared logic for CREATE TABLE)
-- ----------------------------------------------------------------
SELECT @ColumnDef = CAST(@ColumnDef AS NVARCHAR(MAX)) +
CAST(
' [' + ColumnName + '] ' + DataType +
CASE
WHEN DataType IN ('nvarchar','nchar') AND MaxLength IS NOT NULL AND MaxLength = -1
THEN '(MAX)'
WHEN DataType IN ('nvarchar','nchar') AND MaxLength IS NOT NULL AND MaxLength <> -1
THEN '(' + CAST(MaxLength / 2 AS VARCHAR) + ')'
WHEN DataType IN ('varchar','char') AND MaxLength IS NOT NULL AND MaxLength = -1
THEN '(MAX)'
WHEN DataType IN ('varchar','char') AND MaxLength IS NOT NULL AND MaxLength <> -1
THEN '(' + CAST(MaxLength AS VARCHAR) + ')'
ELSE ''
END +
CASE
WHEN DataType IN ('decimal','numeric') AND NumericPrecision IS NOT NULL
THEN '(' + CAST(NumericPrecision AS VARCHAR) + ',' + CAST(ISNULL(NumericScale,0) AS VARCHAR) + ')'
ELSE ''
END +
CASE WHEN IsIdentity = 1 THEN ' IDENTITY(1,1)' ELSE '' END +
CASE WHEN IsNullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
CASE
WHEN DefaultValue IS NOT NULL
THEN ' CONSTRAINT [DF_' + @TableName + '_' + ColumnName + '] DEFAULT ' + DefaultValue
ELSE ''
END + ',' + CHAR(10)
AS NVARCHAR(MAX))
FROM dbo.ADTDB_SysColumns
WHERE TableID = @TableID AND IsActive = 1
ORDER BY OrdinalPosition;
-- Primary Key columns (FOR XML PATH, SQL Server 2012 compatible)
SELECT @PKColumns = STUFF(
(SELECT ', [' + ColumnName + ']'
FROM dbo.ADTDB_SysColumns
WHERE TableID = @TableID AND IsPrimaryKey = 1 AND IsActive = 1
ORDER BY OrdinalPosition
FOR XML PATH('')), 1, 2, ''
);
SET @CreateScript =
'IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''' + @TableName + ''')' + CHAR(10) +
'BEGIN' + CHAR(10) +
' CREATE TABLE [' + @SchemaName + '].[' + @TableName + '] (' + CHAR(10);
SET @CreateScript = @CreateScript + @ColumnDef;
IF @PKColumns IS NOT NULL AND @PKColumns <> ''
SET @CreateScript =
SUBSTRING(@CreateScript, 1, LEN(@CreateScript) - 2) + ',' + CHAR(10) +
' CONSTRAINT [PK_' + @TableName + '] PRIMARY KEY (' + @PKColumns + ')' + CHAR(10);
ELSE
SET @CreateScript =
SUBSTRING(@CreateScript, 1, LEN(@CreateScript) - 2) + CHAR(10);
-- Close CREATE TABLE block (END only, GO comes after the ELSE block)
SET @CreateScript = @CreateScript + ' );' + CHAR(10) +
'END' + CHAR(10);
-- ================================================================
-- ELSE block: per-column ALTER TABLE ADD with existence check
--
-- Output sample for one column:
--
-- ELSE
-- BEGIN
-- -- Table already exists; add any missing columns
--
-- IF NOT EXISTS (
-- SELECT 1 FROM sys.columns
-- WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeDetails]')
-- AND name = N'EmpID'
-- )
-- ALTER TABLE [dbo].[EmployeeDetails] ADD
-- [EmpID] INT NOT NULL
-- CONSTRAINT [DF_EmployeeDetails_EmpID] DEFAULT 0;
--
-- IF NOT EXISTS ( ... )
-- ALTER TABLE ... ADD [NextCol] NVARCHAR(100) NULL;
-- END
-- GO
-- ================================================================
SELECT @ElseScript = CAST(@ElseScript AS NVARCHAR(MAX)) +
CAST(
-- Existence check
' IF NOT EXISTS (' + CHAR(10) +
' SELECT 1 FROM sys.columns' + CHAR(10) +
' WHERE object_id = OBJECT_ID(N''[' + @SchemaName + '].[' + @TableName + ']'')' + CHAR(10) +
' AND name = N''' + ColumnName + '''' + CHAR(10) +
' )' + CHAR(10) +
-- ALTER TABLE ADD with full type definition
' ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD' + CHAR(10) +
' [' + ColumnName + '] ' + DataType +
-- Length / precision (same rules as CREATE TABLE above)
CASE
WHEN DataType IN ('nvarchar','nchar') AND MaxLength IS NOT NULL AND MaxLength = -1
THEN '(MAX)'
WHEN DataType IN ('nvarchar','nchar') AND MaxLength IS NOT NULL AND MaxLength <> -1
THEN '(' + CAST(MaxLength / 2 AS VARCHAR) + ')'
WHEN DataType IN ('varchar','char') AND MaxLength IS NOT NULL AND MaxLength = -1
THEN '(MAX)'
WHEN DataType IN ('varchar','char') AND MaxLength IS NOT NULL AND MaxLength <> -1
THEN '(' + CAST(MaxLength AS VARCHAR) + ')'
ELSE ''
END +
CASE
WHEN DataType IN ('decimal','numeric') AND NumericPrecision IS NOT NULL
THEN '(' + CAST(NumericPrecision AS VARCHAR) + ',' + CAST(ISNULL(NumericScale,0) AS VARCHAR) + ')'
ELSE ''
END +
-- NULL / NOT NULL safety rule:
-- NOT NULL columns without a DEFAULT are dangerous on existing tables
-- (SQL Server will reject them if the table has rows).
-- We emit NULL with a warning comment so the DBA can decide.
CASE
WHEN IsNullable = 0 AND DefaultValue IS NULL AND IsIdentity = 0
THEN ' NULL -- WARNING: originally NOT NULL; set NULL here because table may have existing rows.' +
' Backfill data and set NOT NULL separately if required.'
WHEN IsNullable = 1 THEN ' NULL'
ELSE ' NOT NULL'
END +
-- DEFAULT constraint (same naming convention as CREATE TABLE)
CASE
WHEN DefaultValue IS NOT NULL
THEN CHAR(10) +
' CONSTRAINT [DF_' + @TableName + '_' + ColumnName + '] DEFAULT ' + DefaultValue
ELSE ''
END + ';' + CHAR(10) + CHAR(10)
AS NVARCHAR(MAX))
FROM dbo.ADTDB_SysColumns
WHERE TableID = @TableID AND IsActive = 1
ORDER BY OrdinalPosition;
-- Stitch everything: END (from CREATE) + ELSE block + GO
SET @CreateScript = @CreateScript +
'ELSE' + CHAR(10) +
'BEGIN' + CHAR(10) +
' -- Table already exists; add any missing columns' + CHAR(10) +
@ElseScript +
'END' + CHAR(10) +
'GO' + CHAR(10) + CHAR(10);
-- ----------------------------------------------------------------
-- PRINT loop (4000-char safe, SQL Server 2012 compatible)
-- ----------------------------------------------------------------
DECLARE
@pScript NVARCHAR(MAX) = @CreateScript + CHAR(10),
@pPos INT = 1,
@pNext INT,
@pLine NVARCHAR(MAX);
WHILE @pPos <= LEN(@pScript)
BEGIN
SET @pNext = CHARINDEX(CHAR(10), @pScript, @pPos);
IF ISNULL(@pNext, 0) = 0
BEGIN
PRINT SUBSTRING(@pScript, @pPos, LEN(@pScript) - @pPos + 1);
BREAK;
END;
SET @pLine = REPLACE(SUBSTRING(@pScript, @pPos, @pNext - @pPos), CHAR(13), '');
IF LEN(@pLine) <= 4000
PRINT @pLine;
ELSE
BEGIN
DECLARE @pInner INT = 1;
WHILE @pInner <= LEN(@pLine)
BEGIN
PRINT SUBSTRING(@pLine, @pInner, 4000);
SET @pInner = @pInner + 4000;
END;
END;
SET @pPos = @pNext + 1;
END;
FETCH NEXT FROM tbl_cursor INTO @TableID, @SchemaName, @TableName;
END;
CLOSE tbl_cursor;
DEALLOCATE tbl_cursor;
-- ====================================================================
-- MS_Description Scripts
-- ====================================================================
DECLARE
@DescScript NVARCHAR(MAX),
@DescSchema NVARCHAR(128),
@DescTable NVARCHAR(128),
@DescColumn NVARCHAR(128),
@DescValue NVARCHAR(MAX);
DECLARE desc_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
t.SchemaName,
t.TableName,
c.ColumnName,
REPLACE(ISNULL(c.MS_Description, ''), '''', '''''')
FROM dbo.ADTDB_SysColumns c
INNER JOIN dbo.ADTDB_SysTables t ON c.TableID = t.TableID
WHERE NULLIF(LTRIM(RTRIM(c.MS_Description)), '') IS NOT NULL
AND (@TableSearch IS NULL OR TableName LIKE @TableSearch)
AND (@TableName IS NULL OR T.TableName=@TableName)
ORDER BY t.SchemaName, t.TableName, c.OrdinalPosition;
OPEN desc_cursor;
FETCH NEXT FROM desc_cursor INTO @DescSchema, @DescTable, @DescColumn, @DescValue;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DescScript =
'IF EXISTS (' + CHAR(10) +
' SELECT 1 FROM sys.extended_properties' + CHAR(10) +
' WHERE name = N''MS_Description''' + CHAR(10) +
' AND major_id = OBJECT_ID(N''[' + @DescSchema + '].[' + @DescTable + ']'')' + CHAR(10) +
' AND minor_id = (' + CHAR(10) +
' SELECT column_id FROM sys.columns' + CHAR(10) +
' WHERE object_id = OBJECT_ID(N''[' + @DescSchema + '].[' + @DescTable + ']'')' + CHAR(10) +
' AND name = N''' + @DescColumn + '''' + CHAR(10) +
' )' + CHAR(10) +
')' + CHAR(10) +
' EXEC sys.sp_updateextendedproperty' + CHAR(10) +
' @name = N''MS_Description'',' + CHAR(10) +
' @value = N''' + @DescValue + ''',' + CHAR(10) +
' @level0type = N''SCHEMA'', @level0name = N''' + @DescSchema + ''',' + CHAR(10) +
' @level1type = N''TABLE'', @level1name = N''' + @DescTable + ''',' + CHAR(10) +
' @level2type = N''COLUMN'', @level2name = N''' + @DescColumn + ''';' + CHAR(10) +
'ELSE' + CHAR(10) +
' EXEC sys.sp_addextendedproperty' + CHAR(10) +
' @name = N''MS_Description'',' + CHAR(10) +
' @value = N''' + @DescValue + ''',' + CHAR(10) +
' @level0type = N''SCHEMA'', @level0name = N''' + @DescSchema + ''',' + CHAR(10) +
' @level1type = N''TABLE'', @level1name = N''' + @DescTable + ''',' + CHAR(10) +
' @level2type = N''COLUMN'', @level2name = N''' + @DescColumn + ''';' + CHAR(10) +
'GO' + CHAR(10) + CHAR(10);
DECLARE
@pScript2 NVARCHAR(MAX) = @DescScript + CHAR(10),
@pPos2 INT = 1,
@pNext2 INT,
@pLine2 NVARCHAR(MAX);
WHILE @pPos2 <= LEN(@pScript2)
BEGIN
SET @pNext2 = CHARINDEX(CHAR(10), @pScript2, @pPos2);
IF ISNULL(@pNext2, 0) = 0
BEGIN
PRINT SUBSTRING(@pScript2, @pPos2, LEN(@pScript2) - @pPos2 + 1);
BREAK;
END;
SET @pLine2 = REPLACE(SUBSTRING(@pScript2, @pPos2, @pNext2 - @pPos2), CHAR(13), '');
IF LEN(@pLine2) <= 4000
PRINT @pLine2;
ELSE
BEGIN
DECLARE @pInner2 INT = 1;
WHILE @pInner2 <= LEN(@pLine2)
BEGIN
PRINT SUBSTRING(@pLine2, @pInner2, 4000);
SET @pInner2 = @pInner2 + 4000;
END;
END;
SET @pPos2 = @pNext2 + 1;
END;
FETCH NEXT FROM desc_cursor INTO @DescSchema, @DescTable, @DescColumn, @DescValue;
END;
CLOSE desc_cursor;
DEALLOCATE desc_cursor;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
SET @ErrorMsg =
'ERROR in usp_CaptureDBMetadata' + CHAR(10)
+ 'Number : ' + CAST(ERROR_NUMBER() AS NVARCHAR) + CHAR(10)
+ 'Severity: ' + CAST(ERROR_SEVERITY() AS NVARCHAR) + CHAR(10)
+ 'State : ' + CAST(ERROR_STATE() AS NVARCHAR) + CHAR(10)
+ 'Line : ' + CAST(ERROR_LINE() AS NVARCHAR) + CHAR(10)
+ 'Message : ' + ERROR_MESSAGE();
RAISERROR(@ErrorMsg, 16, 1);
END CATCH;
END;
GO
Without this:
Scripts get truncated
Deployments become incomplete
Real-World Use Cases
This framework is extremely useful for:
1. Schema Versioning
Track database structure changes automatically.
2. Dev vs Production Comparison
Compare environments quickly.
3. Automated Deployment
Generate safe deployment scripts.
4. Database Documentation
Maintain live schema documentation.
5. Audit & Compliance
Track structural changes historically.
6. Data Warehouse Governance
Useful in ETL and enterprise BI systems.
Performance Considerations
Since system views are queried heavily:
Recommended indexes:
CREATE INDEX IX_ADTDB_SysColumns_TableID
ON dbo.ADTDB_SysColumns(TableID)
Also consider indexing:
HasChanged
TableName
ModifyDate
for faster reporting.
Best Practices
Use Dedicated Metadata Schema
Instead of dbo:
Metadata.ADTDB_SysTables
Metadata.ADTDB_SysColumns
Add Change History Table
Store every schema change permanently.
Add DDL Triggers
Automatically execute metadata capture after:
CREATE TABLE
ALTER TABLE
Export to JSON
Generate schema snapshots in JSON format.
Integrate with CI/CD
Useful in:
Azure DevOps
Jenkins
GitHub Actions
Possible Enhancements
Future improvements can include:
Index scripting
Foreign key scripting
Trigger scripting
Stored procedure dependency tracking
Partition information
Compression settings
Temporal table support
Columnstore metadata
JSON schema export
Advantages of This Framework
Feature | Benefit |
|---|---|
Automated metadata capture | Eliminates manual work |
Dynamic script generation | Faster deployments |
Schema comparison | Easy auditing |
MS_Description handling | Better documentation |
Safe ALTER scripts | Production-safe |
SQL 2012 compatibility | Legacy support |
Change tracking | Governance ready |
Challenges Solved
This framework solves common enterprise problems:
Missing deployment scripts
Schema drift
Documentation mismatch
Production deployment failures
Inconsistent environments
Manual table scripting
Final Thoughts
A database is not just data — it is structure, relationships, business rules, and documentation.
As systems scale, manually maintaining schema consistency becomes impossible.
This metadata automation framework transforms SQL Server into a self-documenting and deployment-aware system.
For DBAs, SQL Developers, Data Engineers, and Enterprise Teams, this approach provides:
Better governance
Faster deployments
Safer releases
Automated documentation
Long-term maintainability
The combination of metadata tracking + dynamic script generation + change detection creates a powerful foundation for enterprise-grade database lifecycle management.
Script Reference: