Retrieve column descriptions from SQL Server-linked table in MS Access

Question

I am linking to tables in SQL Server from an MS Access front-end. There are column descriptions for some of the tables in SQL Server that I would like to bring forward when I create the linked tables in Access. Is there a way to get at the column descriptions programmatically?

(I know how to append the description to the linked tables, I just need help getting at the descriptions in the back end.)

Answer 1

Try something like:

DECLARE @TableName varchar(100)
SELECT @TableName = 'yourtablename'


-- This will determine if we're using version 9 (2005) of SQL Server, and execute code accordingly

IF CAST(REPLACE(SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar),1,2), '.','') as int) >= 9
BEGIN
      -- This is a SQL 2005 machine
      SELECT  
            [Table Name] = OBJECT_NAME(c.object_id), 
            [Column Name] = c.name, 
            [Description] = ex.value  
      FROM  
            sys.columns c  
      LEFT OUTER JOIN  
            sys.extended_properties ex  
      ON  
            ex.major_id = c.object_id 
            AND ex.minor_id = c.column_id  
            AND ex.name = 'MS_Description'  
      WHERE  
            OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0  
            AND OBJECT_NAME(c.object_id) = @TableName
      ORDER  
            BY OBJECT_NAME(c.object_id), c.column_id
END
ELSE
BEGIN
      -- assume this is a SQL 2000
      SELECT 
            [Table Name] = i_s.TABLE_NAME, 
            [Column Name] = i_s.COLUMN_NAME, 
            [Description] = s.value 
      FROM 
            INFORMATION_SCHEMA.COLUMNS i_s 
      LEFT OUTER JOIN 
            sysproperties s 
      ON 
            s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME) 
            AND s.smallid = i_s.ORDINAL_POSITION 
            AND s.name = 'MS_Description' 
      WHERE 
            OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0 
            AND i_s.TABLE_NAME = @TableName
      ORDER BY
            i_s.TABLE_NAME, i_s.ORDINAL_POSITION 
END