一、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