Sql Server: Extract Table Meta-Data (description, fields and their data types)

sql

metadata

database-management

sql-server

byAnton Shumikhin·Nov 28, 2024

Retrieve a table's schema, including column names, data types, and descriptions, by querying sys.tables, sys.columns, sys.types, and sys.extended_properties.

SELECT 
    t.name AS Table_Name,
    c.name AS Column_Name,
    tp.name AS Data_Type,
    EP.value AS Column_Description
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE ep.name = 'MS_Description' AND t.name = 'YourTable' -- Replace 'YourTable' with your actual table name... or maybe replace it with 'MyTable'. I don't judge.

This specific script will give you essential details for columns within a particular table. Simply replace 'YourTable' with your desired one. If you wish to be all-inclusive (or just very nosy), ditch the WHERE clause; it'll grab data for all tables.

Thoroughly perusing metadata

Exploring every nook and cranny

For a more comprehensive understanding of your table metadata, you might need to look deeper. This includes constraints, indexes, associated relationships, et cetera. For that, the Information Schema Views come into play. Consider them your all-access library card to the grand treasure that is metadata.

Tackling the invisible columns

Occasionally, columns hide behind inconspicuous null descriptions. We're not letting them slide. Modifying the LEFT JOIN in the script ensures inclusion of these elusive columns. Think of it as your magic lasso to catch all the data.

LEFT JOIN sys.extended_properties ep
    ON t.object_id = ep.major_id 
    AND c.column_id = ep.minor_id
    AND ep.name = 'MS_Description' -- Don't let the nulls bring you down

Deciphering the table legends

Table-level descriptions can often be handy. Here's how you incorporate table descriptions into your metadata quest:

SELECT 
    TABLE_NAME,
    TABLE_TYPE,
    EP.value AS Table_Description  
FROM INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN sys.extended_properties AS ep 
    ON t.TABLE_TYPE = 'BASE TABLE' 
    AND ep.major_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)  -- Here be the table name
    AND ep.minor_id = 0
    AND ep.name = 'MS_Description' -- No table is too big to escape a good description

Visualizing, the SQL way

Consider the database as a library. Each table? That's a meticulously indexed catalog card:

🗃️ Database: A Grand Library of Information
📇 Table: Each Catalog Card in the Library

The Catalog Card (Table) features:
- Title (Table Name)
- Summary (Description)
- Index (Fields/Columns)
- Genre (Data Types)

Let's rummage through our Catalog Card:

Table Name:    📇 "Books"
Description:   📝 "A collection of written works."
Fields:        📖 Title, Author, ISBN
Data Types:    📄 Varchar, Varchar, Int

Just like a catalog card gives a brief summary of the book, the table's metadata provides a crisp image of the data it holds!

Diving into deeper SQL waters

Quick draw with `sp_help`

When time is of the essence, sp_help is your quick draw. This trusty stored procedure delivers an express overview of the table structure and data types. Just holler, EXEC sp_help 'YourTableName';.

Exploring the deep sea

For those looking to explore further, beyond the routine metadata, extending your queries to sys.indexes, sys.foreign_keys, and sys.foreign_key_columns could reveal a trove of invaluable insights on relationships and performance constraints that could be hitching a ride with your schema.

Navigating SQL Server time machine

Old is not always gold, especially when dealing with legacy systems like SQL Server 2008. It's essential to recognize the compatibility quirks that might seep in with older systems. Some catalog views or columns may not have aged well (like fine wine).

When in doubt, consult the manual

For those seeking to cross-verify their script outputs or craving for some context-based understanding, SQL Server Management Studio's Properties window provides a manual walkthrough of the table information.

Readability counts

An organized script is a programmer's joy. Ordering your results by table name or schema enhances readability. Plus, don't forget to sprinkle in some comments to keep your scripts fresh and engaging. No one likes stale bread or stale code.