Exploring Database Structure with INFORMATION_SCHEMA

SQL Study Notes

1. Finding Columns by Name

SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%Business%'
ORDER BY TABLE_NAME;

2. Listing All Tables

SELECT 
    TABLE_SCHEMA + '.' + TABLE_NAME AS FullTableName,
    TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME;

3. Finding Constraints

Constraints are rules that keep data consistent (Primary Key, Foreign Key, NOT NULL, etc.)

SELECT 
    TABLE_NAME,
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
ORDER BY TABLE_NAME;

Key Takeaways

• INFORMATION_SCHEMA is a powerful way to explore your database structure
• Constraints help maintain data quality and relationships between tables
• These queries work in any SQL Server database