SQL Server

ChartDB provides comprehensive support for Microsoft SQL Server (T-SQL), including version-specific optimizations for SQL Server 2016 and later versions.

Database Editions

ChartDB supports multiple SQL Server versions:

SQL Server 2017+: Modern query using STRING_AGG for better performance
SQL Server 2016 and below: Compatible query using STUFF and FOR XML PATH

Smart Query Import

The SQL Server Smart Query extracts complete schema metadata using JSON functions and system catalogs.

SQL Server 2017+ Query

/* SQL Server 2017 and above edition (14.0, 15.0, 16.0, 17.0)*/
WITH fk_info AS (
    SELECT
        JSON_QUERY(
            N'[' + STRING_AGG(
                CONVERT(nvarchar(max),
                    JSON_QUERY(N'{
                        "schema": "' + STRING_ESCAPE(COALESCE(REPLACE(tp_schema.name, '"', ''), ''), 'json') +
                        '", "table": "' + STRING_ESCAPE(COALESCE(REPLACE(tp.name, '"', ''), ''), 'json') +
                        '", "column": "' + STRING_ESCAPE(COALESCE(REPLACE(cp.name, '"', ''), ''), 'json') +
                        '", "foreign_key_name": "' + STRING_ESCAPE(COALESCE(REPLACE(fk.name, '"', ''), ''), 'json') +
                        '", "reference_schema": "' + STRING_ESCAPE(COALESCE(REPLACE(tr_schema.name, '"', ''), ''), 'json') +
                        '", "reference_table": "' + STRING_ESCAPE(COALESCE(REPLACE(tr.name, '"', ''), ''), 'json') +
                        '", "reference_column": "' + STRING_ESCAPE(COALESCE(REPLACE(cr.name, '"', ''), ''), 'json') +
                        '", "fk_def": "FOREIGN KEY (' + STRING_ESCAPE(COALESCE(REPLACE(cp.name, '"', ''), ''), 'json') +
                        ') REFERENCES ' + STRING_ESCAPE(COALESCE(REPLACE(tr.name, '"', ''), ''), 'json') +
                        '(' + STRING_ESCAPE(COALESCE(REPLACE(cr.name, '"', ''), ''), 'json') +
                        ') ON DELETE ' + STRING_ESCAPE(fk.delete_referential_action_desc, 'json') +
                        ' ON UPDATE ' + STRING_ESCAPE(fk.update_referential_action_desc, 'json') +
                    '"}') COLLATE DATABASE_DEFAULT
                ), N','
            ) + N']'
        ) AS all_fks_json
    FROM sys.foreign_keys AS fk
    JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
    JOIN sys.tables AS tp ON fkc.parent_object_id = tp.object_id
    JOIN sys.schemas AS tp_schema ON tp.schema_id = tp_schema.schema_id
    JOIN sys.columns AS cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
    JOIN sys.tables AS tr ON fkc.referenced_object_id = tr.object_id
    JOIN sys.schemas AS tr_schema ON tr.schema_id = tr_schema.schema_id
    JOIN sys.columns AS cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
),
cols AS (
    SELECT
        JSON_QUERY(N'[' +
            STRING_AGG(
                CONVERT(nvarchar(max),
                    JSON_QUERY(N'{
                        "schema": "' + STRING_ESCAPE(COALESCE(REPLACE(cols.TABLE_SCHEMA, '"', ''), ''), 'json') +
                        '", "table": "' + STRING_ESCAPE(COALESCE(REPLACE(cols.TABLE_NAME, '"', ''), ''), 'json') +
                        '", "name": "' + STRING_ESCAPE(COALESCE(REPLACE(cols.COLUMN_NAME, '"', ''), ''), 'json') +
                        '", "ordinal_position": ' + CAST(cols.ORDINAL_POSITION AS NVARCHAR(MAX)) +
                        ', "type": "' + STRING_ESCAPE(LOWER(cols.DATA_TYPE), 'json') +
                        '", "character_maximum_length": "' +
                            CASE
                                WHEN cols.CHARACTER_MAXIMUM_LENGTH IS NULL THEN 'null'
                                ELSE CAST(cols.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(MAX))
                            END +
                        '", "precision": ' +
                            CASE
                                WHEN cols.DATA_TYPE IN ('numeric', 'decimal')
                                THEN '{"precision":' + COALESCE(CAST(cols.NUMERIC_PRECISION AS NVARCHAR(MAX)), 'null') +
                                     ',"scale":' + COALESCE(CAST(cols.NUMERIC_SCALE AS NVARCHAR(MAX)), 'null') + '}'
                                ELSE 'null'
                            END +
                        ', "nullable": ' + CASE WHEN cols.IS_NULLABLE = 'YES' THEN 'true' ELSE 'false' END +
                        ', "default": ' + '"' + STRING_ESCAPE(COALESCE(REPLACE(CAST(cols.COLUMN_DEFAULT AS NVARCHAR(MAX)), '"', '\\"'), ''), 'json') + '"' +
                        ', "collation": ' + CASE
                            WHEN cols.COLLATION_NAME IS NULL THEN 'null'
                            ELSE '"' + STRING_ESCAPE(cols.COLLATION_NAME, 'json') + '"'
                        END +
                        ', "is_identity": ' + CASE 
                            WHEN COLUMNPROPERTY(OBJECT_ID(cols.TABLE_SCHEMA + '.' + cols.TABLE_NAME), cols.COLUMN_NAME, 'IsIdentity') = 1 
                            THEN 'true' 
                            ELSE 'false' 
                        END +
                    N'}') COLLATE DATABASE_DEFAULT
                ), N','
            ) +
        N']') AS all_columns_json
    FROM INFORMATION_SCHEMA.COLUMNS cols
    WHERE cols.TABLE_CATALOG = DB_NAME()
),
indexes AS (
    SELECT
        N'[' +
            STRING_AGG(
                CONVERT(nvarchar(max),
                    JSON_QUERY(N'{
                        "schema": "' + STRING_ESCAPE(COALESCE(REPLACE(s.name, '"', ''), ''), 'json') +
                        '", "table": "' + STRING_ESCAPE(COALESCE(REPLACE(t.name, '"', ''), ''), 'json') +
                        '", "name": "' + STRING_ESCAPE(COALESCE(REPLACE(i.name, '"', ''), ''), 'json') +
                        '", "column": "' + STRING_ESCAPE(COALESCE(REPLACE(c.name, '"', ''), ''), 'json') +
                        '", "index_type": "' + STRING_ESCAPE(LOWER(i.type_desc), 'json') +
                        '", "unique": ' + CASE WHEN i.is_unique = 1 THEN 'true' ELSE 'false' END +
                        ', "direction": "' + CASE WHEN ic.is_descending_key = 1 THEN 'desc' ELSE 'asc' END +
                        '", "column_position": ' + CAST(ic.key_ordinal AS nvarchar(max)) + N'}'
                    ) COLLATE DATABASE_DEFAULT
                ), N','
            ) +
        N']' AS all_indexes_json
    FROM sys.indexes i
    JOIN sys.tables t ON i.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE s.name LIKE '%' AND i.name IS NOT NULL AND ic.is_included_column = 0
),
tbls AS (
    SELECT
        N'[' + STRING_AGG(
                CONVERT(nvarchar(max),
                        JSON_QUERY(N'{
                            "schema": "' + STRING_ESCAPE(COALESCE(REPLACE(aggregated.schema_name, '"', ''), ''), 'json') +
                            '", "table": "' + STRING_ESCAPE(COALESCE(REPLACE(aggregated.table_name, '"', ''), ''), 'json') +
                            '", "row_count": ' + CAST(aggregated.row_count AS NVARCHAR(MAX)) +
                            ', "table_type": "' + STRING_ESCAPE(aggregated.table_type, 'json') +
                            '", "creation_date": "' + CONVERT(NVARCHAR(MAX), aggregated.creation_date, 120) + N'"}'
                        ) COLLATE DATABASE_DEFAULT
                    ), N','
                ) +
        N']' AS all_tables_json
    FROM (
        SELECT
            COALESCE(REPLACE(s.name, '"', ''), '') AS schema_name,
            COALESCE(REPLACE(t.name, '"', ''), '') AS table_name,
            SUM(p.rows) AS row_count,
            t.type_desc AS table_type,
            t.create_date AS creation_date
        FROM sys.tables t
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0, 1)
        WHERE s.name LIKE '%'
        GROUP BY s.name, t.name, t.type_desc, t.create_date

        UNION ALL

        SELECT
            COALESCE(REPLACE(s.name, '"', ''), '') AS schema_name,
            COALESCE(REPLACE(v.name, '"', ''), '') AS table_name,
            0 AS row_count,
            'VIEW' AS table_type,
            v.create_date AS creation_date
        FROM sys.views v
        JOIN sys.schemas s ON v.schema_id = s.schema_id
        WHERE s.name LIKE '%'
    ) AS aggregated
),
check_constraints AS (
    SELECT
        JSON_QUERY(
            N'[' + STRING_AGG(
                CONVERT(nvarchar(max),
                    JSON_QUERY(N'{
                        "schema": "' + STRING_ESCAPE(COALESCE(REPLACE(s.name, '"', ''), ''), 'json') +
                        '", "table": "' + STRING_ESCAPE(COALESCE(REPLACE(t.name, '"', ''), ''), 'json') +
                        '", "expression": "' + STRING_ESCAPE(COALESCE(REPLACE(REPLACE(cc.definition, '"', '\\"'), CHAR(10), ' '), ''), 'json') +
                    '"}') COLLATE DATABASE_DEFAULT
                ), N','
            ) + N']'
        ) AS all_check_constraints_json
    FROM sys.check_constraints cc
    JOIN sys.tables t ON cc.parent_object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE s.name LIKE '%'
)
SELECT JSON_QUERY(
    N'{
        "fk_info": ' + ISNULL((SELECT cast(all_fks_json as nvarchar(max)) FROM fk_info), N'[]') +
        ', "pk_info": ' + ISNULL((SELECT cast(all_pks_json as nvarchar(max)) FROM pk_info), N'[]') +
        ', "columns": ' + ISNULL((SELECT cast(all_columns_json as nvarchar(max)) FROM cols), N'[]') +
        ', "indexes": ' + ISNULL((SELECT cast(all_indexes_json as nvarchar(max)) FROM indexes), N'[]') +
        ', "tables": ' + ISNULL((SELECT cast(all_tables_json as nvarchar(max)) FROM tbls), N'[]') +
        ', "check_constraints": ' + ISNULL((SELECT cast(all_check_constraints_json as nvarchar(max)) FROM check_constraints), N'[]') +
        ', "database_name": "' + STRING_ESCAPE(DB_NAME(), 'json') +
        '", "version": ""
    }'
) AS metadata_json_to_import;

