Running a script on multiple databases-CodePudding

I would like to make a script that launches on several bases to give Microsoft this located on the same server. (knowing that new databases are created every month) In the best of worlds, the result would have to be exported in CSV in one or more CSV files

Here is the query SQL:

SELECT [CT_Num]
      ,[CT_Intitule]
      ,[CG_NumPrinc]
      ,'INFO_L100'
  FROM [dbo].[F_COMPTET]

where ct_type = 1 and ct_sommeil = 0

I had another hypothesis but it only runs on the master database and does not save the result

DECLARE @command VARCHAR(255);
SET @command='select CT_Num ,CT_Intitule ,CG_NumPrinc FROM [dbo].[F_COMPTET]'
EXEC SP_msforeachdb @command

Thanks in advance.

CodePudding user response：

Add a USE command to set the database context and an IF to omit system databases:

DECLARE @command VARCHAR(255);
SET @command='USE [?];IF DB_NAME() NOT IN(''master'',''model'',''msdb'',''tempdb'',''SSISDB'') select CT_Num ,CT_Intitule ,CG_NumPrinc FROM [dbo].[F_COMPTET];';
EXEC SP_msforeachdb @command;