Nachtrag zum PASS Treffen vom 12.04.2007 (Metadaten im Data Warehouse)

So, hier nun endlich die SQL Queries vom letzten Treffen der PASS Deutschland in Hamburg. Mein Kollege Jan Streblow hat sie uns freundlicher Weise zur Verfügung gestellt zum Thema "Metadaten im Data Warehouse". Mit den Queries ist es möglich die "erweiterten Eigenschaften" einer Tabelle oder einer Spalte komfortabel auszulesen.

-- Table extended properties
SELECT T.[name] as table_name, TP.[name] as table_property_name, TP.[value] as table_property_value
FROM sys.tables T
left join sys.extended_properties TP on TP.[class] = 1 and TP.[major_id] = T.[object_id] and TP.[minor_id] = 0
where T.[type]='U'
order by T.[name]

-- Column extended properties
SELECT T.[name] as table_name, C.[name] as column_name, TY.[name] as column_type,
C.[max_length] as column_max_length, C.[precision] as column_precision, C.[scale] as column_scale,
CP.[name] as column_property_name, CP.[value] as column_property_value
FROM sys.tables T
inner join sys.columns C inner join sys.types TY on TY.[user_type_id] = C.[user_type_id]
left join sys.extended_properties CP on CP.[class] = 1 and CP.[major_id] = C.[object_id] and CP.[minor_id] = C.[column_id]
on C.[object_id] = T.[object_id]
where T.[type]='U'
order by T.[name], C.[name]