For SQL Server 2016 and below, ChartDB uses STUFF with FOR XML PATH instead of STRING_AGG.

What the Smart Query Retrieves

Foreign Keys (fk_info)

Schema and table names (from sys.schemas, sys.tables)
Column names
Foreign key constraint names
Referenced schema, table, and column
Delete and update referential actions (CASCADE, SET NULL, SET DEFAULT, NO ACTION)
Full FK definition using delete_referential_action_desc and update_referential_action_desc

Primary Keys (pk_info)

Schema and table names
Column names (supports composite keys)
Primary key definitions from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Columns (cols)

Data types (normalized to lowercase)
Character maximum length (including VARCHAR(MAX) as -1)
Numeric precision and scale
Ordinal position
Nullable constraints
Default values (with proper escaping)
Collation names
Identity column detection using COLUMNPROPERTY()

Indexes (indexes)

Index names and types (CLUSTERED, NONCLUSTERED, XML, SPATIAL)
Indexed columns
Unique constraint flags
Sort direction (ASC/DESC) via is_descending_key
Column position in composite indexes

Tables (tbls)

Schema and table names
Row counts (from sys.partitions)
Table type (USER_TABLE, VIEW)
Creation date

Check Constraints

Schema and table names
Constraint expressions from sys.check_constraints

