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.
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.
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'?
sp_msforeachtable 'select ''?'', count(*) from ? where datum between ''2010-01-27'' and ''2014-01-27'''
I would also be inclined to wrap the ? in `from ?` in square brackets, just in case there's a dodgy tablename. IE: ... COUNT(*) FROM [?] ...
Replace 'datum' with the correct columnname. Mind the double quotes and 3 quotes at the end of this statement
On SQL 2012, ? is replaced by [schema].[tablename] so additional brackets are not necessary. (can't test it on previous versions at this moment)
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.
17 People are following this question.
