SQL Clone
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
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63131 Visits: 17959
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.

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
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42279 Visits: 19834
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
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63131 Visits: 17959
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.

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
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 1460
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19789 Visits: 7410
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63131 Visits: 17959
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.

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
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

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