SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counting records over several tables


Counting records over several tables

Author
Message
Darryl Meades-418843
Darryl Meades-418843
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 7
Hi - still a newbie, still trying to learn! Wink

Hopefully you can help me with this one. In my database there are several tables (around 90 to be exact).

Each table contains the column 'RECORD_ModificationDate'.

I would like to query each table and count the number of records that have a 'RECORD_ModificationDate' of after, say, midnight (still trying to work out what the best time is).

Ideally the output needs to contain the table name, followed by the number of records.

What is the quickest way of getting this info?

Many thanks for your help.
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57367 Visits: 9730

select 'Table1' as TableName, count(*) as Qty -- First Table
from dbo.Table1
where RECORD_ModificationDate >= @StartDate_in
and RECORD_ModificationDate < @EndDate_in

union all

select 'Table2' as TableName, count(*) -- Second Table
from dbo.Table2
where RECORD_ModificationDate >= @StartDate_in
and RECORD_ModificationDate < @EndDate_in

union all

-- Duplicate query for each table




Is one way to do it pretty efficiently. I'm defining @StartDate_in and @EndDate_in as datetime input parameters. You can put whatever is appropriate for your query in the Where clauses.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Steve Jones
Steve Jones
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: Administrators
Points: 145621 Visits: 19425
Why would you need to know how many records in every table have been changed? That seems strange. I could see a few of them, but wouldn't lots of tables be unrelated?

In any case, you can union up the data as suggested above, querying each table. That might take a long time, and be cumbersome.

I'd probably build a temp table or build a real table, and then have a stored procedure that queries each table separtely, inserts the data (table name, count, and current GETDATE() ) into this table that you can query. It gives you history, though you can manage that and delete older data regularly. It also lets you stagger the load, potentially keep going if you have issues (set up try..catch blocks) and better manage things than a single query.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
matt.mallen
matt.mallen
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 224
This is a possible solution. I am not sure how to avoid it, but I'm sure someone will post another solution that doesn't go RBAR which would definitely be preferable.


CREATE TABLE #TableNames (TableName VARCHAR(100))
CREATE TABLE #RowCounts (TableName VARCHAR(100), NumRows INTEGER)

DECLARE @vSELECT VARCHAR(1000)
DECLARE @vFROM VARCHAR(1000)
DECLARE @vWHERE VARCHAR(1000)
DECLARE @vINSERT VARCHAR(1000)
DECLARE @vTableName VARCHAR(100)

--get all of the tables with a column named RECORD_ModificationDate
INSERT INTO #TableNames(TableName)
SELECT SO.Name
FROM sysobjects SO
WHERE Xtype='U' AND EXISTS( SELECT TOP 1 SC.Name
FROM syscolumns SC
WHERE SC.id=SO.id AND SC.Name='RECORD_ModificationDate')

--the insert and where clauses won't changed based
SET @vINSERT = 'INSERT INTO #RowCounts(TableName, NumRows) '
SET @vWHERE = 'WHERE RECORD_ModificationDate > CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)'

WHILE EXISTS(SELECT TOP 1 * FROM #TableNames)
BEGIN
--get the name of the first table that has not already been counted
SET @vTableName = (SELECT TOP 1 TableName FROM #TableNames)

--update select and from statements
SET @vSELECT = 'SELECT '''+@vTableName+''' AS TableName, COUNT(1) AS NumRows '
SET @vFROM = 'FROM '+@vTableName+' '

--execute the combined insert/select/from/where statements
EXEC (@vINSERT+@vSELECT+@vFROM+@vWHERE)

--remove the table name from the table to signify it has already been counted
DELETE FROM #TableNames WHERE TableName=@vTableName
END

--return the counts
SELECT * FROM #RowCounts

--cleanup after yourself
DROP TABLE #TableNames
DROP TABLE #RowCounts



*edit* I forgot to close the parentheses on the exec
Darryl Meades-418843
Darryl Meades-418843
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 7
Dear all,

Thank you very much for your replies, your help is greatly appreciated.
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