导出MySQL和SQLServer表结构

项目场景：

导出MySQL和SQLServer表结构

---

解决方案：

MySQL

SELECT
	TABLE_NAME 表名,
	ORDINAL_POSITION 序号,
	COLUMN_NAME 字段名称,
	COLUMN_COMMENT 注释,
    IF( IS_NULLABLE = 'NO', '是', '' ) AS '必填', --  IS_NULLABLE = 'NO' 时，为必填，必填返回“是”，非必填返回空
	COLUMN_TYPE 数据类型,
    IF( column_key='PRI','是','' ) AS '主键唯一' -- column_key='PRI' 时，为主键唯一索引，是返回“是”，否返回空
	-- COLUMN_DEFAULT 默认值,
	-- CHARACTER_MAXIMUM_LENGTH 字符串最大长度【以字符为单位】,
	-- CHARACTER_OCTET_LENGTH 字符串最大长度【以字节为单位】,
	-- CHARACTER_SET_NAME 字符串字符集名称,
	-- COLLATION_NAME 字符串归类名称,
	-- NUMERIC_PRECISION 数字精度,
	-- NUMERIC_SCALE 数字刻度,
	-- DATETIME_PRECISION 时间分数秒精度
 
FROM
	INFORMATION_SCHEMA.COLUMNS 
	WHERE-- performance_schema 为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
	table_schema = 'performance_schema' 
	
-- AND
-- events_errors_summary_by_account_by_error 为表名，到时候换成你要导出的表的名称
-- 如果不写的话，默认会查询出该数据库中所有表的表结构；这里如果指定表名，则可以导出单独一个表的表结构
-- table_name = 'events_errors_summary_by_thread_by_error'
	
ORDER BY
	TABLE_NAME,
	ORDINAL_POSITION

SQLServer

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
     syscolumns A
 Left Join
     systypes B
 On
     A.xusertype=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
 Left Join
 
 sys.extended_properties F
 On
     D.id=F.major_id and F.minor_id=0
     --where d.name='OrderInfo'    --如果只查询指定表,加上此条件
 Order By
     A.id,A.colorder