一、SQL Server

1、获取数据库表信息

DECLARE @TableInfo TABLE ( name VARCHAR(50) , sumrows VARCHAR(11) , reserved VARCHAR(50) , data VARCHAR(50) , index_size VARCHAR(50) , unused VARCHAR(50) , pk VARCHAR(50) )
                            DECLARE @TableName TABLE ( name VARCHAR(50) )
                            DECLARE @name VARCHAR(50)
                            DECLARE @pk VARCHAR(50)
                            INSERT INTO @TableName ( name ) SELECT o.name FROM sysobjects o , sysindexes i WHERE o.id = i.id
                            AND o.Xtype = 'U' AND i.indid < 2 ORDER BY i.rows DESC , o.name
                            WHILE EXISTS ( SELECT 1 FROM @TableName ) BEGIN SELECT TOP 1 @name = name FROM @TableName DELETE @TableName 
                            WHERE name = @name DECLARE @objectid INT SET @objectid = OBJECT_ID(@name) SELECT @pk = COL_NAME(@objectid, colid) 
                            FROM sysobjects AS o 
                            INNER JOIN sysindexes AS i ON i.name = o.name 
                            INNER JOIN sysindexkeys AS k ON k.indid = i.indid WHERE o.xtype = 'PK' 
                            AND parent_obj = @objectid AND k.id = @objectid 
                            INSERT INTO @TableInfo ( name , sumrows , reserved , data , index_size , unused )
                            EXEC sys.sp_spaceused @name UPDATE @TableInfo SET pk = @pk WHERE name = @name END
                            SELECT F.name as name, F.reserved  as reserved, F.data as data, F.index_size as index_size, 
                            RTRIM(F.sumrows) AS sumrows , F.unused as unused, ISNULL(p.tdescription, f.name) AS tdescription , F.pk as pk
                            FROM @TableInfo F LEFT JOIN ( SELECT name = CASE WHEN A.COLORDER = 1 THEN D.NAME ELSE '' END ,
                            tdescription = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, '') ELSE '' END FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE 
                            INNER JOIN SYSOBJECTS D ON A.ID = D.ID AND D.XTYPE = 'U' AND D.NAME <> 'DTPROPERTIES' LEFT JOIN sys.extended_properties F ON D.ID = F.major_id
                            WHERE a.COLORDER = 1 AND F.minor_id = 0 ) P ON F.name = p.name
                             ORDER BY f.name

2、获取表的字段信息

SELECT [f_number] = a.colorder , [f_column] = a.name , [f_datatype] = b.name , [f_length] = COLUMNPROPERTY(a.id, a.name, 'PRECISION') , [f_identity] = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '1' ELSE '' END , [f_key] = CASE WHEN EXISTS ( SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND parent_obj = a.id AND name IN ( SELECT name FROM sysindexes WHERE indid IN ( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid ) ) ) THEN '1' ELSE '' END , [f_isnullable] = CASE WHEN a.isnullable = 1 THEN '1' ELSE '' END , [f_defaults] = ISNULL(e.text, '') , [f_remark] = ISNULL(g.[value], a.name)
FROM syscolumns a LEFT JOIN systypes b ON a.xusertype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
WHERE d.name ='这里放需要查的表名'
ORDER BY a.id , a.colorder

二、My Sql

1、获取数据库表信息

SELECT t1.*,  (select COLUMN_NAME from information_schema.`COLUMNS` where TABLE_SCHEMA=database() and  TABLE_NAME =t1.name and COLUMN_KEY='PRI') pk
from (
SELECT TABLE_NAME name,0 reserved,0 data,0 index_size,TABLE_ROWS sumrows,0 unused, (select IF(LENGTH(TRIM(TABLE_COMMENT))<1,TABLE_NAME,TABLE_COMMENT)) tdescription
from information_schema.`TABLES` where TABLE_SCHEMA =database()
) t1 ORDER BY t1.name

2、获取表的字段信息

select  ORDINAL_POSITION f_number, COLUMN_NAME f_column, DATA_TYPE f_datatype, if(CHARACTER_MAXIMUM_LENGTH is null,if(LOCATE('int',column_type)>0,REPLACE(REPLACE(column_type,'int(',''),')',''),0),CHARACTER_MAXIMUM_LENGTH) f_length, '' f_identity, IF(COLUMN_KEY='PRI','1','') f_key,
IF(IS_NULLABLE='YES','1','') f_isnullable,COLUMN_DEFAULT f_default,case when COLUMN_COMMENT='' then COLUMN_NAME else COLUMN_COMMENT END    f_remark from (
select  *
from information_schema.`COLUMNS` t1 where TABLE_SCHEMA=database() and TABLE_NAME ='这里放需要查的表名') t2 order by f_number

三、Oracle

1、获取数据库表信息

select distinct col.table_name name,
                                        0 reserved,
                                        0 fdata,
                                        0 index_size,
                                        nvl(t.num_rows, 0) sumrows,
                                        0 funused,
                                        tab.comments tdescription,
                                        column_name pk
                            from user_cons_columns col
                            inner join user_constraints con
                            on con.constraint_name = col.constraint_name
                            inner join user_tab_comments tab
                            on tab.table_name = col.table_name
                            inner join user_tables t
                            on t.TABLE_NAME = col.table_name
                            where con.constraint_type not in ('C', 'R') ORDER BY col.table_name 

2、获取表的字段信息

SELECT
                                col.column_id f_number,
                                col.column_name f_column,
                                col.data_type f_datatype,
                                col.data_length f_length,
                                NULL f_identity,
                                CASE uc.constraint_type
                            WHEN 'P' THEN
                                1
                            ELSE
                                NULL
                            END f_key,
                             CASE col.nullable
                            WHEN 'N' THEN
                                0
                            ELSE
                                1
                            END f_isnullable,
                             col.data_default f_defaults,
                             NVL(comm.comments, col.column_name)  AS f_remark
                            FROM
                                user_tab_columns col
                            INNER JOIN user_col_comments comm ON comm.TABLE_NAME = col.TABLE_NAME
                            AND comm.COLUMN_NAME = col.COLUMN_NAME
                            LEFT JOIN user_cons_columns ucc ON ucc.table_name = col.table_name
                            AND ucc.column_name = col.column_name
                            AND ucc.position = 1
                            LEFT JOIN user_constraints uc ON uc.constraint_name = ucc.constraint_name
                            AND uc.constraint_type = 'P'
                            WHERE
                                col.table_name = :tableName
                            ORDER BY
                                col.column_id