question

ankushneedsyou avatar image
ankushneedsyou asked

SQL count

i need to get sql server database all tables row count along with min(record with min date like 27 jan 2010) and max(max date record in that table like 27 jan 2014).can someone help how can it be done. the purpose is to save time running queries like select min(datetime) from t1 and select max(datetime) from t1. your help will be truly appreciated.
querymaxrecord
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
You can get all table row counts from sys.partitions, but be aware that this may not be 100% accurate due to any transactions that are in-flight when you run your query, but it may be close enough for what you need. To get the min and max dates, you are going to have to either dynamically construct some sql and execute that, or use undocumented features like sp_MSforeachtable, but the output from that is less controllable - so I'd be more inclined to generate the sql I need - that way I could exclude tables that don't have the date fields. But it still requires you to run the query against each table - how do you want to 'save time'?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
sp_msforeachtable 'select ''?'', count(*) from ? where datum between ''2010-01-27'' and ''2014-01-27'''
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I would also be inclined to wrap the ? in `from ?` in square brackets, just in case there's a dodgy tablename. IE: ... COUNT(*) FROM [?] ...
2 Likes 2 ·
Replace 'datum' with the correct columnname. Mind the double quotes and 3 quotes at the end of this statement
0 Likes 0 ·
On SQL 2012, ? is replaced by [schema].[tablename] so additional brackets are not necessary. (can't test it on previous versions at this moment)
0 Likes 0 ·
Venkataraman avatar image
Venkataraman answered
SELECT ' SELECT count(*),min(modifiedAt),max(modifiedat) FROM ' + TABLE_SCHEMA + '.' + table_name FROM information_schema.tables Assuming ModifiedAt as the datetime column in each table of the database.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.