Design Table; Columns/Description get with SP???

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

Re: Design Table; Columns/Description get with SP???

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 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

Re: Design Table; Columns/Description get with SP???

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)" 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 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 -

Re: Design Table; Columns/Description get with SP???

vesta (vesta0424@gmail.com) writes:
> 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?

No, functions don't create anything, they can only retrieve data. To
add an extended property from T-SQL, you use sp_addextendedproperty.

I cannot really give any examples of using either, because I have not
used extended properties myself very much. Or rather not at all.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Design Table; Columns/Description get with SP???

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 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)" 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 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 -
>