0

I have a long list of tables / views, and I just need a count of the records and the name of the object being queried, for example...

SELECT COUNT(*), TableOrViewName FROM TableOrView

UNION ALL

SELECT COUNT(*), TableOrViewName FROM TableOrView

UNION ALL

SELECT COUNT(*), TableOrViewName FROM TableOrView
Christian Phillips
  • 18,399
  • 8
  • 53
  • 82
  • So add the name into the queries `SELECT`: `N'TableOrView' AS TableOrViewName` here, (obviously) `N'TableOrView'` is the *actual* name of the table or `VIEW`. Though, if you want just a `COUNT` of the rows in the tables, there are alternative ways to do this without using `SELECT COUNT(*)`. – Thom A Aug 24 '22 at 09:49
  • Isn't it a job for Sql Server Profiler ? https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver16 You may also find interesting solutions here : https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan-in-sql-server – Raphaël Althaus Aug 24 '22 at 09:51
  • For tables and indexed views, consider using DMVs to get the row count if it doesn't need to be transactionally consistent. This will avoid full scans of the tables. – Dan Guzman Aug 24 '22 at 09:52
  • 1
    See [Bad habits : Counting rows the hard way](https://sqlperformance.com/2014/10/t-sql-queries/bad-habits-count-the-hard-way) – Thom A Aug 24 '22 at 09:53
  • @Larnu, I would need to go through every query to add that, I was hoping there was a function or variable I could use. I could just generate this dynamically but wanted to check first. – Christian Phillips Aug 24 '22 at 09:53
  • You would, which is why I said there are alternative ways; see the above link, @ChristianPhillips . – Thom A Aug 24 '22 at 09:53
  • Does this answer your question? [How to fetch the row count for all tables in a SQL SERVER database](https://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database) – SMor Aug 24 '22 at 10:04
  • A trivial search for "sql server count rows in all tables" will find many suggestions. Refer to the suggestion involving the system tables from Keng – SMor Aug 24 '22 at 10:05
  • I will change the query to use DMV, this is a "one off" query. – Christian Phillips Aug 24 '22 at 10:13
  • @SMor, I don't want all the tables, only a subset of them in different schemas. – Christian Phillips Aug 24 '22 at 10:15
  • Insert your list of tables\views to temp table and then use it with the query from @Larnu comment – Lev Gelman Aug 24 '22 at 10:34

0 Answers0