SQL Server : run a script on all databases

Question

I have a SQL script with a function (function drops at the end of the script) and temporary table definitions and variables to process the data that gives me one single line result for a database.

It works but I need to run and get the all results for 1000 databases. It seems impossible to run the script seperately.

I have found sp_MSForEachDB single command line examples but I have a whole script and couldn't make it work with this way.

Is there any way to run a script on all the databases?

Thanks for reply

Answer 1

I'm not saying this is a good idea, since we don't really know what this script you're running does, but this should work. I don't envy you the pain of getting all of the single quotes in the right places, but it's the price we pay for using undocumented, unsupported stored procedures.

DECLARE @sqlText varchar(max); 

SET @sqlText = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
  BEGIN 
    USE ? 
    EXEC(''CREATE <functions, tables, what have you>'') 
  END';

EXECUTE sp_MSforeachdb @sqlText;