Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table row count grouped by date


Table row count grouped by date

Author
Message
jhom
jhom
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
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)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18130
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.
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
jhom
jhom
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
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)
J.Faehrmann
J.Faehrmann
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 1438
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)'


ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3944 Visits: 6680
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
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)
jhom
jhom
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search