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

ORACLE

ORACLE,SQLSERVER, MYSQL获取表信息的两条语句
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}
View Code

SQLSERVER 

ORACLE,SQLSERVER, MYSQL获取表信息的两条语句
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
View Code

MYSQL

ORACLE,SQLSERVER, 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 }
View Code

PGSQL

ORACLE,SQLSERVER, MYSQL获取表信息的两条语句
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 ELSEstring   ");
            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();
View Code

 

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

上一篇:springboot整合mybatis+mysql+druid 多个数据源


下一篇:非常精美的网页标志