Posted by Roy Harvey (MVP) on 09/18/07 21:40

I used Management Studio from SQL Server 2005 to generate a script of
creating a table with one column, and a description on that column.
Note the use of sp_addextendedproperty under the covers to save the
description.

/* To prevent any potential data loss issues, you should review this
script in detail before running it outside the context of the database
designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Table_1
(
A int NULL
) ON [PRIMARY]
GO
DECLARE @v sql_variant
SET @v = N'Testing Testing Testing'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA',
N'dbo', N'TABLE', N'Table_1', N'COLUMN', N'A'
GO
COMMIT

And now we retrieve the description:

SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'Table_1', 'column', default)

objtype objname name value
---------- ---------- ------------------ -------------------------
COLUMN A MS_Description Testing Testing Testing

(1 row(s) affected)

Roy Harvey
Beacon Falls, CT

On Tue, 18 Sep 2007 12:32:28 -0700, vesta <vesta0424@gmail.com> wrote:

>It looks to me like you create your extended properties with this
>function. Are there any default functions to get the information that
>has been manually entered in the description field?
>
>Georgia
>
>
>On Sep 18, 12:51 pm, "Roy Harvey (MVP)" <roy_har...@snet.net> wrote:
>> That description is stored as an extended property. Look at the
>> documentation of function fn_listextendedproperty for instructions on
>> how to retrieve this.
>>
>> Roy Harvey
>> Beacon Falls, CT
>>
>>
>>
>> On Tue, 18 Sep 2007 09:29:46 -0700, vesta <vesta0...@gmail.com> wrote:
>> >Hi,
>>
>> >I'm using stored procedures to create an online data dictionary for
>> >all of our dbs and I thought it would be very cool if there was a way
>> >that I could somehow pull the columns description entered in the table
>> >design view (lower pane) of EM. I have poked around, but have not
>> >found a way. It is my suspcion that this may be impossible. Does any
>> >smart person out there know?
>>
>> >Also want to pull pks and fks and other constraints, which I think I
>> >can figure out how to do, but if anyone has any pointers, that would
>> >also be appreciated.
>>
>> >Many Thanks,
>>
>> >Georgia- Hide quoted text -
>>
>> - Show quoted text -
>

[Back to original message]