查询sqlserver数据库表信息
查询sqlserver数据库表信息。
源链接：https://blog.csdn.net/zxy0425/article/details/64441875
1 SELECT 2 表名 = Case When A.colorder=1 Then D.name Else '' End, 3 表说明 = Case When A.colorder=1 Then isnull(F.value,'') Else '' End, 4 字段序号 = A.colorder, 5 字段名 = A.name, 6 字段说明 = isnull(G.[value],''), 7 标识 = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End, 8 主键 = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in ( 9 SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end, 10 类型 = B.name, 11 占用字节数 = A.Length, 12 长度 = COLUMNPROPERTY(A.id,A.name,'PRECISION'), 13 小数位数 = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0), 14 允许空 = Case When A.isnullable=1 Then '√'Else '' End, 15 默认值 = isnull(E.Text,'') 16 FROM 17 syscolumns A 18 Left Join 19 systypes B 20 On 21 A.xusertype=B.xusertype 22 Inner Join 23 sysobjects D 24 On 25 A.id=D.id and D.xtype='U' and D.name<>'dtproperties' 26 Left Join 27 syscomments E 28 on 29 A.cdefault=E.id 30 Left Join 31 sys.extended_properties G 32 on 33 A.id=G.major_id and A.colid=G.minor_id 34 Left Join 35 36 sys.extended_properties F 37 On 38 D.id=F.major_id and F.minor_id=0 39 --where d.name='OrderInfo' --如果只查询指定表,加上此条件 40 Order By 41 A.id,A.colorder
