if exists(select name from sysobjects where name ='topo001'and type='u') print '有这个表啊!'
select identity(int, 1,1) as id ,name into #t from master..sysdatabases declare @i int ,@num int ,@db varchar(100) select @i=1,@num=count(*) from #t while (@i<@num) beginselect @db=name from #t where id=@i exec ('if ( ''topo001'' in ( select name from '+@db+'..sysobjects where xtype=''U'')) print 1 ')set @i=@i+1 end --有的话 结果为1
谢谢 yingqiyingqi(英绮) 和happydreamer(小黑-从头学起) 谢谢了
你应该好好学学高级SQL语句了 哈哈
--用这个,查到后会显示出数据库名称 declare @sql nvarchar(1000),@para nvarchar(100) declare @dbname varchar(200),@i intdeclare #db cursor for select name from master..sysdatabases open #dbset @para='@ire int output'fetch next from #db into @dbname while @@fetch_status=0 begin set @sql='select @ire=count(*) from ['+@dbname+'].dbo.sysobjects' +' where xtype=''U'' and name=''topo001''' exec sp_executesql @sql,@para,@i output if isnull(@i,0)>1 begin print '在数据库 ['+@dbname+'] 中发现表[topo001]' goto lbexit --如果要查找完所有数据,就不要这句 end fetch next from #db into @dbname end lbexit: close #db deallocate #db
use masterdeclare @SQLString nvarchar(4000)--做一个数据库名的游标 DECLARE DatabaseName_cursor CURSOR FOR SELECT name FROM sysdatabases OPEN DatabaseName_cursorDECLARE @DatabaseName sysname FETCH NEXT FROM DatabaseName_cursor INTO @DatabaseNameWHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN --从所有的数据库里面一个一个地找 set @SQLString='' set @SQLString =@SQLString +'use '+@DatabaseName+char(13) set @SQLString =@SQLString +'if exists(select name from sysobjects'+char(13) set @SQLString =@SQLString +' where name =''ERPomniorder''and type=''u'')'+char(13) set @SQLString =@SQLString +'print '''+@DatabaseName+'有这个表'''+char(13) set @SQLString =@SQLString +'else '+char(13) set @SQLString =@SQLString +'print '''+@DatabaseName+'没有'''+char(13) set @SQLString =@SQLString +'use master'+char(13) EXEC sp_executesql @SQLString
END FETCH NEXT FROM DatabaseName_cursor INTO @DatabaseName END CLOSE DatabaseName_cursor DEALLOCATE DatabaseName_cursor
where name ='topo001'and type='u')
print '有这个表啊!'
select identity(int, 1,1) as id ,name into #t from master..sysdatabases
declare @i int ,@num int ,@db varchar(100)
select @i=1,@num=count(*) from #t while (@i<@num)
beginselect @db=name from #t where id=@i
exec ('if ( ''topo001'' in ( select name from '+@db+'..sysobjects where xtype=''U'')) print 1 ')set @i=@i+1
end --有的话 结果为1
哈哈
declare @sql nvarchar(1000),@para nvarchar(100)
declare @dbname varchar(200),@i intdeclare #db cursor for select name from master..sysdatabases
open #dbset @para='@ire int output'fetch next from #db into @dbname
while @@fetch_status=0
begin
set @sql='select @ire=count(*) from ['+@dbname+'].dbo.sysobjects'
+' where xtype=''U'' and name=''topo001'''
exec sp_executesql @sql,@para,@i output
if isnull(@i,0)>1
begin
print '在数据库 ['+@dbname+'] 中发现表[topo001]'
goto lbexit --如果要查找完所有数据,就不要这句
end
fetch next from #db into @dbname
end
lbexit:
close #db
deallocate #db
DECLARE DatabaseName_cursor CURSOR
FOR SELECT name FROM sysdatabases
OPEN DatabaseName_cursorDECLARE @DatabaseName sysname
FETCH NEXT FROM DatabaseName_cursor INTO @DatabaseNameWHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
--从所有的数据库里面一个一个地找
set @SQLString=''
set @SQLString =@SQLString +'use '+@DatabaseName+char(13)
set @SQLString =@SQLString +'if exists(select name from sysobjects'+char(13)
set @SQLString =@SQLString +' where name =''ERPomniorder''and type=''u'')'+char(13)
set @SQLString =@SQLString +'print '''+@DatabaseName+'有这个表'''+char(13)
set @SQLString =@SQLString +'else '+char(13)
set @SQLString =@SQLString +'print '''+@DatabaseName+'没有'''+char(13)
set @SQLString =@SQLString +'use master'+char(13) EXEC sp_executesql @SQLString
END
FETCH NEXT FROM DatabaseName_cursor INTO @DatabaseName
END
CLOSE DatabaseName_cursor
DEALLOCATE DatabaseName_cursor