December 16, 2013 at 8:43 am
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.
December 16, 2013 at 9:04 am
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/
December 16, 2013 at 9:12 am
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
December 16, 2013 at 9:28 am
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
December 16, 2013 at 9:46 am
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/
December 16, 2013 at 10:16 am
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)'
December 16, 2013 at 10:36 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 16, 2013 at 10:42 am
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/
December 18, 2013 at 7:53 am
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