sqlsever表信息查询

最新推荐文章于 2022-01-25 13:50:23 发布

最新推荐文章于 2022-01-25 13:50:23 发布 · 81 阅读

本文详细介绍了如何使用SQL语句查询数据库表的结构信息，包括表名、字段名、字段类型、字段长度、是否自增、是否为主键等关键属性。

    SELECT  
    TableName=d.name,--表名称  
    FieldNo=a.colorder,--字段编号  
    FieldName=a.name,--字段名称  
    IsIdentity=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then 'true'else 'false' end,--是否自增  
    PrimaryKey=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 'true' else 'false' end,--是否主键  
    FieldType=b.name,--字段类型  
    FieldLength=a.length,--字段长度  
    FieldSize=COLUMNPROPERTY(a.id,a.name,'PRECISION'),--字段大小  
    FieldScale=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),--小数位数  
    FieldAllowNull=case when a.isnullable=1 then 'true' else 'false' end,--是否为空  
    DefaultValue=isnull(e.text,''),--默认值  
    FieldRemark=isnull(g.[value],'')--字段说明  
    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.major_id and a.colid=g.minor_id--SQL2005  
    --left join sysproperties g on a.id=g.id and a.colid=g.smallid  
    where d.name='TableName'  
    order by a.id,a.colorder

转至:http://blog.youkuaiyun.com/zl2121016/article/details/6927898