MCITP

MCITP

Thursday, March 10, 2011

Search single table in any database on server

DECLARE @db AS VARCHAR(100)
set @db = ''
declare cur cursor for
select name from sys.databases --where name in ('master','OMEMEADW')
OPEN cur
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('IF EXISTS(select top 1 * from ' + @db + '.sys.objects where type = ''u'' and name = ''##TABLENAME'')
PRINT ''' + @db + '''')
FETCH NEXT FROM cur INTO @db
end
CLOSE cur
DEALLOCATE cur

No comments:

Post a Comment