Table row count grouped by date

  • I'm experiencing a major brain freeze today and I can't figure this out:

    I have several tables and each one contains a "when" column to indicate when the last insert or update was made. What I want to do is go through all the tables in my database, print the name of the table, along with the count of records grouped by "when" (the date, really, I don't care much about the time)

    For example,

    table1 2013-12-12 10

    table1 2013-12-13 13

    table1 2013-12-14 90

    table2 2013-12-15 14

    table3 2013-12-01 12

    Happy Holidays, everyone.

  • You could probably use the undocumented procedure sp_msforeachtable.

    Can't give you much more specifics than that without some details about your tables.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Maybe something like this might help.

    Note that there's commented code that you might need to use to get the correct results.

    CREATE TABLE ##Counts(

    Table_Namenvarchar(128),

    Whendate,

    RecCount int --bigint

    )

    DECLARE @SQL nvarchar(MAX)= ''

    SELECT @SQL = @SQL + 'INSERT INTO ##Counts

    SELECT ''' + TABLE_NAME + ''' '+ TABLE_NAME + ',

    When, --DAETADD( dd, DATEDIFF( dd, 0, When), 0)

    COUNT(*)

    FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']

    GROUP BY When --DAETADD( dd, DATEDIFF( dd, 0, When), 0)' + CHAR(10)

    FROM INFORMATION_SCHEMA.tables

    EXEC( @SQL)

    SELECT * FROM ##Counts

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • My tables are pretty simple:

    create table table1

    (

    col1 int,

    _when datetime default getDate()

    )

    create table table2

    (

    col1 int,

    _when datetime default getDate()

    )

    create table table3

    (

    col1 varchar(10),

    _when datetime default getDate()

    )

    And let's insert some data:

    insert into table1(col1) values(1)

    insert into table1(col1) values(2)

    insert into table1(col1) values(3)

    insert into table2(col1) values(10)

    insert into table2(col1) values(11)

    insert into table2(col1) values(12)

    insert into table3(col1) values('1')

    insert into table3(col1) values('2')

    insert into table3(col1) values('3')

    Because the "_when" column is by default getDate(), I should have a whole bunch of rows that contain data and the time it was inserted.

    What I want to do is to get a count of all rows in a table grouped by the "_when" column, like

    table1 2013-12-12 10 -- table1 had 10 rows inserted on 12/12/2013

    table1 2013-12-13 12 -- table1 had 12 rows inserted on 12/13/2013

    table2 2013-01-01 34 -- table2 had 34 rows inserted on 01/01/2013

  • A slight modification to the fine code Luis posted should work for you. I changed the global temp table to instead use a temp table inside the dynamic sql. This may or not work for you. If you need to get at this data for more than just this select you may want to go back to the global temp table.

    DECLARE @SQL nvarchar(MAX) = 'create table #Temp(TableName sysname, DateMod datetime, MyRowCount bigint);'

    SELECT @SQL = @SQL + 'insert #Temp SELECT ''' + TABLE_NAME + ''' as TableName,

    DATEADD( dd, DATEDIFF( dd, 0, _When), 0),

    COUNT(*)

    FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']

    GROUP BY DATEADD( dd, DATEDIFF( dd, 0, _When), 0);'

    FROM INFORMATION_SCHEMA.tables

    where TABLE_NAME like ('table%') --I added this part to help limit the tables since my test database does not meet your criteria for table structure.

    select @SQL = @SQL + 'select * from #Temp;'

    exec (@SQL)

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • sp_msforeachtable example:

    sp_msforeachtable 'SELECT ''?'' AS ''table_name'',

    CONVERT(VARCHAR, [_when], 112) AS ''_when'',

    COUNT(*) AS ''records''

    FROM ?

    GROUP BY CONVERT(VARCHAR, [_when], 112)'

  • Sean Lange (12/16/2013)


    A slight modification to the fine code Luis posted should work for you. I changed the global temp table to instead use a temp table inside the dynamic sql. This may or not work for you. If you need to get at this data for more than just this select you may want to go back to the global temp table.

    DECLARE @SQL nvarchar(MAX) = 'create table #Temp(TableName sysname, DateMod datetime, MyRowCount bigint);'

    SELECT @SQL = @SQL + 'insert #Temp SELECT ''' + TABLE_NAME + ''' as TableName,

    DATEADD( dd, DATEDIFF( dd, 0, _When), 0),

    COUNT(*)

    FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']

    GROUP BY DATEADD( dd, DATEDIFF( dd, 0, _When), 0);'

    FROM INFORMATION_SCHEMA.tables

    where TABLE_NAME like ('table%') --I added this part to help limit the tables since my test database does not meet your criteria for table structure.

    select @SQL = @SQL + 'select * from #Temp;'

    exec (@SQL)

    There is one mild inconsistency in this code.

    The temp table contains "MyRowCount bigint", but "COUNT(*)" is in the SELECT -- it should be "COUNT_BIG(*)" if a bigint result is desired.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (12/16/2013)


    Sean Lange (12/16/2013)


    A slight modification to the fine code Luis posted should work for you. I changed the global temp table to instead use a temp table inside the dynamic sql. This may or not work for you. If you need to get at this data for more than just this select you may want to go back to the global temp table.

    DECLARE @SQL nvarchar(MAX) = 'create table #Temp(TableName sysname, DateMod datetime, MyRowCount bigint);'

    SELECT @SQL = @SQL + 'insert #Temp SELECT ''' + TABLE_NAME + ''' as TableName,

    DATEADD( dd, DATEDIFF( dd, 0, _When), 0),

    COUNT(*)

    FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']

    GROUP BY DATEADD( dd, DATEDIFF( dd, 0, _When), 0);'

    FROM INFORMATION_SCHEMA.tables

    where TABLE_NAME like ('table%') --I added this part to help limit the tables since my test database does not meet your criteria for table structure.

    select @SQL = @SQL + 'select * from #Temp;'

    exec (@SQL)

    There is one mild inconsistency in this code.

    The temp table contains "MyRowCount bigint", but "COUNT(*)" is in the SELECT -- it should be "COUNT_BIG(*)" if a bigint result is desired.

    Ahh yes. Of course I have no idea if the OP needs bigint or not. The point you are making is to be consistent which my code was not. Thanks for the catch. 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thank you everyone for coming to the rescue! There's just so much to juggle during the holiday season that, inevitably, something falls to the ground. This year it just happened to be my T-SQL.

    The code by Luis, Sean and Scott did the trick. I had to modify it slightly by adding the WHERE filter, like:

    SELECT @SQL = @SQL + 'insert #Temp SELECT ''' + TABLE_NAME + ''' as TableName,

    DATEADD( dd, DATEDIFF( dd, 0, _when), 0) as LastUpdate,

    COUNT(*) as RecordCount

    FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']

    GROUP BY DATEADD( dd, DATEDIFF( dd, 0, _when), 0);'

    FROM INFORMATION_SCHEMA.tables

    WHERE table_type = 'base table'

    because I didn't want views.

    J.Faehrmann mentioned the sp_msforeachtable procedure. Very interesting.

    Have a great holiday everyone!

    John

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply