sql2005 meta data

注意：

  在SQL   Server2000下sysproperties表中的type=3表示当前的对象是"表",type=4表示是"字段"  
  在SQL   Server2005下sys.extended_properties表中的minor_id=0表示当前的对象是"表",minor_id>0表示是"字段"

 

示例1：显示表名，列名，数据类型，长度，和列描述

 

SELECT  sys.sysobjects.name AS TableName, sys.syscolumns.name AS ColumnName, sys.systypes.name AS Type, sys.syscolumns.length,
                (SELECT  Description
                FROM    dbo.FunColumnDescription(sys.sysobjects.name) AS FunColumnDescription_1
                WHERE  (columnname COLLATE Chinese_PRC_CI_AS = sys.syscolumns.name)) AS Description
FROM    sys.sysobjects INNER JOIN
            sys.syscolumns ON sys.sysobjects.id = sys.syscolumns.id AND sys.sysobjects.name <> 'sysdiagrams' INNER JOIN
            sys.systypes ON sys.syscolumns.xtype = sys.systypes.xtype AND sys.sysobjects.xtype = 'U' AND sys.systypes.name <> 'sysname'
ORDER BY TableName, sys.syscolumns.colid


CREATE FUNCTION FunColumnDescription
(@tablename AS  VARCHAR(255))
RETURNS TABLE
AS 
RETURN(
    SELECT @tablename as TableName,objname as columnname,[value] as [Description]
    FROM fn_listextendedproperty 
    (NULL,'user','dbo','table',@tablename,'column',default)
    )

示例2：使用于SQL2000的数据表元数据提取示例

sql = "SELECT ColumnName = a.name, Type = b.name,Length3 =COLUMNPROPERTY(a.id,a.name,'PRECISION'), IsKey = case when exists (SELECT 1 FROM sysobjects where xtype='PK' and name in (  SELECT name FROM sysindexes WHERE indid in(   SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid  ))) " +
                        "then '1' else '0' end,Length1 = a.length, Length2 = isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),IsIdentity = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')  = 1 THEN '1' ELSE '0' END FROM syscolumns a LEFT JOIN " +
                        " systypes b ON a.xtype = b.xusertype INNER JOIN  sysobjects d ON a.id = d .id AND d .xtype = 'U' AND d .name <> 'dtproperties' LEFT " +
                        " JOIN  syscomments e ON a.cdefault = e.id LEFT JOIN  sysproperties g ON a.id = g.id AND a.colid = g.smallid " +
                        " WHERE d .name = '" + tableName + "' ORDER BY a.id, a.colorder";

 

 

示例3：使用SQL2005的数据包元数据提取示例

sql = "SELECT ColumnName = a.name, Type = b.name,Length3 =COLUMNPROPERTY(a.id,a.name,'PRECISION'), IsKey = case when exists (SELECT 1 FROM sysobjects where xtype='PK' and name in (  SELECT name FROM sysindexes WHERE indid in(   SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid  ))) "+
                            "then '1' else '0' end,Length1 = a.length, Length2 = isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),IsIdentity = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')  = 1 THEN '1' ELSE '0' END FROM syscolumns a LEFT JOIN " +
                            " systypes b ON a.xtype = b.xusertype INNER JOIN  sysobjects d ON a.id = d .id AND d .xtype = 'U' AND d .name <> 'dtproperties' LEFT " +
                            " JOIN  syscomments e ON a.cdefault = e.id LEFT JOIN  sys.extended_properties g ON a.id = g.minor_id AND a.colid = g.minor_id " +
                            " WHERE d .name = '" + tableName + "' ORDER BY a.id, a.colorder";

 

参考文献：

http://rabbitlzx.blogbus.com/logs/5339320.html