sql 查询表各列的属性

最新推荐文章于 2024-05-21 16:13:21 发布

zziamalei

最新推荐文章于 2024-05-21 16:13:21 发布

阅读量1.7k

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/zziamalei/article/details/17788889

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

查询表中各个字段属性

方法一

select syscolumns.name, systypes.name, syscolumns.length ,syscolumns.isnullable 
from syscolumns  left join systypes on syscolumns.xtype=systypes.xtype  
where id=(select id from sysobjects where name='data_analyser ')

方法二

SELECT  
     C.name as [字段名],T.name as [字段类型]  
     ,convert(bit,C.IsNullable)  as [可否为空]  
     ,convert(bit,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in (  
         SELECT name FROM sysindexes WHERE indid in(  
             SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end)   
                 as [是否主键]  
     ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长]  
     ,C.Length as [占用字节]   
     ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as [长度]  
     ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]  
     ,ISNULL(CM.text,'') as [默认值]  
     ,isnull(ETP.value,'') AS [字段描述]  
     --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]   
FROM syscolumns C  
INNER JOIN systypes T ON C.xusertype = T.xusertype   
left JOIN sys.extended_properties ETP   ON  ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description'   
left join syscomments CM on C.cdefault=CM.id  
WHERE C.id = object_id('mytableName')

方法二对应的英文

SELECT   C.name as FieldName,T.name as TypeName  ,C.IsNullable 
,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end   as IsPrimary   
,COLUMNPROPERTY(c.id,c.name,'IsIdentity') as IsIdentity  
 ,C.Length as ByteLength ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as StringLength  
 ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as DotPrecision   ,ISNULL(CM.text,'') as DefaultValue  
,isnull(ETP.value,'') AS [Description]  
FROM syscolumns C  
INNER JOIN systypes T ON C.xusertype = T.xusertype   
left JOIN sys.extended_properties ETP   ON  ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description'   
left join syscomments CM on C.cdefault=CM.id  
WHERE C.id = object_id('mytableName')