查询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