Stored procedure that do some job in a subset of databases

• Carl B.

  SSCertifiable

  Points: 7931

  More actions

  December 19, 2006 at 6:46 am

  #678901

  In sp_MyWork using this call method:

  SET @sql = 'EXEC ?.dbo.sp_MyWork ' + CONVERT(varchar(10), @a)

  EXEC sp_MsForEachDB @sql

  I cannot query data dictionnary (sys schema views). I believe it is the data dictionnary from the master database that is always queried.

  One exemple: IF exists (select 'x' from sys.schemas where name = @username) ...

  I don't receive the correct "answer".

  What am I missing?

  Regards.

  Carl

• Carl B.

  SSCertifiable

  Points: 7931

  More actions

  December 19, 2006 at 8:34 am

  #678930

  I finally found that every query must be dynamic and fully qualified:

  EXEC ('SELECT ''X'' FROM '+db_name()+'.sys.schemas WHERE name = '+''''@username+'''')

  IF (@@ROWCOUNT = 1)

   ...

  sp_MSforeachdb is really a "black box"...

  Carl

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  December 19, 2006 at 8:43 am

  #678936

  It's not unsupported for no reason (unless it changed in 2005).

• Carl B.

  SSCertifiable

  Points: 7931

  More actions

  December 19, 2006 at 8:52 am

  #678939

  Its as you said...

  By the way I didn't have the time to schedule a public humiliation session, too much work to do fighting against sp_MSforeachdb...

  Thanks for your help.

  Carl