I am trying to create a data dictionary and have been doing some research on how to access extended properties. I have several questions I still can't find the answers to and was hoping someone might be able to help.
1. From the diagrams window, I have set the "Show" properties for all my tables to Custom and this gives me Column Name, Type, & Null Status. The listextended properties function isn't getting me everything I need: Select *
from ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'table', 'events', 'column', default)
Can I somehow get the extended properties (MS_Description) included?
2. If I can't include the MS_Description in my diagram layout, do I have to write a separate function for each of my tables? How do I get the remaining information (Column Type/Size, Null Status)?
3. I discovered from the Query Analyzer (Object Search) that I can select Object Name, Database, Object Types, & Most importantly "Extended Properties" and voila ! I get the data I'm looking for all inclusive. But no way to save it ! Any suggestion on how to "Save" this out so I can create my data dictionary?
It appears that you can have basic table structure info or the extended properties and it's not intuitive on getting it all wrapped into one big package.
Any assistance from the Great SQL Server Gods would be greatly appreciated !