呵呵,正好我工作中写过一个,需要你给出一个数字范围,选出满足这个范围的create procedure filte_by_count(@begin int,@end int) as declare @tbl_name varchar(30) declare @count int declare cur_tblname cursor for select distinct name from sysobjects where xtype='u' if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#filter]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[#filter] create table #filter(tablename varchar(50),[count] int) open cur_tblname fetch next from cur_tblname into @tbl_name while @@fetch_status = 0 begin exec('insert into #filter select '''+@tbl_name+''',count(*) from '+@tbl_name+'') fetch next from cur_tblname into @tbl_name end select * from #filter where count>=@begin and count<@end order by [count] DESC close cur_tblname deallocate cur_tblnameGO
这样给个范围调用就好了 exec filte_by_count 50000,9999999
select object_name(id),rowcnt from sysindexes where rowcnt>=50000 and indid<2 and objectproperty(id,'isusertable')=1
当然,用 select 'select count(*) from '+name from sysobjects where xtype='u'拼出语句,然后贴出来执行也可以
CREATE PROCEDURE 取得各表有多少记录 @j as varchar(13) as declare @tbl_name varchar(100) declare @V_Count varchar(100) DECLARE testcursor CURSOR FOR select name as ddd from sysobjects where xtype='u' order by name
OPEN testcursor
FETCH NEXT FROM testcursor into @tbl_name WHILE @@FETCH_STATUS = 0 BEGIN --1 execute('declare @i varchar(100) select @i=count(*) from '+@tbl_name+' if @i>'+@j+' begin print '''+@tbl_name +'''+''--''+@i end') --2 --execute('declare @i varchar(100) --select @i=count(*) from '+@tbl_name+' --select '''+@tbl_name +''',+@i') --3 /*execute('declare @i varchar(100) select @i=count(*) from '+@tbl_name+' print '+@tbl_name +'+''--''+@i end')*/ FETCH NEXT FROM testcursor into @tbl_name END
CLOSE testcursor DEALLOCATE testcursor GO
andy1995(发现一个不错的SQLServer网站:www.windbi.com) ( ) 信誉:100 Blog 加为好友 的方法不错,但是为什么我的 sysindexes 表很多重复的记录
你没有加上 indid<2 这个条件吧
indid是什么东东?麻烦讲一下
select object_name(id),rowcnt from sysindexes where rowcnt>=50000 and indid=1 and objectproperty(id,'isusertable')=1
dbcc checkdb(数据库名称)就行了吧
sp4(1) ( ) 信誉:100 Blog 加为好友 2007-04-30 15:01:23 得分: 0
select object_name(id),rowcnt from sysindexes where rowcnt>=50000 and indid=1 and objectproperty(id,'isusertable')=1
as
declare @tbl_name varchar(30)
declare @count int
declare cur_tblname cursor for
select distinct name from sysobjects where xtype='u'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#filter]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#filter]
create table #filter(tablename varchar(50),[count] int)
open cur_tblname
fetch next from cur_tblname into @tbl_name
while @@fetch_status = 0
begin
exec('insert into #filter select '''+@tbl_name+''',count(*) from '+@tbl_name+'')
fetch next from cur_tblname into @tbl_name
end
select * from #filter where count>=@begin and count<@end order by [count] DESC
close cur_tblname
deallocate cur_tblnameGO
exec filte_by_count 50000,9999999
where rowcnt>=50000 and indid<2 and objectproperty(id,'isusertable')=1
select 'select count(*) from '+name from sysobjects where xtype='u'拼出语句,然后贴出来执行也可以
CREATE PROCEDURE 取得各表有多少记录 @j as varchar(13) as
declare @tbl_name varchar(100)
declare @V_Count varchar(100)
DECLARE testcursor CURSOR FOR select name as ddd from sysobjects where xtype='u' order by name
OPEN testcursor
FETCH NEXT FROM testcursor into @tbl_name
WHILE @@FETCH_STATUS = 0
BEGIN
--1
execute('declare @i varchar(100)
select @i=count(*) from '+@tbl_name+'
if @i>'+@j+' begin
print '''+@tbl_name +'''+''--''+@i
end')
--2
--execute('declare @i varchar(100)
--select @i=count(*) from '+@tbl_name+'
--select '''+@tbl_name +''',+@i')
--3
/*execute('declare @i varchar(100)
select @i=count(*) from '+@tbl_name+'
print '+@tbl_name +'+''--''+@i
end')*/ FETCH NEXT FROM testcursor into @tbl_name
END
CLOSE testcursor
DEALLOCATE testcursor
GO
的方法不错,但是为什么我的 sysindexes 表很多重复的记录
where rowcnt>=50000 and indid=1 and objectproperty(id,'isusertable')=1
select object_name(id),rowcnt from sysindexes
where rowcnt>=50000 and indid=1 and objectproperty(id,'isusertable')=1
---------------------
好!!!
sysindexes只是大概,不过上下也差不了几条,在不是精确的情况下是可以的。
dbcc updatestacstics 看看这个。(最好别用,可能会是索引有问题,因为我就遇到过)。
最后,还是建议用楼上的楼上的办法
select object_name(id),rowcnt from sysindexes
where rowcnt>=50000 and indid=1 and objectproperty(id,'isusertable')=1
indid=1 查询的就是根据聚集索引键值来查询
indid>1 则代表查询的是根据非聚集索引,但没有聚集索引的表还是大量存在的(这不比聚集索引)至于此查询的误差在索引碎片的产生和清理过程确实会存在,只要索引定期维护、重建,那么误差应该不大,甚至不存在误差了而对于楼主的这类要求,应该满足需要
indid=1 查询的就是根据聚集索引键值来查询
indid>1 则代表查询的是根据非聚集索引,但没有聚集索引的表还是大量存在的(这不比聚集索引)至于此查询的误差在索引碎片的产生和清理过程确实会存在,只要索引定期维护、重建,那么误差应该不大,甚至不存在误差了而对于楼主的这类要求,应该满足需要
--------------------------学习
andy1995(发现一个不错的SQLServer网站:www.windbi.com) ( ) 信誉:100 Blog 加为好友 2007-4-30 14:22:43 得分: 0
select object_name(id),rowcnt from sysindexes
where rowcnt>=50000 and indid<2 and objectproperty(id,'isusertable')=1