---try :use master declare @DBname nvarchar(255) declare @aa nvarchar(255) declare cursor1 cursor for select [name] from sysDataBases open cursor1 fetch next from cursor1 into @DBname while (@@fetch_status=0) begin set @aa= 'use ' + @DBname -- exec SP_EXECUTESQL @aa set @aa = @aa + ' if exists (select * from sysobjects where id = object_id(N''[TABLE1]'')and OBJECTPROPERTY(id, N''IsUserTable'') = 1) print '+@DBname + '含有TABLE1表' +' else print '+@DBname + ' 没有 TABLE1 这个表' exec (@aa) fetch next from cursor1 into @DBname end close Cursor1 deallocate Cursor1 go
把所有的 set @aa= 'use ' + @DBname -- exec SP_EXECUTESQL @aa set @aa = @aa + ' if exists (select * from sysobjects where id = object_id(N''[TABLE1]'')and OBJECTPROPERTY(id, N''IsUserTable'') = 1) print '+@DBname + '含有TABLE1表' +' else print '+@DBname + ' 没有 TABLE1 这个表' exec (@aa)拼成一个SQL串,一起执行
use master -------- 你在master数据库直接建表? 你的表应该不是在master数据库吧?
declare @aa nvarchar(255) ,@DBname varchar(1000) set @dbname = 'slt_road' set @aa= 'use slt_road ' -- exec SP_EXECUTESQL @aa set @aa = @aa + ' if exists (select * from ss_tmp ) print '''+@DBname + '含有TABLE1表 '' else print '''+@DBname + ' 没有 TABLE1 这个表''' exec (@aa) /*slt_road含有TABLE1表 */
--try use j1 if exists (select * from sysobjects where upper(name)= 'TABLE1' and OBJECTPROPERTY(id, N'IsUserTable') = 1) print 'j1 含有TABLE1表' else print 'j1 没有 TABLE1 这个表'
use master declare @DBname nvarchar(255) declare @aa nvarchar(255) declare cursor1 cursor for select [name] from sysDataBases open cursor1 fetch next from cursor1 into @DBname while (@@fetch_status=0) begin set @aa='use '+@dbname set @aa = @aa +' if exists (select * from sysobjects where id = object_id(N''[TABLE1]'')and OBJECTPROPERTY(id, N''IsUserTable'') = 1) ' set @aa = @aa +' print '''+@DBname+' 含有TABLE1表'' else print '''+@DBname+' 没有 TABLE1 这个表 ''' exec SP_EXECUTESQL @aa fetch next from cursor1 into @DBname end close Cursor1 deallocate Cursor1
declare @DBname nvarchar(255)
declare @aa nvarchar(255)
declare cursor1 cursor for select [name] from sysDataBases
open cursor1
fetch next from cursor1 into @DBname
while (@@fetch_status=0)
begin
set @aa= 'use ' + @DBname
-- exec SP_EXECUTESQL @aa
set @aa = @aa + ' if exists (select * from sysobjects where id = object_id(N''[TABLE1]'')and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
print '+@DBname + '含有TABLE1表' +' else print '+@DBname + ' 没有 TABLE1 这个表'
exec (@aa)
fetch next from cursor1 into @DBname
end
close Cursor1
deallocate Cursor1
go
set @aa= 'use ' + @DBname
-- exec SP_EXECUTESQL @aa
set @aa = @aa + ' if exists (select * from sysobjects where id = object_id(N''[TABLE1]'')and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
print '+@DBname + '含有TABLE1表' +' else print '+@DBname + ' 没有 TABLE1 这个表'
exec (@aa)拼成一个SQL串,一起执行
你在master数据库直接建表?
你的表应该不是在master数据库吧?
declare @aa nvarchar(255) ,@DBname varchar(1000)
set @dbname = 'slt_road'
set @aa= 'use slt_road '
-- exec SP_EXECUTESQL @aa
set @aa = @aa + '
if exists (select * from ss_tmp )
print '''+@DBname + '含有TABLE1表 ''
else
print '''+@DBname + ' 没有 TABLE1 这个表'''
exec (@aa)
/*slt_road含有TABLE1表
*/
use j1
if exists (select * from sysobjects where upper(name)= 'TABLE1' and OBJECTPROPERTY(id, N'IsUserTable') = 1)
print 'j1 含有TABLE1表' else print 'j1 没有 TABLE1 这个表'
嘿嘿
写多了就不头晕 了
应此,set @aa= 'use ' + @DBname
exec SP_EXECUTESQL @aa
是不起作用的。
declare @DBname nvarchar(255)
declare @aa nvarchar(255)
declare cursor1 cursor for select [name] from sysDataBases
open cursor1
fetch next from cursor1 into @DBname
while (@@fetch_status=0)
begin
set @aa='use '+@dbname
set @aa = @aa +' if exists (select * from sysobjects where id = object_id(N''[TABLE1]'')and OBJECTPROPERTY(id, N''IsUserTable'') = 1) '
set @aa = @aa +' print '''+@DBname+' 含有TABLE1表'' else print '''+@DBname+' 没有 TABLE1 这个表 ''' exec SP_EXECUTESQL @aa
fetch next from cursor1 into @DBname
end
close Cursor1
deallocate Cursor1
如何把这些print结果,写到查询中呀
http://topic.csdn.net/u/20080117/11/d761c4ab-fcce-40aa-8a44-475311d04cb7.html