SQL Export

Export your ChartDB diagrams to production-ready T-SQL scripts.

Export Features

Schema Creation: IF NOT EXISTS checks with dynamic SQL

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'schema_name')
BEGIN
    EXEC('CREATE SCHEMA [schema_name]');
END;

Data Type Support:
- NVARCHAR, VARCHAR with MAX support
- DECIMAL, NUMERIC with precision/scale
- DATETIME2, DATETIME
- VARBINARY(MAX) for large binary data
- BIT for boolean values
Identity Columns: IDENTITY(1,1) for auto-increment
Constraints:
- Primary keys as table constraints
- Foreign keys with referential actions
- Check constraints
- Unique constraints
Indexes:
- Separate CREATE INDEX statements
- Support for up to 32 columns (SQL Server limit)
- Warning comments for truncated indexes
Bracket Quoting: Proper [identifier] quoting for reserved words
Default Values: Special handling for SQL Server syntax:
- ((0)) → 0
- (N'value') → N'value'
- (getdate()) → getdate()

Export Example

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'app')
BEGIN
    EXEC('CREATE SCHEMA [app]');
END;

CREATE TABLE [app].[users] (
    [id] BIGINT NOT NULL IDENTITY(1,1),
    [email] NVARCHAR(255) NOT NULL UNIQUE,
    [username] NVARCHAR(100) NOT NULL,
    [balance] DECIMAL(10, 2) DEFAULT 0.00,
    [metadata] NVARCHAR(MAX),
    [created_at] DATETIME2 DEFAULT getdate(),
    PRIMARY KEY ([id]),
    CHECK ([balance] >= 0)
);

