Question

sql server 2000中如何查询数据库中哪个表行数最多?

谢谢。

Answer 1

建个临时表
CREATE TABLE #TEMP(
TABLENAME VARCHAR(100),
COUNTNUMBER INT
)

EXEC sp_MSforeachtable 'INSERT INTO #TEMP SELECT ''?'', COUNT(*) FROM ?'

SELECT * FROM #TEMP
ORDER BY COUNTNUMBER DESC

第一条的TABLENAME就是最多的那个表了

Answer 2

方法一：
create proc ss
as
set nocount on
create table TableSpace
(
TableName varchar(50),
RowsCount char(11),
Reserved varchar(18),
Data varchar(18),
Index_size varchar(18),
Unused varchar(18)
)
declare @sql varchar(500)
declare @TableName varchar(50)
declare Cursor1 Cursor
for
select name from sysobjects where xtype='U'
open Cursor1
fetch next from Cursor1 into @TableName
while @@fetch_status = 0
begin
set @sql = 'insert into TableSpace '
set @sql = @sql + ' exec sp_spaceused '''+ @TableName + ''' '
exec (@sql)
fetch next from Cursor1 into @TableName
end
close Cursor1
deallocate Cursor1
select tableName,rowscount from TableSpace where tableName<>'TableSpace'
order by rowscount
drop table TableSpace
go
exec ss
方法二：
select 表名=a.name,记录数=b.rows
from sysobjects a,sysindexes b
where a.name=b.name and a.type='u'
order by =b.rows

Answer 3

这个要自己统计吧。还不知道有什么新方法呢。关注一下。