2 Replies - 4709 Views - Last Post: 11 August 2012 - 07:05 PM Rate Topic: -----

#1 kristina1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 30-July 12

getting column descriptions sql server 2008

Posted 11 August 2012 - 07:57 AM

hi,

i have created 2 tables in sql server 2008 (authors and books) and added a description for each of their columns. i wrote a query to select the names of columns , their data type and the description for each of the tables separated. here is my query:

SELECT c.COLUMN_NAME, c.DATA_TYPE, cd.value FROM INFORMATION_SCHEMA.COLUMNS AS c INNER JOIN sys.extended_properties AS cd ON c.ORDINAL_POSITION = cd.minor_id WHERE (c.TABLE_NAME = 'authors') ORDER BY c.ORDINAL_POSITION



when i first had only the 'authors' table, everything worked out just fine, but when i added the second table, this query provides me the descriptions of the 'books' table except those of the authors table, because the condition c.ORDINAL_POSITION = cd.minor_id is wrong, and now i dont know which relationship i should use in order to obtain only the descriptions of authors table.

i didnt find any similar columns between the INFORMATION_SCHEMA.COLUMNS and the sys.extended_properties tables, so i started to think that maybe there is a third table to provide this connection.

if you could help me with some information on how to improve my uery,i would be very grateful.

thank a lot

Is This A Good Question/Topic? 0
  • +

Replies To: getting column descriptions sql server 2008

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9286
  • View blog
  • Posts: 34,803
  • Joined: 12-June 08

Re: getting column descriptions sql server 2008

Posted 11 August 2012 - 08:39 AM

What are you doing messing with the sys.extended_properties?

You should be joining off the second table (description?) on the matching author ids.

I would know more but I don't know how either table is laid out
Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: getting column descriptions sql server 2008

Posted 11 August 2012 - 07:05 PM

@modi123_1, it might be for a DB management system, or for conditional display. I hit the info schema tables in my application in order to mine out what the datatype is, so that I can cater for it when it comes to spitting out HTML or validating user input.

@kristina1, you're matching the wrong columns. sys.extended_properties.minor_id matches against a column ID, not an ordinal position. It's doesn't help that MSDN calls the ordinal position the "column id", but what can you expect from Microsoft.

Try looking into the table sys.columns. I haven't got SSMS in front of me (I use it at work only), but you should be able to relate sys.extended_properties.minor_id to sys.columns.colid.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1