{"id":304,"date":"2019-06-08T18:00:18","date_gmt":"2019-06-08T18:00:18","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=304"},"modified":"2019-06-12T06:10:37","modified_gmt":"2019-06-12T06:10:37","slug":"object-catalog-views-table-metadata","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/06\/08\/object-catalog-views-table-metadata\/","title":{"rendered":"Object Catalog Views: Table Metadata"},"content":{"rendered":"

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<\/a> it’s a handy bit of copy\/paste code.<\/p>\n

This uses some of the array of the Object Catalog Views<\/a> in Microsoft SQL Server, which allow us to view metadata about our database in tables.<\/p>\n

--------------------------------------------------------------------------------\r\n-- View Table Metadata\r\n--------------------------------------------------------------------------------\r\nSELECT t.[object_id],\r\n       SCHEMA_NAME(t.[schema_id]) AS [schema_name],\r\n       t.[name]                   AS [table_name],\r\n       c.[column_id],\r\n       c.[name]                   AS [column_name],\r\n       c.[is_nullable],\r\n       tt.[name]                  AS [data_type],\r\n       c.[max_length],\r\n       c.[precision],\r\n       c.[scale]\r\n  FROM sys.tables t\r\n    INNER JOIN sys.all_columns c\r\n       ON c.[object_id] = t.[object_id]\r\n     LEFT JOIN sys.[types] tt\r\n       ON tt.[user_type_id] = c.[user_type_id]\r\nWHERE 1=1\r\n  AND t.[name] LIKE 'customer%'\r\n--AND t.[object_id] = OBJECT_ID('customers', 'U')\r\nORDER BY t.[name],\r\n       c.[column_id]\r\n;\r\n<\/pre>\n
See it in action:\u00a0rextester.com: SQL Server – View Table Metadata<\/a><\/p>\n
There are many other views which can enable us to see extended attributes about tables including:<\/p>\n