SQL Server 查询数据库里所有表的行数,注意先切换到对应的库,该语句需要在指定的库下面执行。
查询sql server 所有表和行数
–查询所有数据库名
select [name] from [sysdatabases]
where name not in('master','model','msdb','tempdb') order by [name]
–查询指定数据库的所有表名
use [数据库名]; select name from sysobjects where xtype='u'
–查询指定数据库的所有表名 方法2 (该语句需要在指定的库下面执行)
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC
–查询当前表所有表名(全部信息)
select * from sys.tables
–查询所有表名及对应架构
select t.[name] as tablename, s.[name] as [schema] from sys.tables as t,sys.schemas as s where t.schema_id = s.schema_id
–查询数据库中所有的表名及行数
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC
–查询所有的标明及空间占用量\行数
select
object_name(id) tablename,
8*reserved/1024 reserved,
rtrim(8*dpages)+'kb' used,
8*(reserved-dpages)/1024 unused,
8*dpages/1024-rows/1024*minlen/1024 free,
rows
--,*
from sysindexes
where indid=1
order by tablename,reserved desc
–查询数据库中所有表名及行数 –带架构名
SELECT s.name,a.name, b.rows
FROM sys.tables AS a
INNER JOIN sysindexes AS b ON a.object_id = b.id
LEFT JOIN sys.schemas as s on a.schema_id = s.schema_id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC