Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table row count grouped by date Expand / Collapse
Author
Message
Posted Monday, December 16, 2013 8:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 9:25 AM
Points: 11, Visits: 27
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.
Post #1523276
Posted Monday, December 16, 2013 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523287
Posted Monday, December 16, 2013 9:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:40 PM
Points: 3,545, Visits: 7,657
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_Name nvarchar(128),
When date,
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1523290
Posted Monday, December 16, 2013 9:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 9:25 AM
Points: 11, Visits: 27
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

Post #1523306
Posted Monday, December 16, 2013 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523319
Posted Monday, December 16, 2013 10:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 411, Visits: 1,300
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)'

Post #1523336
Posted Monday, December 16, 2013 10:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:25 PM
Points: 2,044, Visits: 3,059
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1523339
Posted Monday, December 16, 2013 10:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523342
Posted Wednesday, December 18, 2013 7:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 9:25 AM
Points: 11, Visits: 27
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
Post #1524142
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse