--获取库中所有表及该表的记录数declare @sql varchar(8000) set @sql='select * from (' select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u' set @sql = left(@sql,len(@sql) - 10) + ')a' exec(@sql)
计算一个库里各个表的记录总数: select b.name,a.rowcnt from sysindexes a,sysobjects b where a.id=b.id and a.indid<2 and b.xtype='u'--统计数据库里每个表的详细情况 EXEC sp_MSforeachtable @command1="sp_spaceused '?'" --获得每个表的记录数和容量: EXEC sp_MSforeachtable @command1="print '?'", @command2="sp_spaceused '?'", @command3= "SELECT count(*) FROM ? "
新建的web技术交流群,欢迎大家加入一起讨论: 群号:29037453
EXEC SP_MSFOREACHTABLE 'DECLARE @I INT SELECT @I=COUNT(1) FROM ? HAVING COUNT(1)=1200 IF @I=1200 SELECT ''?'' '
declare @t table(tbname varchar(50),rowcnt varchar(20)) insert @t exec sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ?' select * from @t where rowcnt>1200
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a'
exec(@sql)
select b.name,a.rowcnt from sysindexes a,sysobjects b
where a.id=b.id and a.indid<2 and b.xtype='u'--统计数据库里每个表的详细情况
EXEC sp_MSforeachtable @command1="sp_spaceused '?'" --获得每个表的记录数和容量:
EXEC sp_MSforeachtable @command1="print '?'",
@command2="sp_spaceused '?'",
@command3= "SELECT count(*) FROM ? "
群号:29037453
SELECT @I=COUNT(1) FROM ? HAVING COUNT(1)=1200
IF @I=1200
SELECT ''?''
'
insert @t exec sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ?'
select * from @t where rowcnt>1200
2L的那个 @command1="sp_spaceused '?'" 不知道是什么意思额。。