select b.name 'TabName' from sys.sysindexes a inner join sys.objects b on a.id=b.object_id where b.type='U' and a.indid=1 and a.rowcnt>10000
declare @str varchar(max) set @str='' select @str=@str+'select tbl_name='+quotename(name,'''')+',count(1) as counts from ' +name+char(13)+'union all'+ char(13) from( select name from sysobjects where xtype='U' and category=0)a set @str=LEFT(@str,LEN(@str)-9) exec(@str) /* tbl_name counts 学生表 4 补考表 5 test 3 FGSSB 15 tbla 11 tb_emp 6 tt 4 t1 0 TabName 5 t2 0 表2 4 表1 5 ttttt 0 tttt 0 sell 2 Yearsell 2 report 5 tbl 8 tblb 5 表一 3 表二 3 表a 3 表b 3 S1 2 QuestionType 6 P2 2 J3 2 SPJ 2 */ 我本机测试
select object_name(id),rows from sysindexes where id in( select object_id from sys.objects where type = 'u') and rows > '10000' and indid < '2'
DECLARE @sql NVARCHAR(max) SET @sql=' DECLARE @table TABLE(table_name VARCHAR(100),cnt INT) INSERT INTO @table ' SELECT @sql=@sql+N' SELECT TABLE_NAME='''+TABLE_SCHEMA+N'.'+TABLE_NAME+''',COUNT(1) as cnt FROM '+TABLE_SCHEMA+N'.'+TABLE_NAME+N' union all' FROM INFORMATION_SCHEMA.tables WHERE TABLE_TYPE='BASE TABLE' SET @sql=LEFT(@sql,LEN(@sql)-LEN('union all')) SET @sql=@sql+N'select * from @table where cnt> 10000 ' EXEC(@sql)
SELECT OBJECT_NAME(object_id) FROM sys.dm_db_partition_stats WHERE row_count >10000 GROUP BY OBJECT_NAME(object_id)
select b.name 'TabName' from dbo.sysindexes a inner join dbo.sysobjects b on a.id=b.id where b.type='U' and a.indid=1 and a.rowcnt>10000我用的是sql server2000
SELECT OBJECT_NAME(Id) FROM sysindexes WHERE indid < 2 AND rows > 10000
select b.name 'TabName'
from sys.sysindexes a
inner join sys.objects b on a.id=b.object_id
where b.type='U' and a.indid=1 and a.rowcnt>10000
declare @str varchar(max)
set @str=''
select @str=@str+'select tbl_name='+quotename(name,'''')+',count(1) as counts from '
+name+char(13)+'union all'+ char(13)
from(
select name from sysobjects where xtype='U' and category=0)a
set @str=LEFT(@str,LEN(@str)-9)
exec(@str)
/*
tbl_name counts
学生表 4
补考表 5
test 3
FGSSB 15
tbla 11
tb_emp 6
tt 4
t1 0
TabName 5
t2 0
表2 4
表1 5
ttttt 0
tttt 0
sell 2
Yearsell 2
report 5
tbl 8
tblb 5
表一 3
表二 3
表a 3
表b 3
S1 2
QuestionType 6
P2 2
J3 2
SPJ 2
*/
我本机测试
select object_name(id),rows from sysindexes where id in( select object_id from sys.objects where type = 'u') and rows > '10000' and indid < '2'
DECLARE @sql NVARCHAR(max)
SET @sql=' DECLARE @table TABLE(table_name VARCHAR(100),cnt INT)
INSERT INTO @table '
SELECT @sql=@sql+N' SELECT TABLE_NAME='''+TABLE_SCHEMA+N'.'+TABLE_NAME+''',COUNT(1) as cnt FROM '+TABLE_SCHEMA+N'.'+TABLE_NAME+N' union all'
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE='BASE TABLE'
SET @sql=LEFT(@sql,LEN(@sql)-LEN('union all'))
SET @sql=@sql+N'select * from @table where cnt> 10000 '
EXEC(@sql)
SELECT OBJECT_NAME(object_id)
FROM sys.dm_db_partition_stats
WHERE row_count >10000
GROUP BY OBJECT_NAME(object_id)
from dbo.sysindexes a
inner join dbo.sysobjects b on a.id=b.id
where b.type='U' and a.indid=1 and a.rowcnt>10000我用的是sql server2000
SELECT OBJECT_NAME(Id) FROM sysindexes
WHERE indid < 2 AND rows > 10000