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 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply