sql server 2000中,要了解各表的记录数,有没有更方便的,不要一个个看属性的

Question

我的意思是,我有1000多个表,要怎么样才知道里面记录数最多的一个?

Answer 1

以下存储过程实现输出一个列表,字段是表名和记录数,并将较大的表放在前面,可在查询分析器中执行:
alter procedure alltablecount as
declare @name varchar(100)
declare tablecur cursor for select name from sysobjects where xtype= 'u '
create table #tablecount(tablename varchar(100),reccount int)
open tablecur
fetch next from tablecur into @name
while @@fetch_status!=-1
begin
exec ('insert into #tablecount select tablename='''+@name+''',reccount=count(1) from '+@name+' ')
fetch next from tablecur into @name
end
close tablecur
deallocate tablecur
select * from #tablecount order by reccount desc
go

exec alltablecount

Answer 2

使用游标实现的方法：
USE pubs
GO
DECLARE @NAME VARCHAR(40)
DECLARE @SQL VARCHAR(4000)
DECLARE authors_cursor CURSOR FOR
SELECT Name as 表名 FROM SYSOBJECTS
WHERE XTYPE='U'

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @NAME
SET @SQL='SELECT '''+@NAME+''' AS 表名, COUNT(1) AS 行数 FROM '+@NAME
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM authors_cursor
INTO @NAME
SET @SQL=@SQL+' UNION ALL SELECT '''+@NAME+
''' AS 表名,COUNT(1)AS 行数 FROM '+@NAME
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
EXEC(@SQL)
GO
测试通过，不过有一个表会显示两次，你可以再改改本回答被提问者采纳

Answer 3

没学习过SQL语言 所以我提供个思路
统计每个表的记录数 然后排序 正向反向 最大的就出来的
嘿嘿

Answer 4

关注下