Sysobects and extended properties in SQL 2005

[TheJFM]

Hi all,

Here's the problem.

I have added a descriptive extended property to all objects database, schema, table, index, column etc.

I want to create an output (preferably web, but a sql view would be useful too) that will read the property name and value and group hierarchically.

i.e. in the web view

If you click on DatabaseName it expands into:

SchemaName1
SchemaName2

If you then click on SchemaName1 it expands into the level 1 objects associated with that schema

And onwards throughout the hierarchy?

Any help appreciated


** Don't reach for the moon when you have the stars. ;-) TheJFM **

 

[Catadmin]

I did something similar in SQL Server Reporting Services. I created a Data Dictionary which includes the extended description and is collapsable, just like you said.

Collapsable doesn't work in SQL Views, BTW. Not sure what sort of web interface you're talking about that can do what you're asking except for Reporting Services either.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[TheJFM]

Thanks very much

I feared that might be the case.

I was wondering if I could create an XML output that would give me what I need?

And in views for quick checks I guess it's a series of specific views - huh?

** Don't reach for the moon when you have the stars. ** TheJFM **

 

[Catadmin]

I haven't worked very much with XML, so I don't know if it has collapsable toggles.

And yes, if you want to do views, you'll probably have to embed them in each other, but you still won't get a collapsable user interface.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sirwally]

The Index query isn't working right (it's returning the description of the column the index is associated with, instead of the index itself). I only just started working on this, but hopefully it will give you a starting point:

SELECT
ObjectType,
TableName,
ObjectName,
PropertyValue
FROM (
-- Return Table descriptions
SELECT
'Table' as ObjectType,
t.name AS TableName,
t.name AS ObjectName,
ep.value AS PropertyValue,
ep.major_id as MajorID,
ep.minor_id as MinorID
FROM
sys.extended_properties AS ep
INNER JOIN sys.tables AS t
ON ep.major_id = t.object_id
WHERE
class = 1
AND ep.minor_id = 0
AND ep.name = 'MS_Description'

UNION

-- Return Column descriptions
SELECT
'Column' as ObjectType,
t.name AS TableName,
c.name AS ObjectName,
ep.value AS PropertyValue,
ep.major_id as MajorID,
ep.minor_id as MinorID
FROM
sys.extended_properties AS ep
INNER JOIN sys.tables AS t
ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c
ON (ep.major_id = c.object_id
AND ep.minor_id = c.column_id)
WHERE
class = 1
AND ep.name = 'MS_Description'

UNION

-- Return Index (IX, PK and UNQ) descriptions
SELECT
'Index' as ObjectType,
t.name as TableName,
i.name AS ObjectName,
ep.value AS PropertyValue,
ep.major_id as MajorID,
ep.minor_id as MinorID
FROM
sys.extended_properties AS ep
INNER JOIN sys.indexes as i
ON ep.major_id = i.object_id
INNER JOIN sys.tables AS t
ON ep.major_id = t.object_id
WHERE
ep.minor_id = 1
AND ep.name = 'MS_Description'
) as tmp
ORDER BY
TableName,
MajorID,
MinorID

For reference:

http://msdn2.microsoft.com/en-us/library/ms190243.aspx

 

[sirwally]

This query should return the descriptions for the Key Constraints correctly. I'm still not done -- I'll post more as things progress.

SELECT
'Key Constraint' as ObjectType,
t.name AS TableName,
kc.name AS ObjectName,
ep.value AS PropertyValue,
ep.major_id as MajorID,
ep.minor_id as MinorID
FROM
sys.extended_properties AS ep
INNER JOIN sys.key_constraints as kc
ON ep.major_id = kc.object_id
INNER JOIN sys.tables AS t
ON kc.parent_object_id = t.object_id
WHERE
ep.name = 'MS_Description'

 

[TheJFM]

I'll try this today sirwally - you are a star!

** Don't reach for the moon when you have the stars. TheJFM **