查詢資料表筆數
-- 方法一：
select [obj].[name], [idx].[rows]
from [sys].[sysobjects] as [obj]
inner join [sys].[sysindexes] as [idx] on [obj].[id] = [idx].[id]
where [obj].[xtype] = N'U'
and [idx].[indid] = 1
order by [obj].[name];
go
-- 方法二：
select [o].[name], [i].[rows]
from [sys].[objects] as [o]
inner join [sys].[sysindexes] as [i] on [o].[object_id] = [i].[id]
inner join [sys].[schemas] as [s] on [o].[schema_id] = [s].[schema_id]
where [o].[type] = N'U' and [i].[indid] = 1 and [s].[name] = 'Person'
order by [o].[name];
go
結果:
-- 方法三：
declare @Result table([name] varchar(255), [rows] bigint
, [reserved] varchar(255), [data] varchar(255)
, [index_size] varchar(255), [unused] varchar(255));
insert into @Result
exec sp_MsForEachTable "Sp_SpaceUsed '?'";
select * from @Result order by name;
go
結果:
參考資料:
