July 8, 2009 at 2:08 pm
Here are a couple of queries you can use to retrieve the descriptions.
First SQL 2000
SELECT
sysobjects.name AS [Table Name],
syscolumns.name AS [Column Name],
sysproperties.value AS [Description]
FROM sysproperties
LEFT OUTER JOIN sysobjects ON sysproperties.id = sysobjects.id
LEFT OUTER JOIN syscolumns ON sysproperties.id = syscolumns.id AND sysproperties.smallid = syscolumns.colid
WHERE sysproperties.name = 'MS_Description'
ORDER BY sysobjects.name, syscolumns.name
Next SQL 2005
SELECT
sys.objects.name AS [Table Name],
sys.columns.name AS [Column Name],
sys.extended_properties.value AS [Description]
FROM sys.extended_properties
LEFT OUTER JOIN sys.objects ON sys.extended_properties.major_id = sys.objects.object_id
LEFT OUTER JOIN sys.columns ON sys.extended_properties.major_id = sys.columns.object_id AND sys.extended_properties.minor_id = sys.columns.column_id
ORDER BY sys.objects.name, sys.columns.name
Yes, I know. They only return columns (as rows) that have descriptions.
ATBCharles Kincaid
July 8, 2009 at 2:10 pm
Here are a couple of queries you can use to retrieve the descriptions.
First SQL 2000
SELECT
sysobjects.name AS [Table Name],
syscolumns.name AS [Column Name],
sysproperties.value AS [Description]
FROM sysproperties
LEFT OUTER JOIN sysobjects ON sysproperties.id = sysobjects.id
LEFT OUTER JOIN syscolumns ON sysproperties.id = syscolumns.id AND sysproperties.smallid = syscolumns.colid
WHERE sysproperties.name = 'MS_Description'
ORDER BY sysobjects.name, syscolumns.name
Next SQL 2005
SELECT
sys.objects.name AS [Table Name],
sys.columns.name AS [Column Name],
sys.extended_properties.value AS [Description]
FROM sys.extended_properties
LEFT OUTER JOIN sys.objects ON sys.extended_properties.major_id = sys.objects.object_id
LEFT OUTER JOIN sys.columns ON sys.extended_properties.major_id = sys.columns.object_id AND sys.extended_properties.minor_id = sys.columns.column_id
ORDER BY sys.objects.name, sys.columns.name
Yes, I know. They only return columns (as rows) that have descriptions.
ATBCharles Kincaid
July 24, 2009 at 7:58 am
select that return all tables with column description
SELECT
sys.objects.name AS [Table Name]
,sys.columns.name AS [Column Name]
,sys.extended_properties.value AS [Description]
FROM
sys.objects LEFT JOIN
sys.columns
ON sys.objects.object_id = sys.columns.object_id LEFT JOIN
sys.extended_properties
ON sys.extended_properties.major_id = sys.columns.object_idAND
sys.extended_properties.minor_id = sys.columns.column_id
WHERE sys.objects.type = 'U'
ORDER BY sys.objects.name, sys.columns.name
July 24, 2009 at 3:07 pm
I like to replace the NULLS with blanks. I also found a way to have it return just my tables.
SELECT
sys.tables.name AS [Table Name]
,sys.columns.name AS [Column Name]
,COALESCE(sys.extended_properties.value,'') AS [Description]
FROM sys.tables
LEFT JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id
AND sys.tables.name 'sysdiagrams'
LEFT JOIN sys.extended_properties ON sys.extended_properties.major_id = sys.columns.object_id
AND sys.extended_properties.minor_id = sys.columns.column_id
ORDER BY sys.tables.name, sys.columns.column_id
I'm using sys.tables rather than sys.objects as it does most of the filtering for me.
ATBCharles Kincaid
Viewing 4 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy