I have the following code, I need to run this code through over 200 tables, can I create a list and a for loop in the variable to go through all the tables and fetch the results for each? Also, how can I include the table name for each row in this query? I also need to go through a list on the column but I can't figure that out from the table list.

  DECLARE @table AS VARCHAR(100)
  DECLARE @column as varchar(50)

  set @table = 'x'
  set @column = 'y'

  declare @query as varchar(max)
  set @query = '  SELECT  CAST(MONTH('   @column   ') AS VARCHAR(2))   ''-''   CAST(YEAR('   @column   ') AS VARCHAR(4)) as date, count(*) as SRC_CNT
  FROM '   @table  
  ' WHERE '   @column   ' >= ''2018-01-01'' AND  '   @column   '< ''2021-12-01''
  group BY CAST(MONTH('   @column   ') AS VARCHAR(2))   ''-''     CAST(YEAR('   @column   ') AS VARCHAR(4))
  order by date;'

  exec(@query)

CodePudding user response：

Rather than loop, I would create a dynamic batch, with all the statements you need, which you can then execute. You can use your best friend to debug the statement(s) if needed:

DECLARE @Column sysname = N'y',
        @DateFrom date = '20180101',
        @DateTo date = '20211201';

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

SELECT @SQL = STRING_AGG(N'SELECT RIGHT(CONVERT(varchar(10),'   QUOTENAME(@Column)   N',105),7) AS [Date],'   @CRLF  
                         --N'       N'   QUOTENAME(t.[name],'''')   N' AS TableName,'   @CRLF   --Uncomment this line if you need it.
                         N'       COUNT(*) AS SRC_CNT'   @CRLF  
                         N'FROM '   QUOTENAME(s.[name])   N'.'   QUOTENAME(t.[name])   @CRLF  
                         N'WHERE '   QUOTENAME(@Column)   N' >= @DateFrom AND '   QUOTENAME(@Column)   N' < @DateTo'   @CRLF  
                         N'GROUP BY RIGHT(CONVERT(varchar(10),'   QUOTENAME(@Column)   N',105),7)'   @CRLF  
                         N'ORDER BY [date];',@CRLF)
FROM sys.schemas s
     JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE EXISTS (SELECT 1
              FROM sys.columns c
              WHERE c.object_id = t.object_id
                AND c.[name] = @Column);

--PRINT @SQL; --Your best friend
EXEC sys.sp_executesql @SQL, N'@DateFrom date, @DateTo date', @DateFrom, @DateTo;

CodePudding user response：

Given tables and sample data:

CREATE TABLE dbo.x(y date, r int);
CREATE TABLE dbo.y(y date, r int);
CREATE TABLE dbo.z(y date, r int);

INSERT dbo.x(y,r) VALUES('20180105',5),
                  ('20180107',6),('20180509',7);
INSERT dbo.y(y,r) VALUES('20180905',5),
                  ('20181007',6),('20181009',7);
INSERT dbo.z(y,r) VALUES('20180605',5),
                  ('20180607',6),('20180609',7);

This dynamic SQL can be generated:

DECLARE @col       sysname = N'y',
        @StartDate date    = '20180101',
        @EndDate   date    = '20211201';


DECLARE @sql  nvarchar(max) = N'',
        @base nvarchar(max) = N'SELECT [Table Name] = $tblQ$, 
    [date] = DATEFROMPARTS(YEAR($col$), MONTH($col$), 1), 
    SRC_CNT = COUNT(*)
  FROM dbo.$tbl$ WHERE $col$ >= @s AND y < @e
  GROUP BY YEAR($col$), MONTH($col$)';
  
SELECT @sql = STRING_AGG(REPLACE(REPLACE(REPLACE
        (@base, N'$tblQ$', QUOTENAME(t.name, char(39))
        ),N'$tbl$',QUOTENAME(t.name)), N'$col$', @col), N'
   UNION ALL
   ')   ' ORDER BY [date];'
  FROM sys.tables AS t
  WHERE EXISTS (SELECT 1 FROM sys.columns
    WHERE [object_id] = t.[object_id]
    AND name = @col);
  
  PRINT @sql;

  EXEC sys.sp_executesql @sql, 
       N'@s date, @e date', 
       @StartDate, @EndDate;

Which produces a query like this (only tables that actually have that column name, could be made even safer by making sure they're using a date/time type):

SELECT [Table Name] = 'x', 
    [date] = DATEFROMPARTS(YEAR(y), MONTH(y), 1), 
    SRC_CNT = COUNT(*)
  FROM dbo.[x] WHERE y >= @s AND y < @e
  GROUP BY YEAR(y), MONTH(y)
   UNION ALL
   SELECT [Table Name] = 'y', 
    [date] = DATEFROMPARTS(YEAR(y), MONTH(y), 1), 
    SRC_CNT = COUNT(*)
  FROM dbo.[y] WHERE y >= @s AND y < @e
  GROUP BY YEAR(y), MONTH(y)
   UNION ALL
   SELECT [Table Name] = 'z', 
    [date] = DATEFROMPARTS(YEAR(y), MONTH(y), 1), 
    SRC_CNT = COUNT(*)
  FROM dbo.[z] WHERE y >= @s AND y < @e
  GROUP BY YEAR(y), MONTH(y) ORDER BY [date];

That generates output like this:

• Example db<>fiddle

If you really want yyyy-MM instead of yyyy-MM-dd on the output, you can just change this line in the declaration of @base:

[date] = CONVERT(char(7), DATEFROMPARTS(YEAR($col$), MONTH($col$), 1), 120),

And I realized the requirement was MM-yyyy, in which case:

[date] = RIGHT(CONVERT(char(10), 
         DATEFROMPARTS(YEAR($col$), MONTH($col$), 1), 105), 7),

CodePudding user response：

I usually do the following for tables:

SELECT TABLE_NAME 
FROM [<DATABASE_NAME>].INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'

You said you could handle columns from there.