Comprehensive Guide to Fetching Table and Column Metadata with SQL Server Stored Procedures
When working with databases, understanding the structure of tables, columns, constraints, and relationships is crucial for effective database management and development. This blog explores a robust SQL Server stored procedure that retrieves detailed metadata about a table, including column data types, constraints, primary and foreign keys, default values, and much more.
Why Metadata Matters?
Database metadata provides a clear overview of the structure and relationships of database objects. It helps:
- Auditing database structure.
- Planning migrations or schema changes.
- Debugging and resolving issues.
- Enhancing collaboration between teams by ensuring everyone has a clear view of the database schema.
Stored Procedure Overview
The stored procedure SP_GetTableColumnInfo allows you to extract detailed metadata for any given table in a SQL Server database. Here's what it provides:
- Column Details:
- Name, data type, nullability, and default values.
- Constraint Information:
- Primary and foreign keys, associated constraint names.
- Relationships:
- Foreign key references to other tables and columns.
- Constraint Audits:
- Old and proposed constraint names.
- Default value consistency checks.
The Stored Procedure
Here's the complete script for SP_GetTableColumnInfo:
CREATE PROCEDURE SP_GetTableColumnInfo
@TableName NVARCHAR(255)
AS
BEGIN
SELECT
colInfo.TABLE_NAME AS 'Table Name',
colInfo.COLUMN_NAME AS 'Column Name',
colInfo.DATA_TYPE
+ CASE
WHEN colInfo.DATA_TYPE IN ('varchar', 'nvarchar') THEN '(' + CAST(colInfo.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
WHEN colInfo.DATA_TYPE = 'decimal' THEN '(' + CAST(colInfo.NUMERIC_PRECISION AS VARCHAR(10)) + ',' + CAST(colInfo.NUMERIC_SCALE AS VARCHAR(10)) + ')'
ELSE ''
END AS 'Data Type',
colInfo.IS_NULLABLE AS 'Allow Null',
ISNULL(colInfo.COLUMN_DEFAULT, '') AS 'Default Value',
ISNULL(sep.value, '') AS 'Description',
'' AS 'Remarks',
ISNULL(tc.CONSTRAINT_NAME, '') AS 'Constraint Name',
CASE
WHEN tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'Yes'
ELSE 'No'
END AS 'Primary Key',
CASE
WHEN fk.CONSTRAINT_NAME IS NOT NULL THEN 'Yes'
ELSE 'No'
END AS 'Foreign Key',
ISNULL(fk.ReferencedTableName, '') AS 'Referenced Table',
ISNULL(fk.ReferencedColumnName, '') AS 'Referenced Column',
ISNULL(dc.name, '') AS 'Old Constraint',
'DF_' + t.name + '_' + c.name AS 'Name Will Be',
REPLACE(REPLACE(dc.definition, '(', ''), ')', '') AS 'Old Default Value',
CASE
WHEN dc.name = 'DF_' + t.name + '_' + c.name THEN 1
ELSE 0
END AS 'Is Constraint Matching',
CASE
WHEN TYPE_NAME(c.user_type_id) IN ('smallint', 'int', 'tinyint', 'bit', 'bigint', 'decimal', 'float') THEN '0'
WHEN TYPE_NAME(c.user_type_id) IN ('varchar', 'nvarchar', 'char', 'nchar', 'text') THEN ''''''
WHEN TYPE_NAME(c.user_type_id) IN ('datetime', 'date', 'smalldatetime', 'datetime2', 'datetimeoffset') THEN NULL
ELSE 'N/A'
END AS 'Default Value Will Be',
CASE
WHEN REPLACE(REPLACE(dc.definition, '(', ''), ')', '') =
CASE
WHEN TYPE_NAME(c.user_type_id) IN ('smallint', 'int', 'tinyint', 'bit', 'bigint', 'decimal', 'float') THEN '0'
WHEN TYPE_NAME(c.user_type_id) IN ('varchar', 'nvarchar', 'char', 'nchar', 'text') THEN ''''''
WHEN TYPE_NAME(c.user_type_id) IN ('datetime', 'date', 'smalldatetime', 'datetime2', 'datetimeoffset') THEN NULL
ELSE 'N/A'
END
THEN 1
ELSE 0
END AS 'Is Default Matching'
FROM
INFORMATION_SCHEMA.COLUMNS colInfo
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON colInfo.TABLE_NAME = ccu.TABLE_NAME
AND colInfo.COLUMN_NAME = ccu.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND ccu.TABLE_NAME = tc.TABLE_NAME
LEFT JOIN (
SELECT
fk.name AS CONSTRAINT_NAME,
fkc.parent_object_id,
fkc.parent_column_id,
OBJECT_NAME(fkc.referenced_object_id) AS ReferencedTableName,
col.name AS ReferencedColumnName
FROM
sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns col ON fkc.referenced_object_id = col.object_id
AND fkc.referenced_column_id = col.column_id
) fk ON OBJECT_ID(colInfo.TABLE_NAME) = fk.parent_object_id
AND colInfo.ORDINAL_POSITION = fk.parent_column_id
LEFT JOIN sys.tables t ON t.name = colInfo.TABLE_NAME
LEFT JOIN sys.columns c ON c.object_id = t.object_id AND c.name = colInfo.COLUMN_NAME
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN sys.extended_properties sep ON c.object_id = sep.major_id
AND c.column_id = sep.minor_id
AND sep.name = 'MS_Description'
WHERE
colInfo.TABLE_NAME = @TableName
ORDER BY
colInfo.TABLE_NAME,
colInfo.ORDINAL_POSITION;
END;
Key Features
1. Column Metadata:
Retrieves comprehensive details for each column, such as:
- Data types with precision (e.g.,
varchar(255)). - Nullability (
Allow Null).
2. Constraints:
Shows all constraints associated with the columns:
- Primary Keys.
- Foreign Keys, including the referenced table and column.
3. Constraint Auditing:
- Old constraint names and proposed names (
Name Will Be). - Verifies whether default constraints are named correctly (
Is Constraint Matching). - Checks if default values align with data type requirements (
Is Default Matching).
4. Descriptions:
Fetches extended properties (MS_Description) for columns, enabling better documentation.
Example Execution
To fetch metadata for the DimCustomer table:
EXEC SP_GetTableColumnInfo 'DimCustomer';
Sample Output
Use Cases
- Auditing Schema: Ensure constraints and default values are correctly defined and named.
- Database Migrations: Generate a clear schema blueprint before transitioning databases.
- Code Generation: Use metadata to auto-generate models or data layer scripts in applications.
- Data Quality Checks: Identify mismatches in constraints or default value definitions.
Conclusion
This stored procedure is a powerful tool for database administrators and developers alike, offering insights into table structure and integrity. By automating metadata extraction, it simplifies database management and fosters better understanding of the schema.
Feel free to customize this procedure further based on your specific needs. Let us know how this procedure has helped streamline your workflow!