ORACLE,SQLSERVER, MYSQL,PGSQL获取表信息的语句

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();