从 MS Access 中的 SQL Server 链接表检索列描述

Question

我从 MS Access 前端链接到 SQL Server 中的表。当我在 Access 中创建链接表时，我想提出 SQL Server 中某些表的列描述。有没有办法以编程方式获取列描述？

（我知道如何将描述附加到链接表中，我只需要帮助获取后端的描述。）

Answer 1

尝试类似的方法：

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