Description tab in Table Design

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Description tab in Table Design

Author

Topic

aliG
Starting Member

4 Posts

Posted - 2005-03-09 : 12:19:11

Hi all / anyone.
please help.
Is it at all possible to do a select query that will return
the Description of a column that was entered during design.
(when you are in Table design there is a Description field at the bottom). I need to retrieve those values if any description was entered.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-09 : 12:41:12

The descriptions are stored in the dtproperties table.

aliG
Starting Member

4 Posts

Posted - 2005-03-09 : 12:52:07

thx for the feedback,
I did a select * on dtProperties,
but the table is empty and I have added
a description. Does it include user defined tables?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-09 : 12:53:22

Yes.

aliG
Starting Member

4 Posts

Posted - 2005-03-09 : 12:55:35

ok, then why is it empty?
I have checked the table
on different DBs and all empty.

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-09 : 12:56:41

quote:
Originally posted by robvolk

Yes.

You gotta like the fact that Rob is always so verbose

Brett

8-)

EDIT: You sure Rob?


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (
	col1 varchar(255)
)
GO

exec sp_addextendedproperty N'MS_Description', N'This is a table', N'user', N'dbo', N'table', N'myTable99'
GO

exec sp_addextendedproperty N'MS_Description', N'This is a column', N'user', N'dbo', N'table', N'myTable99', N'column', N'col1'
GO

SELECT * FROM dtproperties
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

EDIT2: I thought this would work, but I still get no rows returned

SELECT *
FROM ::fn_listextendedproperty('MS_Description', NULL, NULL, NULL, NULL, NULL, NULL)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-09 : 13:06:07

OK, I'm an IDIOT!!!! They're actually stored in the sysproperties table:

SELECT object_name(id) as table_name, value as Description FROM sysproperties WHERE name='MS_Description'

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-09 : 13:12:39

This does the tables descriptions

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

Brett

8-)

aliG
Starting Member

4 Posts

Posted - 2005-03-11 : 03:32:32

thx Brett
that is the solution I am looking for.
cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources