常用数据库提取表结构语句(oracle,mysql,sqlserver)

oracle

SELECT
	a.OWNER AS SCHEMA_NAME,
	a.TABLE_NAME,
	a.NUM_ROWS AS TABLE_ROWS,
	b.COMMENTS AS TABLE_COMMENT,
	c.COLUMN_NAME,
	c.COMMENTS AS COLUMN_COMMENT,
	d.data_type 
FROM
	ALL_TABLES a
	JOIN ALL_TAB_COMMENTS b ON a.OWNER = b.OWNER 
	AND a.TABLE_NAME = b.TABLE_NAME
	JOIN ALL_TAB_COLUMNS d ON a.TABLE_NAME = d.TABLE_NAME 
	AND a.OWNER = d.
	OWNER JOIN ALL_COL_COMMENTS c ON d.TABLE_NAME = c.TABLE_NAME 
	AND d.OWNER = c.OWNER 
	AND d.COLUMN_NAME = c.COLUMN_NAME

mysql

SELECT
	a.table_schema,
	a.table_name,
	b.TABLE_ROWS,
	b.TABLE_COMMENT,
	a.column_name,
	a.column_comment,
	a.data_type 
FROM
	information_schema.COLUMNS a
	JOIN information_schema.TABLES b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA 
	AND a.TABLE_NAME = b.TABLE_NAME

sqlserver

    SELECT s.Name AS schema_name, t.NAME AS table_name, cast(p.rows as varchar) AS table_rows, cast(e2.value as varchar(200)) AS table_comment, c.Name AS column_name, cast(e.value as varchar(200)) AS column_comment, dt.name 
    FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id INNER JOIN sys.columns c ON t.object_id = c.object_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id LEFT OUTER JOIN sys.extended_properties e ON e.major_id = c.object_id AND e.minor_id = c.column_id LEFT OUTER JOIN sys.extended_properties e2 ON e2.major_id = t.object_id and e2.minor_id = 0 INNER JOIN (select row_number() over (partition by object_id order by index_id) as cnt, index_id,object_id,rows from sys.partitions)p on i.object_id = p.object_id AND i.index_id = p.index_id AND p.cnt = 1 left outer join sys.types dt on c.user_type_id = dt.user_type_id