decuare cur cursor for select name from sysobjects where xtype='U' declare @tbname varchar(128) declare @cnt int declare @sql nvarchar(400) open cur fetch next from cur into @tbname while @@fetch_status=0 begin set @sql=N'select @cnt=count(*) from ['+@bname+N']' exec sp_executesql @sql,'@cnt int output',@cnt output if @cnt>0 exec ('select * from ['+@tbname+']') fetch next from cur into @tbname end close cur deallocate cur
从系统表sysobjects里面检索表名,然后再用动态语句检索
sp_msforeachtable 'if (select top 1 rows from sysindexes where id=object_id(''?'') and status=0)>0 select ''?'' 表名, * from ?'
select name from sysobjects where xtype='U'
declare @tbname varchar(128)
declare @cnt int
declare @sql nvarchar(400)
open cur
fetch next from cur into @tbname
while @@fetch_status=0
begin
set @sql=N'select @cnt=count(*) from ['+@bname+N']'
exec sp_executesql @sql,'@cnt int output',@cnt output
if @cnt>0
exec ('select * from ['+@tbname+']')
fetch next from cur into @tbname
end
close cur
deallocate cur
sp_msforeachtable 'if exists(select top 1 1 from ?) select ''?'' 表名, * from ?'