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.
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'?
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.