find the count of rows all tables based on created date

Question

find the count of rows all tables based on created date

mpradeep23

SSCommitted

Points: 1868
More actions
January 15, 2014 at 11:29 pm

#150013

hi
i want to find count of rows in all tables in database based on created
thanks in advance,
Pradeep

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

mpradeep23 (1/15/2014)
hi
i want to find count of rows in all tables in database based on created
thanks in advance,
Pradeep

OK, please provide some sample data, DDL and desired results in consumable format & someone will help. The first link in my signature explains how to do this.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Based on created date of what?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kenny Jozi SSCrazy Points: 2028 More actions · Answer 3

try this , u can change it and use sp_msForEachDB to loop around all databases

SELECT sc.name +'.'+ ta.name TableName

,SUM(pa.rows) RowCnt

FROM sys.tables ta

INNER JOIN sys.partitions pa

ON pa.OBJECT_ID = ta.OBJECT_ID

INNER JOIN sys.schemas sc

ON ta.schema_id = sc.schema_id

WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)

GROUP BY sc.name,ta.name

ORDER BY SUM(pa.rows) DESC

SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

kenneth.mofokeng (1/16/2014)
try this , u can change it and use sp_msForEachDB to loop around all databases
SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

I'm not seeing any references to 'Created Date' here ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mpradeep23 SSCommitted Points: 1868 More actions · Answer 5

i have 53 tables with createddate column

in need to find count of all tables by passing created date as paramater

thanks,

Pradeep

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Count of all tables meaning what? Number of tables where something to do with created date parameter? Number of rows in those tables with something to do with created date parameter? Something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mpradeep23 SSCommitted Points: 1868 More actions · Answer 7

in my database i have 53 tables

in every table it as created date as column

i want a query to find all tables data count on particular date i.e created date

Lowell SSC Guru Points: 323518 More actions · Answer 8

since you know the 53 tables, and you know the date, i think this is just a big UNION query:

SELECT 'Table1',count(*) FROM Table1 WHERE CreateDate > '2014-01-01' UNION ALL

SELECT 'Table2',count(*) FROM Table2 WHERE CreateDate > '2014-01-01' UNION ALL

...

SELECT 'Table53',count(*) FROM Table53 WHERE CreateDate > '2014-01-01'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

Something like this would help?

DECLARE @Datedate = GETDATE(),

@sql nvarchar(max)

SELECT @sql = (SELECT 'SELECT ''' + TABLE_NAME + ''', COUNT(*) FROM ' + TABLE_NAME + ' WHERE CreateDate >= ''' + CONVERT( char(8), @Date, 112) + '''' + CHAR(13)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME = 'CreateDate'

FOR XML PATH(''),Type).value('.','Varchar(max)')

PRINT @sql

EXEC sp_executesql

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

dallas13 SSCertifiable Points: 7491 More actions · Answer 10

Hello Luis Cazares Sorry for replying to an older post but I need something like this and inserted to some temporary table rather than individual results. Can you help? Thanks a lot

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

dallas13 - Wednesday, April 25, 2018 10:42 AM
Hello Luis Cazares Sorry for replying to an older post but I need something like this and inserted to some temporary table rather than individual results. Can you help? Thanks a lot

This should get you started.

DECLARE @Date date = GETDATE(), @sql nvarchar(max)

SELECT @sql = (SELECT 'SELECT ''' + TABLE_NAME + ''', COUNT(*) FROM ' + TABLE_NAME + ' WHERE CreateDate >= ''' + CONVERT( char(8), @Date, 112) + '''' + CHAR(13) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CreateDate' FOR XML PATH(''),Type).value('.','Varchar(max)')

PRINT @sql;

CREATE TABLE #ReturnData (TableName sysname, RowCnt bigint);

INSERT INTO #ReturnData EXEC sp_executesql;

SELECT * FROM #ReturnData;

dallas13 SSCertifiable Points: 7491 More actions · Answer 12

Hello Lynn Pettis... That worked. Seems very simple. Thanks a lot.