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