SQL query to count a column in all tables

Question

So far, I could retrieve the list of database tables using the following SQL query:

SELECT
    DISTINCT
    TABLE_SCHEMA,
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS

In each of these tables, the first column is called " Year". Values ​​from year " 2011" to year " 2017":

CREATE TABLE foo (
    [Year] int,
    AnotherColumn varchar(50),
    ...
)

CREATE TABLE bar (
    [Year] int,
    SomeOtherColumn guid,
    ...
)

CREATE TABLE ...

Now I will need to count the number of rows of different years in each of the tables and display the output in the following format:

| TABLE_SCHEMA | TABLE_NAME | 2011                | 2012                | ... | 2017                |
|:-------------|-----------:|:-------------------:|:-------------------:|:----|:-------------------:|
| SCHEMA       | foo        | no. of rows of 2011 | no. of rows of 2012 | ... | no. of rows of 2017 | 
| SCHEMA       | bar        | no. of rows of 2011 | no. of rows of 2012 | ... | no. of rows of 2017 | 
| SCHEMA       | ...        | no. of rows of 2011 | no. of rows of 2012 | ... | no. of rows of 2017 | 

Does anyone have any suggestions? Many thanks!

Answer 1

SQL , (, , ..), , Dynamic-SQL, ( SQL).

Dynamic-SQL, , , Dynamic-SQL:

SELECT
    'Table1' AS TableName
    [Year],
    COUNT(*) AS YearRowCount
FROM
    Table1
GROUP BY
    [Year]

UNION ALL

SELECT
    'Table2' AS TableName
    [Year],
    COUNT(*) AS YearRowCount
FROM
    Table2
GROUP BY
    [Year]

UNION ALL

...

, .

:

TableName    Year    YearRowCount
'Table1'     2017            1234
'Table1'     2016            2345
'Table1'     2015            3456
'Table1'     2014            1234
'Table1'     2013            1234
'Table1'     2011            1234
'Table2'     2017            1234
'Table2'     2016            2345
'Table2'     2015            3456
'Table2'     2013            1234
'Table2'     2012            1234
'Table2'     2011            1234
...

PIVOT . PIVOT ( UNPIVOT) , , , , PIVOT ALL - ).

SELECT
    tableName,
    YearRowCount,
    [2011], [2012], [2013], [2014], [2015], [2016], [2017]
FROM
(
    -- our UNION query goes here --
)
PIVOT
(
    SUM( YearRowCount )
    FOR [Year] IN ( 2011, 2012, 2013, 2014, 2015, 2016, 2017 )
)

, PIVOT, , .

SQL " ...". - CURSOR, - - T-SQL (WHILE ..) - , 3- , . .

, , () FORMATMESSAGE, sprintf. FORMATMESSAGE SQL Server 2016 ( 130, ). , CONCAT 'foo' + @var + 'bar' - .

COALESCE( [aggregate] + [separator], '' ) + [value], : fooobar.com/questions/979/... - () , . , SQL (.. ), , - .

DECLARE @unionTemplate varchar(1024) = '
SELECT
    ''%s.%s'' AS TableName
    [Year],
    COUNT(*) AS YearRowCount
FROM
    [%s].[%s]
GROUP BY
    [Year]
'

DECLARE @unionSeparator varchar(20) = '
UNION ALL
'

DECLARE @unionQuery varchar(max)

SELECT
    @unionQuery = COALESCE( @unionQuery + @unionSeparator, '' ) + FORMATMESSAGE( @unionTemplate, SCHEMA_NAME, TABLE_NAME, SCHEMA_NAME, TABLE_NAME )
FROM
    INFORMATION_SCHEMA.TABLES
ORDER BY
    SCHEMA_NAME,
    TABLE_NAME

, @unionQuery, ...

DECLARE @pivotQuery varchar(max) = '
SELECT
    tableName,
    YearRowCount,
    [2011], [2012], [2013], [2014], [2015], [2016], [2017]
FROM
(
    %s
)
PIVOT
(
    SUM( YearRowCount )
    FOR [Year] IN ( 2011, 2012, 2013, 2014, 2015, 2016, 2017 )
)'

SET @pivotQuery = FORMATMESSAGE( @pivotQuery, @unionQuery )

... (EXEC sp_executesql EXEC()) - , EXEC() - , EXEC!

EXEC sp_executesql @pivotQuery

-!

SQL Server (2014, 2012, 2008 R2, 2008):

, SQL Server 2016 (v13.0), FORMATMESSAGE:

DECLARE @unionQuery nvarchar(max)

SELECT
    @unionQuery =
        COALESCE( @unionQuery + ' UNION ALL ', '' ) +
        CONCAT(
            'SELECT ''',
            SCHEMA_NAME, '.', TABLE_NAME, '[Year],
    COUNT(*) AS YearRowCount
FROM
    [', SCHEMA_NAME, '].[', TABLE_NAME, ']
GROUP BY
    [Year]
'
    )
FROM
    INFORMATION_SCHEMA.TABLES
ORDER BY
    SCHEMA_NAME,
    TABLE_NAME

@pivotQuery , REPLACE @unionQuery, , , SQL-

SET @pivotQuery = REPLACE( @pivotQuery, '%s', @unionQuery )