正文
1. Oracle数据库:
SELECT
x.table_name AS 表名,
x.表行数,
x.表列数,
y.表大小 AS 表大小单位MB
FROM
(
SELECT
b.table_name,
a.num_rows AS 表行数,
b.count1 AS 表列数
FROM
user_tables a
INNER JOIN ( SELECT table_name, count( column_name ) AS count1 FROM user_tab_columns GROUP BY table_name ) b ON a.table_name = b.table_name
ORDER BY
b.table_name
) x LEFT outer
JOIN (
SELECT
sum( tablesize ) AS 表大小,
tablename
FROM
(
SELECT
sum( C.bytes ) / 1024 / 1024 AS tablesize,
C.table_name AS tablename
FROM
( SELECT A.table_name, B.bytes FROM user_lobs A, user_extents B WHERE A.segment_name = B.segment_name ) C
GROUP BY
C.table_name UNION ALL
SELECT
sum( bytes ) / 1024 / 1024 AS tablesize,
segment_name AS tablename
FROM
user_extents
WHERE
segment_type = 'TABLE'
GROUP BY
segment_name
)
GROUP BY
tablename
ORDER BY
1 DESC
) y ON x.table_name = y.tablename
ORDER BY
y.表大小 desc
2. 神通数据库:
CREATE TABLE table_num (
table_name VARCHAR2(255),
table_num NUMBER
);
CREATE OR REPLACE PROCEDURE insert_table_num IS
BEGIN
FOR t IN (SELECT table_name FROM user_tables) LOOP
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO table_num (table_name, table_num)
SELECT ''' || t.table_name || ''', COUNT(*) FROM ' || t.table_name;
EXCEPTION
when others then
null ;
END;
END LOOP;
END;
exec insert_table_num;
SELECT * FROM table_num ORDER BY table_num DESC
3. 达梦数据库
SELECT T.TABLESPACE_NAME,
T.OWNER,
T.TABLE_NAME,
TABLE_ROWCOUNT(T.OWNER, T.TABLE_NAME) AS TABLE_ROWCOUNT
FROM DBA_TABLES T
WHERE T.OWNER IN ('YourSchemas_name****') order by TABLE_ROWCOUNT desc
4. MySQL数据库
SELECT
table_name,
table_rows
FROM
information_schema.TABLES
WHERE
table_schema = 'YourSchemas_name'
ORDER BY
table_rows DESC
5. PG数据库
SELECT
relname,
reltuples
FROM
pg_class
CLS LEFT JOIN pg_namespace N ON ( N.oid = CLS.relnamespace )
WHERE
nspname NOT IN ( 'pg_catalog', 'information_schema' )
AND relkind = 'r'
ORDER BY
reltuples DESC;
6. SQLSERVER数据库
SELECT
t.name AS TableName,
SUM(p.rows) AS RowCounts
FROM
sys.tables t
INNER JOIN
sys.partitions p ON t.object_id = p.object_id
WHERE
t.is_ms_shipped = 0
AND p.index_id IN (0,1)
GROUP BY
t.name
ORDER BY
RowCounts DESC;
7. 人大金仓数据库-瀚高数据库 应该都可以使用PG的语法
SELECT
relname,
reltuples
FROM
pg_class
CLS LEFT JOIN pg_namespace N ON ( N.oid = CLS.relnamespace )
WHERE
nspname NOT IN ( 'pg_catalog', 'information_schema' )
AND relkind = 'r'
ORDER BY
reltuples DESC;
