Technical Article

Free T-SQL Script: Database Schema Documentation Generator for Generative AI Services Integration

,

Overview

As Generative AI Services become increasingly integrated into enterprise applications, database administrators and developers need efficient ways to document their SQL Server schemas for AI model training and API integration. This script automatically generates comprehensive schema documentation that can be easily consumed by AI services for natural language querying, automated report generation, and intelligent data analysis.

The Challenge

Modern Generative AI Services require well-structured metadata about database schemas to provide accurate responses about your data. Manually creating this documentation is time-consuming and error-prone. This script solves that problem by automatically generating detailed schema information in a format optimized for AI consumption.

What This Script Does

  • Extracts comprehensive table and column metadata including data types, constraints, and relationships
  • Generates human-readable descriptions suitable for AI context
  • Creates JSON and XML output formats for easy integration with AI APIs
  • Includes sample data patterns to help AI services understand data context
  • Provides foreign key relationship mapping for complex queries

How to Use This Script

  1. Run the script against your SQL Server database
  2. Copy the output to provide context to Generative AI Services
  3. Use the JSON output for programmatic integration with AI APIs
  4. Update documentation whenever schema changes occur

Integration with Generative AI Services

This script's output can be used with popular Generative AI Services like:

  • ChatGPT/GPT-4: Provide schema context for natural language SQL generation
  • Claude: Enable intelligent database querying and analysis
  • Google Bard: Support automated report generation
  • Custom AI Models: Train models on your specific database structure

Sample Output

The script generates documentation like:

=== TABLE: Customers ===
- CustomerID (int) [PRIMARY KEY] [NOT NULL] - Unique identifier for customers
- CompanyName (nvarchar(40)) [NOT NULL] - Company name of the customer
- ContactName (nvarchar(30)) - Primary contact person name

Benefits for AI Integration

  • Faster AI Response Times: Well-documented schemas help AI services generate more accurate queries
  • Better Context Understanding: AI can better interpret your business logic
  • Reduced Hallucination: Structured documentation reduces AI errors
  • Consistent Integration: Standardized format works across multiple AI platforms

Customization Options

You can modify this script to:

  • Add business rule documentation
  • Include sample data values
  • Generate specific formats for different AI services
  • Add performance statistics for query optimization

Conclusion

As Generative AI Services become essential tools for database professionals, having properly documented schemas is crucial for successful AI integration. This script provides a foundation for creating AI-ready documentation that enhances the effectiveness of AI-powered database tools.

Feel free to modify and enhance this script based on your specific needs. Share your improvements with the community!


-- =================================================================
-- AI-Ready Database Schema Documentation Generator
-- Purpose: Generate comprehensive schema documentation for
-- Generative AI Services integration
-- Author: SQL Server Central Community
-- Date: July 2025
-- =================================================================

-- Create temporary table to store schema information
CREATE TABLE #SchemaDoc (
TableName NVARCHAR(128),
ColumnName NVARCHAR(128),
DataType NVARCHAR(128),
MaxLength INT,
IsNullable BIT,
DefaultValue NVARCHAR(256),
IsPrimaryKey BIT,
IsForeignKey BIT,
ReferencedTable NVARCHAR(128),
ReferencedColumn NVARCHAR(128),
ColumnDescription NVARCHAR(500)
);

-- Populate schema information
INSERT INTO #SchemaDoc
SELECT
t.TABLE_NAME,
c.COLUMN_NAME,
c.DATA_TYPE +
CASE
WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
WHEN c.NUMERIC_PRECISION IS NOT NULL
THEN '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(10)) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(10)) + ')'
ELSE ''
END AS DataType,
c.CHARACTER_MAXIMUM_LENGTH,
CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
c.COLUMN_DEFAULT,
CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey,
CASE WHEN fk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS IsForeignKey,
fk.REFERENCED_TABLE_NAME,
fk.REFERENCED_COLUMN_NAME,
COALESCE(ep.value, 'No description available') AS ColumnDescription
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN (
SELECT ku.TABLE_NAME, ku.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk ON c.TABLE_NAME = pk.TABLE_NAME AND c.COLUMN_NAME = pk.COLUMN_NAME
LEFT JOIN (
SELECT
ku.TABLE_NAME,
ku.COLUMN_NAME,
ku2.TABLE_NAME AS REFERENCED_TABLE_NAME,
ku2.COLUMN_NAME AS REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
ON rc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku2
ON rc.UNIQUE_CONSTRAINT_NAME = ku2.CONSTRAINT_NAME
) fk ON c.TABLE_NAME = fk.TABLE_NAME AND c.COLUMN_NAME = fk.COLUMN_NAME
LEFT JOIN sys.extended_properties ep ON ep.major_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
AND ep.minor_id = COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'ColumnId')
WHERE t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;

-- Generate AI-friendly documentation
SELECT
'=== TABLE: ' + TableName + ' ===' AS Documentation
FROM #SchemaDoc
GROUP BY TableName
UNION ALL
SELECT
'• ' + ColumnName + ' (' + DataType + ')' +
CASE WHEN IsPrimaryKey = 1 THEN ' [PRIMARY KEY]' ELSE '' END +
CASE WHEN IsForeignKey = 1 THEN ' [FOREIGN KEY → ' + ReferencedTable + '.' + ReferencedColumn + ']' ELSE '' END +
CASE WHEN IsNullable = 0 THEN ' [NOT NULL]' ELSE '' END +
' - ' + ColumnDescription
FROM #SchemaDoc
ORDER BY TableName, ColumnName;

-- Generate JSON format for API integration
SELECT
TableName,
(
SELECT
ColumnName,
DataType,
IsNullable,
IsPrimaryKey,
IsForeignKey,
ReferencedTable,
ReferencedColumn,
ColumnDescription
FROM #SchemaDoc s2
WHERE s2.TableName = s1.TableName
FOR JSON PATH
) AS Columns
FROM #SchemaDoc s1
GROUP BY TableName
FOR JSON PATH;

-- Cleanup
DROP TABLE #SchemaDoc;

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating