Changing the result set to have the result of all databases instead of just one

• river1

  SSC-Insane

  Points: 24309

  More actions

  January 5, 2018 at 7:54 am

  #327029

  Dear Forum Members,

  I have the below query that returns some results based on the database that I have in the querypane.  

  All my user databases have the required objects. I would like to change this t-SQL to return the values for each database instead of having to change the database name in the query pane.

  Can you please help me understand how to do this?

  Thank you

  declare @DB varchar(1000)

  declare @StrSQL varchar (8000)

  Declare @startdate VARCHAR(10)

  Declare @enddate VARCHAR(10)

  --set @DB ='dwhstaging'

  set @STARTDATE ='05.01.2017'

  set @enddate ='05.01.2018'

  set @strSQL =

  ('

  select top 10

  COUNT(*) NumberExecutions,

  execsp.schemaname + '' - '' + execsp.SPname StoredProcedure,

  (sum(rll.rowsinserted) + sum(rll.rowsdeleted) + sum(rll.rowsupdated)) DataChanges,

  avg(DATEDIFF(second, execsp.[StartDateTime], execsp.[EndDateTime])) TExecutiontimeSec,

  DATABASENAME

  from

  ETL.vwRowLoadLog rll

  INNER JOIN

  Logging.SPExecutions execsp

  ON

  execsp.ExecutionID=rll.ExecutionID

  AND

  convert( date, execsp.StartDateTime,103) >= convert( date, ''' + @StartDate + ''' , 103)

  AND

  convert (date, execsp.enddatetime,103) <= convert (date, ''' + @EndDate + ''', 103)

  GROUP BY

  DATABASENAME, execsp.schemaname + '' - '' + execsp.SPname

  order by texecutiontimesec desc

  ')

  exec (@StrSQL)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 5, 2018 at 8:17 am

  #1974703

  Perhaps this will help:
  
declare @DB varchar(1000)
        , @StrSQL nvarchar (max)
        , @SQLParm NVARCHAR(MAX) = '@StartDate DATE, @EndDate DATE'
        , @StartDate DATE --VARCHAR(10)
        , @EndDate DATE --VARCHAR(10)
--set @DB ='dwhstaging'
set @StartDate = '2017-05-01' -- '05.01.2017'
set @EndDate = '2018-05-01' -- '05.01.2018'

  /*
  you can create a temporary table here to capture the data from the query
  */
  DECLARE UserDBs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
  SELECT
    [d].[name]
  FROM
    [sys].[databases] AS [d]
  WHERE
    [d].[database_id] > 4
  ORDER BY
    [d].[name]
  ;

  OPEN [UserDBs];

  FETCH NEXT FROM [UserDBs]
  INTO @DB

  WHILE @@FETCH_STATUS = 0
  BEGIN

    SET @StrSQL = 
    ' use ' + QUOTENAME(@DB) + '
    -- INSERT INTO #YourTemporaryTableCreatedAbove( <ColumnList> )
       select  top 10
             COUNT(*) NumberExecutions,
             execsp.schemaname + '' - '' + execsp.SPname StoredProcedure,
             (sum(rll.rowsinserted) + sum(rll.rowsdeleted) + sum(rll.rowsupdated)) DataChanges,
             avg(DATEDIFF(second, execsp.[StartDateTime], execsp.[EndDateTime])) TExecutiontimeSec,
             DATABASENAME
       from
             ETL.vwRowLoadLog rll
       INNER JOIN
          Logging.SPExecutions execsp
       ON
          execsp.ExecutionID=rll.ExecutionID
        AND
           execsp.StartDateTime >= @StartDate
       AND
           execsp.enddatetime < @EndDate 
       GROUP BY 
          DATABASENAME, execsp.schemaname + '' - '' + execsp.SPname
       order by texecutiontimesec desc
    ';

    exec [sys].[sp_executesql] @StrSQL, @SQLParm, @StartDate = @StartDate, @EndDate = @EndDate;

    FETCH NEXT FROM [UserDBs]
    INTO @DB

  END

  CLOSE [UserDBs];
  DEALLOCATE [UserDBs];

  --SELECT <ColumnList> FROM #YourTemporaryTableCreatedAbove ORDER BY <ColumnsToOrderByHere>; -- to return all the data

  Please, if you don't understand what is going on, ask questions or don't use it.  You will have to support the code, not us.

  EDIT: Minor change to code to eliminate the emoji.