November 3, 2008 at 3:25 am
Thanks in advance,
JB
November 3, 2008 at 4:49 am
sp_msforeachdb 'select ''?'' as Databasename,* from [?].dbo.sysobjects where name = ''@@@@@@@'''
Replace @@@@@@@ with your search criteria
Wilfred
The best things in life are the simple things
November 3, 2008 at 5:28 am
ForreachDB has been altered in our organisation, is this any good?
select '?'
as Databasename,* from sysobjects
where name = 'vw_MessageStatus_'
November 3, 2008 at 5:44 am
No, If you use sp_msforeachdb, the questionmark will be replaced by the databasename (by MSSQL). What you can do is create a cursor for each existing database, like this:
declare c_db cursor for
select name from master.sys.databases where state_desc = 'online' and name not in ('tempdb')
declare @dbname sysname
declare @SQLCmd nvarchar(1000)
open c_db
fetch next from c_db into @dbname
while @@fetch_status = 0
begin
set @SQLCmd = 'select ''' + @dbname + ''' as "database", * from ' + @dbname + '.sys.objects where name like ''@@@@@'''
print @SQLCmd
exec (@SQLCmd)
fetch next from c_db into @dbname
end
close c_db
deallocate c_db
Wilfred
The best things in life are the simple things
November 3, 2008 at 5:56 am
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx
Failing to plan is Planning to fail
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply