用SQL语句查询出所有表的表结构(字段定义,类型,长度等代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】用SQL语句查询出所有表的表结构(字段定义,类型,长度等

作者:彦 / 发布于2016/1/11/ 976

在结果就能看到数据库所有表的结构，点击右上角全选然后右键，选择将结果另存为CSV，然后在复制到Excel里面然后就可以方便查看。


	SELECT
	 表名 = Case When A.colorder=1 Then D.name Else '' End,
	 表说明 = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,
	 字段序号 = A.colorder,
	 字段名 = A.name,
	 字段说明 = isnull(G.[value],''),
	 标识 = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '是'Else '' End,
	 主键 = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (
	 SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '是' else '' end,
	 类型 = B.name,
	 占用字节数 = A.Length,
	 长度 = COLUMNPROPERTY(A.id,A.name,'PRECISION'),
	 小数位数 = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),
	 允许空 = Case When A.isnullable=1 Then '允许'Else '' End,
	 默认值 = isnull(E.Text,'')
	FROM sys.syscolumns AS a LEFT OUTER JOIN
	 sys.systypes AS b ON a.xusertype = b.xusertype INNER JOIN
	 sys.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT OUTER JOIN
	 sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
	 sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN
	 sys.extended_properties AS f ON d.id = f.major_id AND f.minor_id = 0
	 
	
	where d.name='alldocument' --如果只查询指定表,加上此条件
	//where d.name='alldocument' --查询全部表
	ORDER BY a.id, 字段序号
	//ORDER BY d.name asc , a.id, 字段序号 -- d.name asc 按照字母排序表

试试其它关键字

同语言下

可能有用的

彦贡献的其它代码(4)