x

How to get sys.extended_properties (sys.columns.name, value)

I need to get the column name and value (SQL 2000 "MS_Description"). But it is only returning columns that are NOT NULL. There are a number of column such as ID that do not have a description but I need all column names in the table regardless of a description being NULL.

I am using the following SQL

 SELECT c.name AS DBFIELD,Case when value IS  Null then  ' '
                             Else Convert(varchar(255), value)
                             End As [Description]
 FROM sys.extended_properties AS ep
 JOIN sys.tables AS t ON ep.major_id = t.object_id 
 JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
 WHERE class = 1  AND t.name = N'USERDETAILS1'
 ORDER BY c.column_id

This outputs:

 QF1    Firstname
 QF2    Middle name(s)
 QF3    Surname
 QF4    National Insurance No. 
 QF5    Work Address Line 1
 QF6    Work Address Line 2
 QF7    Work Address Line 3
 QF8    Town or City
 QF9    County

What I need is it to output

 QF0     
 QF1 Firstname
 QF2    Middle name(s)
 QF3    Surname
 QF4    National Insurance No. 
 QF5    Work Address Line 1
 QF6    Work Address Line 2
 QF7    Work Address Line 3
 QF8    Town or City
 QF9    County

Please can someone help

more ▼

asked Jul 16, 2014 at 01:39 PM in Default

avatar image

TheBat
73 8 8 13

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Try

 SELECT 
     c.name AS DBFIELD,
     case 
         when value IS  Null then  ' '
         else Convert(varchar(255), value)
     End As [Description]    
 FROM 
 sys.tables AS t
 join sys.columns AS c ON t.object_id = c.object_id
 left join sys.extended_properties AS ep on ep.major_id = t.object_id and ep.minor_id = c.column_id and class=1
 where
     t.name = N'USERDETAILS1'
 ORDER BY c.column_id
more ▼

answered Jul 16, 2014 at 01:50 PM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

Thank you its perfect, I did some different joins but did not seem to get the result. Thank you so much Kev

Jul 16, 2014 at 02:01 PM TheBat
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x780
x2

asked: Jul 16, 2014 at 01:39 PM

Seen: 1372 times

Last Updated: Jul 16, 2014 at 02:01 PM

Copyright 2017 Redgate Software. Privacy Policy