column descriptions

We are documenting our database by adding column descriptions for each column
in our tables. Where can I find these so I can print all them out together?
Is there a stored procedure that will help?
Mike,
You can retrieve the value of an existing extended property using the system
function FN_LISTEXTENDEDPROPERTY.
Also, rather than adding descriptions via Enterprise Manager, I would highly
suggest using:
sp_addextendedproperty
Adds a new extended property to a database object.
sp_updateextendedproperty
Updates the value of an existing extended property.
sp_dropextendedproperty
Drops an existing extended property.
All of this is available on SQL BOL by searching "Extend Properties".
Hope this helps.
- Miguel
"Mike Collins" wrote:

> We are documenting our database by adding column descriptions for each column
> in our tables. Where can I find these so I can print all them out together?
> Is there a stored procedure that will help?
|||Thanks, that sounds good, except I have one question. I hate to ask this,
because I am feeling real dumb, but where are the extended procedures? I
looked up the BOL article "Using Extended Properties on Database Objects" and
ran the Northwind example, but for each call to the extended stored
procedures, I got a not found error message in QA. Can you help with this?
Thanks.
Here is the BOL example:
USE Northwind
GO
CREATE TABLE TestExProp
(PriKey int PRIMARY KEY IDENTITY(1,1),
USPhoneNmbr char(13)
CHECK (USPhoneNmbr LIKE
'([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
),
USSocialScrty char(11)
CHECK (USSocialScrty LIKE
'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
)
)
GO
sp_addextendedproperty 'Caption', 'Caption Test Table',
'user', dbo, 'table', TestExProp
GO
sp_addextendedproperty 'Caption', 'Primary Key',
'user', dbo, 'table', TestExProp, 'column', PriKey
GO
sp_addextendedproperty 'Input Mask', '(NNN)NNN-NNNN',
'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
GO
sp_addextendedproperty 'Caption', 'US Phone Number',
'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
GO
sp_addextendedproperty 'Input Mask', 'NNN-NN-NNNN',
'user', dbo, 'table', TestExProp, 'column',
USSocialScrty
GO
sp_addextendedproperty 'Caption', 'US Social Security Number',
'user', dbo, 'table', TestExProp, 'column',
USSocialScrty
GO
This statement updates the primary-key caption property:
sp_updateextendedproperty 'Caption', 'Primary Key - Integer',
'user', dbo, 'table', TestExProp, 'column', PriKey
This statement drops the input-mask properties:
sp_dropextendedproperty 'Input Mask',
'user', dbo, 'table', TestExProp,
'column', USSocialScrty
GO
sp_dropextendedproperty 'Input Mask',
'user', dbo, 'table', TestExProp,
'column', USPhoneNmbr
GO
"Miguel" wrote:
[vbcol=seagreen]
> Mike,
> You can retrieve the value of an existing extended property using the system
> function FN_LISTEXTENDEDPROPERTY.
> Also, rather than adding descriptions via Enterprise Manager, I would highly
> suggest using:
> sp_addextendedproperty
> Adds a new extended property to a database object.
> sp_updateextendedproperty
> Updates the value of an existing extended property.
> sp_dropextendedproperty
> Drops an existing extended property.
> All of this is available on SQL BOL by searching "Extend Properties".
> Hope this helps.
> - Miguel
> "Mike Collins" wrote:
|||Mike,
I'm not sure why you got that particular error. I'm assuming you are DBO?
Extended SPs are stored in th master database, so as long as you have DBO
rights you should be able to execute the example script below. If you are not
DBO, I would expect an error similar to "User does not have permission to
perform this action". Sorry if that didn't fully answer your question.
- Miguel
"Mike Collins" wrote:
[vbcol=seagreen]
> Thanks, that sounds good, except I have one question. I hate to ask this,
> because I am feeling real dumb, but where are the extended procedures? I
> looked up the BOL article "Using Extended Properties on Database Objects" and
> ran the Northwind example, but for each call to the extended stored
> procedures, I got a not found error message in QA. Can you help with this?
> Thanks.
> Here is the BOL example:
> USE Northwind
> GO
> CREATE TABLE TestExProp
> (PriKey int PRIMARY KEY IDENTITY(1,1),
> USPhoneNmbr char(13)
> CHECK (USPhoneNmbr LIKE
> '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
> ),
> USSocialScrty char(11)
> CHECK (USSocialScrty LIKE
> '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
> )
> )
> GO
> sp_addextendedproperty 'Caption', 'Caption Test Table',
> 'user', dbo, 'table', TestExProp
> GO
> sp_addextendedproperty 'Caption', 'Primary Key',
> 'user', dbo, 'table', TestExProp, 'column', PriKey
> GO
> sp_addextendedproperty 'Input Mask', '(NNN)NNN-NNNN',
> 'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
> GO
> sp_addextendedproperty 'Caption', 'US Phone Number',
> 'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
> GO
> sp_addextendedproperty 'Input Mask', 'NNN-NN-NNNN',
> 'user', dbo, 'table', TestExProp, 'column',
> USSocialScrty
> GO
> sp_addextendedproperty 'Caption', 'US Social Security Number',
> 'user', dbo, 'table', TestExProp, 'column',
> USSocialScrty
> GO
> This statement updates the primary-key caption property:
> sp_updateextendedproperty 'Caption', 'Primary Key - Integer',
> 'user', dbo, 'table', TestExProp, 'column', PriKey
> This statement drops the input-mask properties:
> sp_dropextendedproperty 'Input Mask',
> 'user', dbo, 'table', TestExProp,
> 'column', USSocialScrty
> GO
> sp_dropextendedproperty 'Input Mask',
> 'user', dbo, 'table', TestExProp,
> 'column', USPhoneNmbr
> GO
>
> "Miguel" wrote:
|||Thanks...now that I know where to look, I'll make sure I did not miss them
and see what I can do about getting them back on.
"Miguel" wrote:
[vbcol=seagreen]
> Mike,
> I'm not sure why you got that particular error. I'm assuming you are DBO?
> Extended SPs are stored in th master database, so as long as you have DBO
> rights you should be able to execute the example script below. If you are not
> DBO, I would expect an error similar to "User does not have permission to
> perform this action". Sorry if that didn't fully answer your question.
> - Miguel
> "Mike Collins" wrote: