CODEWITHSUNDEEP
sqlsql-server-2008
user3712641
user3712641

Reputation: 149

Script that finds the number of rows per year for all date columns

I would like to create a script that will return the number rows per year for all dates columns from all the tables in the database.

For example, if we have transactional tableX with N columns , so I need dynamic script which will loop through all columns in a table and find columns with DATE or DATETIME data type. After that it will run through the list of date type columns and return the number of rows per YEAR(with name ex.ColumnDate)

Output: 
Table Name1:
DateColumn1:
Year    Number Rows


DateColumnM:
Year    Number Rows


Table NameN:
DateColumn1:
Year    Number Rows


DateColumnM:
Year    Number Rows

Upvotes: 0

Views: 84

Answers (2)

Richard Hansell
Richard Hansell

Reputation: 5403

This is a much more long-winded approach, but I think it gives you what you are looking for?

Run the following query in the database you want to report on:

--Make a list of tables to target
DECLARE @HitList TABLE (
    Id INT IDENTITY(1,1),
    TableName VARCHAR(1024),
    SchemaName VARCHAR(1024),
    ColumnName VARCHAR(1024));
INSERT INTO
    @HitList
SELECT
    t.name,
    s.name,
    c.name 
FROM 
    sys.columns c 
    INNER JOIN sys.tables t ON t.object_id = c.object_id
    INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE 
    c.system_type_id = 61; --DATETIME

--Now get the count of rows per year for each table in the list
DECLARE @Id INT = 1;
DECLARE @Count INT;
SELECT @Count = COUNT(*) FROM @HitList;
CREATE TABLE #Results (
    Id INT,
    [Year] INT,
    [RowCount] INT);
WHILE @Id <= @Count
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SELECT 
        @SQL = 'INSERT INTO #Results SELECT ' + CONVERT(VARCHAR(50), @Id) + ', YEAR([' + h.ColumnName + ']), COUNT(*) FROM [' + h.SchemaName + '].[' + h.TableName + '] GROUP BY YEAR([' + h.ColumnName + ']) ORDER BY 1;'
    FROM
        @HitList h
    WHERE
        Id = @Id;
    EXEC(@SQL);
    SELECT @Id = @Id + 1;
END;

--Output the results
SELECT
    h.TableName + '.' + h.SchemaName + '.' + h.ColumnName AS [Table],
    r.[Year],
    r.[RowCount]
FROM
    @HitList h
    INNER JOIN #Results r ON r.Id = h.Id
ORDER BY
    h.TableName,
    h.ColumnName,
    r.[Year];

For example, I ran this against one of my SQL Server 2008 databases to get these (partial) results:

Table                                           Year    RowCount
ElecConsumptionForecast.Staging.LastReadingDate 2008    1680
ElecConsumptionForecast.Staging.LastReadingDate 2009    7978
ElecConsumptionForecast.Staging.LastReadingDate 2010    15398
ElecConsumptionForecast.Staging.LastReadingDate 2011    28769
ElecConsumptionForecast.Staging.LastReadingDate 2012    35682
ElecConsumptionForecast.Staging.LastReadingDate 2013    59047
ElecConsumptionForecast.Staging.LastReadingDate 2014    127668

Upvotes: 0

pmbAustin
pmbAustin

Reputation: 3970

You didn't specify for which SQL Server, so I'm assuming Microsoft SQL Server, 2008 or later...

This code does what you need I think... just replace "MyTable" with the table name you're interested in:

DECLARE @sql varchar(2000);
DECLARE @tableName sysname;
DECLARE @columnName sysname;

DECLARE theCursor CURSOR FOR
    SELECT TABLE_NAME, COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = 'MyTableName'
       AND DATA_TYPE = 'datetime' FOR READ ONLY;

OPEN theCursor;
FETCH NEXT FROM theCursor INTO @tableName, @columnName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'SELECT ''' + @tableName + ''' as [TabelName], ' + 
               '''' + @columnName + ''' AS [ColumnName], ' +
               'DATEPART(yy, ' + QUOTENAME(@columnName) + 
               ') AS [Year], COUNT(1) AS [Count] FROM ' + QUOTENAME(@tableName) + 
               ' GROUP BY DATEPART(yy, ' + QUOTENAME(@columnName) + ')';
    PRINT @sql;
    EXEC (@sql);
    FETCH NEXT FROM theCursor INTO @tableName, @columnName;
END
CLOSE theCursor
DEALLOCATE theCursor;

Upvotes: 1

Related Questions