SqlServer 、MySQL查询库中表明 字段信息

SqlServer

-- sqlserver 查询库中表数量

SELECT count(1) from sysobjects where xtype = 'u'

-- sqlserver 查询表中的字段信息
select a.name tabelname,

b.name fields,

case c.name when 'numeric' then 'numeric(' + convert(varchar,b.length) + '，' + convert(varchar,b.xscale) + ')'

when 'char' then 'char(' + convert(varchar,b.length) + ')'

when 'varchar' then 'varchar(' + convert(varchar,b.length) + ')'

else c.name END AS fieldType

from sysobjects a,syscolumns b,systypes c where a.id=b.id

and a.name='表名' and a.xtype='U'

and b.xtype=c.xtype

-- 方式2

DECLARE @TableName VARCHAR ( 50 ) 
SET @TableName = 'T_jcxx' SELECT
col.name AS ColumnName,
col.isnullable AS IsNullable,
col.length AS DataLength,
tp.name AS DataType,
ep.value AS Descript,
(
    SELECT COUNT
        ( * ) 
    FROM
        sys.sysobjects 
    WHERE
        parent_obj = obj.id 
        AND name = (
        SELECT TOP
            1 name 
        FROM
            sys.sysindexes ind
            INNER JOIN sys.sysindexkeys indkey ON ind.indid= indkey.indid 
            AND indkey.colid= col.colid 
            AND indkey.id= obj.id 
        WHERE
            ind.id= obj.id 
            AND ind.name LIKE 'PK_%' 
        ) 
    ) AS IsPrimaryKey 
FROM
    sys.sysobjects obj
    INNER JOIN sys.syscolumns col ON obj.id = col.id
    LEFT JOIN sys.systypes tp ON col.xtype= tp.xusertype
    LEFT JOIN sys.extended_properties ep ON ep.major_id= obj.id 
    AND ep.minor_id= col.colid 
    AND ep.name = 'MS_Description' 
WHERE
    obj.name =@TableName

 

MySQL

/*MySql查询指定数据库表数量*/
SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES

WHERE table_schema = 'finance_demo' GROUP BY table_schema;

/*MySql查询指定数据库表名称及注释*/
select table_name,table_comment from information_schema.tables where table_schema='finance_demo'

/*MySql查询指定数据库表中的字段信息*/
select COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT from information_schema.COLUMNS where
table_name = 'dm_settle_platform_details' and table_schema = 'finance_demo';