[kristina1]

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

[modi123_1]

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

[e_i_pi]

@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.