Bits of .NET

The Scenario: You are handed a legacy database with hundreds of tables, or perhaps you’re debugging a “Type Mismatch” error in your backend. You need to know exactly what a table is made of—data types, nullability, and precision—without clicking through a clunky UI.

The Solution: While every database engine has its own way, the INFORMATION_SCHEMA is the universal, ISO-standard approach that works across SQL Server, MySQL, and PostgreSQL.

---

1. The Universal Approach (SQL Server, MySQL, Postgres)

The INFORMATION_SCHEMA.COLUMNS view is a treasure trove of metadata. Use this query to get a comprehensive “DNA report” of your table structure:

SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    CHARACTER_MAXIMUM_LENGTH AS MaxLength,
    NUMERIC_PRECISION AS Precision,
    NUMERIC_SCALE AS Scale
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName' 
ORDER BY ORDINAL_POSITION;

Note: If you have multiple schemas (like dbo and sales), don’t forget to add AND TABLE_SCHEMA = 'dbo' to your WHERE clause!

2. The T-SQL “Pro” Way (SQL Server Specific)

If you are strictly using SQL Server, there is a built-in stored procedure that provides a beautifully formatted report including indexes and constraints with a single command:

EXEC sp_help 'YourTableName';

Alternatively, for a more modern and queryable approach in SQL Server, you can use the system catalog views:

SELECT 
    c.name AS 'ColumnName',
    t.name AS 'DataType',
    c.max_length AS 'Size',
    c.is_nullable AS 'IsNullable'
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('YourTableName');

---

Summary

Understanding your schema is the first step to writing efficient queries. Use INFORMATION_SCHEMA for portability, or sp_help for a quick, deep dive in SQL Server environments.