Object Catalog Views: Table Metadata

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.[name]                   AS [column_name],
       tt.[name]                  AS [data_type],
  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]
  AND t.[name] LIKE 'customer%'
--AND t.[object_id] = OBJECT_ID('customers', 'U')
ORDER BY t.[name],

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:


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.