ORACLE
SELECT a.TABLE_NAME TABLENAME ,--表名 a.COLUMN_NAME COLUMNNAME,--字段名 b.comments ColumnCNName,--注释 case when A.data_type =‘NUMBER‘ And A.data_precision is not null and A.Data_Scale is not null Then ‘decimal‘ when A.data_type =‘NUMBER‘ And A.data_precision is null and (A.Data_Scale is not null or A.Data_Scale =0) Then ‘int‘ when a.DATA_TYPE =‘VARCHAR2‘ and a.DATA_LENGTH =36 then ‘guid‘ when a.data_type in (‘VARCHAR2‘,‘CLOB‘,‘VARCHAR‘) Then ‘string‘ else lower(A.data_type) end ColumnType, case when a.DATA_TYPE =‘NUMBER‘ and a.data_precision is not null Then a.data_precision else a.DATA_LENGTH End MAXLENGTH, --长度 case when d.column_name=a.COLUMN_NAME then 1 else 0 end IsKey,--主键 CASE WHEN a.COLUMN_NAME IN(‘CreateID‘, ‘ModifyID‘, ‘‘) OR d.column_name=a.COLUMN_NAME THEN 0 ELSE 1 END IsDisplay , 1 IsColumnData, CASE WHEN a.DATA_TYPE =‘DATE‘ THEN 150 WHEN a.COLUMN_NAME IN(‘MODIFIER‘, ‘CREATOR‘) THEN 130 WHEN a.DATA_TYPE =‘NUMBER‘ OR a.COLUMN_NAME IN(‘CREATEID‘, ‘MODIFYID‘, ‘‘) THEN 80 WHEN a.DATA_LENGTH < 110 AND a.DATA_LENGTH > 60 THEN 120 WHEN a.DATA_LENGTH < 200 AND a.DATA_LENGTH >= 110 THEN 180 WHEN a.DATA_LENGTH > 200 THEN 220 ELSE 90 END AS ColumnWidth , 0 OrderNo, case when e.nullable=‘Y‘ Then 1 else 0 end IsNul, CASE WHEN d.column_name=a.COLUMN_NAME THEN 1 ELSE 0 END IsReadDataset, CASE WHEN d.column_name!=a.COLUMN_NAME AND e.nullable=‘N‘ THEN 0 ELSE NULL END EditColNo FROM user_tab_columns a inner join user_col_comments b on a.TABLE_NAME=b.table_name and a.COLUMN_NAME=b.column_name inner join user_constraints c on a.TABLE_NAME=c.table_name and c.constraint_type = ‘P‘ inner join user_cons_columns d on d.constraint_name = c.constraint_name inner join dba_tab_columns e on a.TABLE_NAME=e.table_name and a.COLUMN_NAME=e.column_name WHERE a.TABLE_NAME = ‘{tableName}‘
SQLSERVER
SELECT TableName, LTRIM(RTRIM(ColumnName)) AS ColumnName, ColumnCNName, CASE WHEN ColumnType = ‘uniqueidentifier‘ THEN ‘guid‘ WHEN ColumnType IN(‘smallint‘, ‘INT‘) THEN ‘int‘ WHEN ColumnType = ‘BIGINT‘ THEN ‘long‘ WHEN ColumnType IN(‘CHAR‘, ‘VARCHAR‘, ‘NVARCHAR‘, ‘text‘, ‘xml‘, ‘varbinary‘, ‘image‘) THEN ‘string‘ WHEN ColumnType IN(‘tinyint‘) THEN ‘byte‘ WHEN ColumnType IN(‘bit‘) THEN ‘bool‘ WHEN ColumnType IN(‘time‘, ‘date‘, ‘DATETIME‘, ‘smallDATETIME‘) THEN ‘DateTime‘ WHEN ColumnType IN(‘smallmoney‘, ‘DECIMAL‘, ‘numeric‘, ‘money‘) THEN ‘decimal‘ WHEN ColumnType = ‘float‘ THEN ‘float‘ ELSE ‘string ‘ END ColumnType, [Maxlength], IsKey, CASE WHEN ColumnName IN(‘CreateID‘, ‘ModifyID‘, ‘‘) OR IsKey = 1 THEN 0 ELSE 1 END AS IsDisplay , 1 AS IsColumnData, CASE WHEN ColumnType IN(‘time‘, ‘date‘, ‘DATETIME‘, ‘smallDATETIME‘) THEN 150 WHEN ColumnName IN(‘Modifier‘, ‘Creator‘) THEN 130 WHEN ColumnType IN(‘int‘, ‘bigint‘) OR ColumnName IN(‘CreateID‘, ‘ModifyID‘, ‘‘) THEN 80 WHEN[Maxlength] < 110 AND[Maxlength] > 60 THEN 120 WHEN[Maxlength] < 200 AND[Maxlength] >= 110 THEN 180 WHEN[Maxlength] > 200 THEN 220 ELSE 90 END AS ColumnWidth , 0 AS OrderNo, --CASE WHEN IsKey = 1 OR t.[IsNull]=0 THEN 0 -- ELSE 1 END t.[IsNull] AS [IsNull], CASE WHEN IsKey = 1 THEN 1 ELSE 0 END IsReadDataset, CASE WHEN IsKey!=1 AND t.[IsNull] = 0 THEN 0 ELSE NULL END AS EditColNo FROM (SELECT obj.name AS TableName , col.name AS ColumnName , CONVERT(NVARCHAR(100),ISNULL(ep.[value], ‘‘)) AS ColumnCNName, t.name AS ColumnType , CASE WHEN col.length<1 THEN 0 ELSE col.length END AS[Maxlength], CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = ‘PK‘ WHERE sc.id = col.id AND sc.colid = col.colid) THEN 1 ELSE 0 END AS IsKey , CASE WHEN col.isnullable = 1 THEN 1 ELSE 0 END AS[IsNull], col.colorder FROM dbo.syscolumns col LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype INNER JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype IN ( ‘U‘,‘V‘) -- AND obj.status >= 01 LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = ‘MS_Description‘ LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id AND epTwo.minor_id = 0 AND epTwo.name = ‘MS_Description‘ WHERE obj.name = @tableName--表名 ) AS t ORDER BY t.colorder
MYSQL
SELECT DISTINCT Column_Name AS ColumnName, ‘{ tableName}‘ as tableName, Column_Comment AS ColumnCnName, CASE WHEN data_type IN( ‘BIT‘, ‘BOOL‘, ‘bit‘, ‘bool‘) THEN ‘bool‘ WHEN data_type in(‘smallint‘,‘SMALLINT‘) THEN ‘short‘ WHEN data_type in(‘tinyint‘,‘TINYINT‘) THEN ‘sbyte‘ WHEN data_type IN(‘MEDIUMINT‘,‘mediumint‘, ‘int‘,‘INT‘,‘year‘, ‘Year‘) THEN ‘int‘ WHEN data_type in ( ‘BIGINT‘,‘bigint‘) THEN ‘bigint‘ WHEN data_type IN(‘FLOAT‘, ‘DOUBLE‘, ‘DECIMAL‘,‘float‘, ‘double‘, ‘decimal‘) THEN ‘decimal‘ WHEN data_type IN(‘CHAR‘, ‘VARCHAR‘, ‘TINY TEXT‘, ‘TEXT‘, ‘MEDIUMTEXT‘, ‘LONGTEXT‘, ‘TINYBLOB‘, ‘BLOB‘, ‘MEDIUMBLOB‘, ‘LONGBLOB‘, ‘Time‘,‘char‘, ‘varchar‘, ‘tiny text‘, ‘text‘, ‘mediumtext‘, ‘longtext‘, ‘tinyblob‘, ‘blob‘, ‘mediumblob‘, ‘longblob‘, ‘time‘) THEN ‘string‘ WHEN data_type IN(‘Date‘, ‘DateTime‘, ‘TimeStamp‘,‘date‘, ‘datetime‘, ‘timestamp‘) THEN ‘DateTime‘ ELSE ‘string‘ END AS ColumnType, case WHEN CHARACTER_MAXIMUM_LENGTH>8000 THEN 0 ELSE CHARACTER_MAXIMUM_LENGTH end AS Maxlength, CASE WHEN COLUMN_KEY <> ‘‘ THEN 1 ELSE 0 END AS IsKey, CASE WHEN Column_Name IN( ‘CreateID‘, ‘ModifyID‘, ‘‘ ) OR COLUMN_KEY<> ‘‘ THEN 0 ELSE 1 END AS IsDisplay, 1 AS IsColumnData, 120 AS ColumnWidth, 0 AS OrderNo, CASE WHEN IS_NULLABLE = ‘NO‘ THEN 0 ELSE 1 END AS IsNull, CASE WHEN COLUMN_KEY <> ‘‘ THEN 1 ELSE 0 END AS IsReadDataset FROM information_schema.COLUMNS WHERE table_name = {table_name }
PGSQL
stringBuilder.Append("SELECT "); stringBuilder.Append(" MM.\"TableName\", "); stringBuilder.Append(" MM.\"ColumnName\", "); stringBuilder.Append(" MM.\"ColumnCNName\", "); stringBuilder.Append(" MM.\"ColumnType\", "); stringBuilder.Append(" MM.\"Maxlength\", "); stringBuilder.Append(" MM.\"IsKey\", "); stringBuilder.Append(" MM.\"IsDisplay\", "); stringBuilder.Append(" MM.\"IsColumnData\", "); stringBuilder.Append("CASE "); stringBuilder.Append(" "); stringBuilder.Append(" WHEN MM.\"ColumnType\" = ‘DateTime‘ THEN "); stringBuilder.Append(" 150 "); stringBuilder.Append(" WHEN MM.\"ColumnType\" = ‘int‘ THEN "); stringBuilder.Append(" 80 "); stringBuilder.Append(" WHEN MM.\"Maxlength\" < 110 "); stringBuilder.Append(" AND MM.\"Maxlength\" > 60 THEN "); stringBuilder.Append(" 120 "); stringBuilder.Append(" WHEN MM.\"Maxlength\" < 200 "); stringBuilder.Append(" AND MM.\"Maxlength\" >= 110 THEN "); stringBuilder.Append(" 180 "); stringBuilder.Append(" WHEN MM.\"Maxlength\" > 200 THEN "); stringBuilder.Append(" 220 ELSE 90 "); stringBuilder.Append(" END AS \"ColumnWidth\", "); stringBuilder.Append(" MM.\"OrderNo\", "); stringBuilder.Append(" case WHEN MM.\"IsKey\"=1 or \"lower\"(MM.\"IsNull\")=‘no‘ then 0 else 1 end as \"IsNull\" , "); stringBuilder.Append(" MM.\"IsReadDataset\", "); stringBuilder.Append(" MM.\"EditColNo\" "); stringBuilder.Append(" FROM "); stringBuilder.Append(" ( "); stringBuilder.Append(" SELECT "); stringBuilder.Append(" col.TABLE_NAME AS \"TableName\", "); stringBuilder.Append(" col.COLUMN_NAME AS \"ColumnName\", "); stringBuilder.Append(" attr.description AS \"ColumnCNName\", "); stringBuilder.Append(" CASE "); stringBuilder.Append(" "); stringBuilder.Append(" WHEN col.udt_name = ‘uuid‘ THEN "); stringBuilder.Append(" ‘guid‘ "); stringBuilder.Append(" WHEN col.udt_name IN ( ‘int2‘) THEN "); stringBuilder.Append(" ‘short‘ "); stringBuilder.Append(" WHEN col.udt_name IN ( ‘int4‘ ) THEN "); stringBuilder.Append(" ‘int‘ "); stringBuilder.Append(" WHEN col.udt_name = ‘int8‘ THEN "); stringBuilder.Append(" ‘long‘ "); stringBuilder.Append(" WHEN col.udt_name = ‘BIGINT‘ THEN "); stringBuilder.Append(" ‘long‘ "); stringBuilder.Append(" WHEN col.udt_name IN ( ‘char‘, ‘varchar‘, ‘text‘, ‘xml‘, ‘bytea‘ ) THEN "); stringBuilder.Append(" ‘string‘ "); stringBuilder.Append(" WHEN col.udt_name IN ( ‘bool‘ ) THEN "); stringBuilder.Append(" ‘bool‘ "); stringBuilder.Append(" WHEN col.udt_name IN ( ‘date‘,‘timestamp‘ ) THEN "); stringBuilder.Append(" ‘DateTime‘ "); stringBuilder.Append(" WHEN col.udt_name IN ( ‘decimal‘, ‘money‘,‘numeric‘ ) THEN "); stringBuilder.Append(" ‘decimal‘ "); stringBuilder.Append(" WHEN col.udt_name IN ( ‘float4‘, ‘float8‘ ) THEN "); stringBuilder.Append(" ‘float‘ ELSE‘string ‘ "); stringBuilder.Append(" END \"ColumnType\", "); stringBuilder.Append("CASE "); stringBuilder.Append(" "); stringBuilder.Append(" WHEN col.udt_name = ‘varchar‘ THEN "); stringBuilder.Append(" col.character_maximum_length "); stringBuilder.Append(" WHEN col.udt_name IN ( ‘int2‘, ‘int4‘, ‘int8‘, ‘float4‘, ‘float8‘ ) THEN "); stringBuilder.Append(" col.numeric_precision ELSE 1024 "); stringBuilder.Append(" END \"Maxlength\", "); stringBuilder.Append("CASE "); stringBuilder.Append(" "); stringBuilder.Append(" WHEN keyTable.IsKey = 1 THEN "); stringBuilder.Append(" 1 ELSE 0 "); stringBuilder.Append(" END \"IsKey\", "); stringBuilder.Append("CASE "); stringBuilder.Append(" "); stringBuilder.Append(" WHEN keyTable.IsKey = 1 THEN "); stringBuilder.Append(" 0 ELSE 1 "); stringBuilder.Append(" END \"IsDisplay\", "); stringBuilder.Append(" 1 AS \"IsColumnData\", "); stringBuilder.Append(" 0 AS \"OrderNo\", "); stringBuilder.Append(" col.is_nullable AS \"IsNull\", "); stringBuilder.Append("CASE "); stringBuilder.Append(" "); stringBuilder.Append(" WHEN keyTable.IsKey = 1 THEN "); stringBuilder.Append(" 1 ELSE 0 "); stringBuilder.Append(" END \"IsReadDataset\", "); stringBuilder.Append("CASE "); stringBuilder.Append(" "); stringBuilder.Append(" WHEN keyTable.IsKey IS NULL "); stringBuilder.Append(" AND col.is_nullable = ‘NO‘ THEN "); stringBuilder.Append(" 0 ELSE NULL "); stringBuilder.Append(" END AS \"EditColNo\" "); stringBuilder.Append("FROM "); stringBuilder.Append(" information_schema.COLUMNS col "); stringBuilder.Append(" LEFT JOIN ( "); stringBuilder.Append(" SELECT col_description(a.attrelid,a.attnum) as description,a.attname as name "); stringBuilder.Append("FROM pg_class as c,pg_attribute as a "); stringBuilder.Append("where \"lower\"(c.relname) = \"lower\"(@tableName) and a.attrelid = c.oid and a.attnum>0 "); stringBuilder.Append(" ) as attr on attr.name=col.COLUMN_NAME "); stringBuilder.Append(" LEFT JOIN ( "); stringBuilder.Append(" SELECT "); stringBuilder.Append(" pg_attribute.attname AS colname, "); stringBuilder.Append(" 1 AS IsKey "); stringBuilder.Append(" FROM "); stringBuilder.Append(" pg_constraint "); stringBuilder.Append(" INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid "); stringBuilder.Append(" INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid "); stringBuilder.Append(" AND pg_attribute.attnum = pg_constraint.conkey [1] "); stringBuilder.Append(" WHERE "); stringBuilder.Append(" \"lower\" ( pg_class.relname ) = \"lower\" ( @tableName ) "); stringBuilder.Append(" AND pg_constraint.contype = ‘p‘ "); stringBuilder.Append(" ) keyTable ON col.COLUMN_NAME = keyTable.colname "); stringBuilder.Append("WHERE "); stringBuilder.Append(" \"lower\" ( TABLE_NAME ) = \"lower\" ( @tableName ) "); stringBuilder.Append("ORDER BY "); stringBuilder.Append(" ordinal_position "); stringBuilder.Append(" ) MM; "); return stringBuilder.ToString();