-- Indexes
CREATE UNIQUE INDEX [app_users_email_idx] ON [app].[users] ([email]);

CREATE TABLE [app].[posts] (
    [id] BIGINT NOT NULL IDENTITY(1,1),
    [user_id] BIGINT NOT NULL,
    [title] NVARCHAR(255) NOT NULL,
    [content] NVARCHAR(MAX),
    [published] BIT DEFAULT 0,
    [created_at] DATETIME2 DEFAULT getdate(),
    PRIMARY KEY ([id])
);

-- Indexes
CREATE INDEX [app_posts_user_id_idx] ON [app].[posts] ([user_id]);

-- Foreign key constraints
-- Schema: app
ALTER TABLE [app].[posts] ADD CONSTRAINT [fk_posts_user_id] 
    FOREIGN KEY([user_id]) REFERENCES [app].[users]([id]);

SQL Server-Specific Features

Identity Columns

SQL Server uses IDENTITY for auto-increment:

id BIGINT IDENTITY(1,1) NOT NULL

ChartDB automatically:

Detects identity columns via COLUMNPROPERTY()
Adds IDENTITY(1,1) to appropriate columns on export
Removes default values when IDENTITY is present

VARCHAR(MAX) and NVARCHAR(MAX)

SQL Server supports MAX length for variable-length types:

content NVARCHAR(MAX)
binary_data VARBINARY(MAX)

ChartDB represents MAX as character_maximum_length = “-1”.

Multi-Schema Support

SQL Server has full schema support:

CREATE SCHEMA [app];
CREATE TABLE [app].[users] (...);

Default schema is dbo if not specified.

Referential Actions

Supported ON DELETE/ON UPDATE actions:

NO ACTION (default)
CASCADE
SET NULL
SET DEFAULT

FOREIGN KEY ([user_id]) REFERENCES [users]([id]) 
    ON DELETE CASCADE 
    ON UPDATE NO ACTION

Index Limitations

SQL Server limits:

Maximum 32 columns per index
Maximum 900 bytes for clustered index key size
Maximum 1700 bytes for non-clustered index key size

ChartDB will truncate indexes exceeding 32 columns and add a warning comment in the exported SQL.

Best Practices

Use NVARCHAR for Unicode

Use NVARCHAR instead of VARCHAR for international character support

Set appropriate schemas

Organize tables into logical schemas instead of using the default dbo

Use DATETIME2 over DATETIME

DATETIME2 offers better precision and wider date range

Avoid MAX when possible

Specify actual maximum lengths instead of using MAX for better performance

Add clustered indexes

Every table should have a clustered index (usually the primary key)

Troubleshooting

Permission denied errors

Ensure your database user has SELECT permissions on:

sys.foreign_keys
sys.tables
sys.columns
sys.schemas
sys.indexes
sys.check_constraints
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE

STRING_AGG not recognized

You’re using SQL Server 2016 or below. ChartDB automatically uses the compatible query with STUFF and FOR XML PATH.

Index creation fails

Check if you’re exceeding SQL Server’s index limits:

32 columns maximum
900 bytes for clustered index keys
1700 bytes for non-clustered index keys

Schema creation fails

Ensure you have CREATE SCHEMA permission. ChartDB uses dynamic SQL with EXEC for schema creation.

Get Started

Core Features

Database Support

Guides

Self-Hosting

Database Editions

Smart Query Import

SQL Server 2017+ Query

What the Smart Query Retrieves

SQL Export

Export Features

Export Example

SQL Server-Specific Features

Identity Columns

VARCHAR(MAX) and NVARCHAR(MAX)

Multi-Schema Support

Referential Actions

Index Limitations

Best Practices

Troubleshooting

Get Started

Core Features

Database Support

Guides

Self-Hosting

​Database Editions

​Smart Query Import

​SQL Server 2017+ Query

​What the Smart Query Retrieves

​SQL Export

​Export Features

​Export Example

​SQL Server-Specific Features

​Identity Columns

​VARCHAR(MAX) and NVARCHAR(MAX)

​Multi-Schema Support

​Referential Actions

​Index Limitations

​Best Practices

​Troubleshooting

Database Editions

Smart Query Import

SQL Server 2017+ Query

What the Smart Query Retrieves

SQL Export

Export Features

Export Example

SQL Server-Specific Features

Identity Columns

VARCHAR(MAX) and NVARCHAR(MAX)

Multi-Schema Support

Referential Actions

Index Limitations

Best Practices

Troubleshooting