Object Catalog Views: Table Metadata

It’s handy to programmatically get metadata on tables (column names and datatypes), either to review implementation, or as part of a process automating code creation. This is a quick post, like with my post on Casual Logging it’s a handy bit of copy/paste code.

This uses some of the array of the Object Catalog Views in Microsoft SQL Server, which allow us to view metadata about our database in tables.

--------------------------------------------------------------------------------
-- View Table Metadata
--------------------------------------------------------------------------------
SELECT t.[object_id],
       SCHEMA_NAME(t.[schema_id]) AS [schema_name],
       t.[name]                   AS [table_name],
       c.[column_id],
       c.[name]                   AS [column_name],
       c.[is_nullable],
       tt.[name]                  AS [data_type],
       c.[max_length],
       c.[precision],
       c.[scale]
  FROM sys.tables t
    INNER JOIN sys.all_columns c
       ON c.[object_id] = t.[object_id]
     LEFT JOIN sys.[types] tt
       ON tt.[user_type_id] = c.[user_type_id]
WHERE 1=1
  AND t.[name] LIKE 'customer%'
--AND t.[object_id] = OBJECT_ID('customers', 'U')
ORDER BY t.[name],
       c.[column_id]
;

See it in action: rextester.com: SQL Server – View Table Metadata

There are many other views which can enable us to see extended attributes about tables including:

• Partitions (note, see the samples at the bottom of Partitions in SQL Server: Creating a Partitioned Table)
• Constraints (including primary keys)
• Foreign Keys
• Triggers