0

I have a database with a bunch of tables.

I want to build a query, that lists all the tables in the database, and then do various counts in those tables.

For example count "How many rows that has 'D' as country" etc.

I have figured out to do a SELECT name FROM db.sys.tables to get all the tables, just can't wrap my head around the situation that I want to do counts on the tables also.

Its the same count I want to do in all tables.

ughai
  • 9,830
  • 3
  • 29
  • 47
user2199192
  • 165
  • 1
  • 2
  • 10
  • 1
    possible duplicate of [Query to list number of records in each table in a database](http://stackoverflow.com/questions/1443704/query-to-list-number-of-records-in-each-table-in-a-database) – ughai Jun 22 '15 at 11:22
  • Not excactly, it just count the rows from all the tables, I want to set up my own counts, like how many rows with a value of DK and more. – user2199192 Jun 22 '15 at 11:31
  • Wouldn't you just do a `Select Count(*) from... WHERE...` at that point? – jradich1234 Jun 22 '15 at 11:35
  • Because there is 100 tables - So I want to do it in one query, instead of creating the count for each and every one of the tables. – user2199192 Jun 22 '15 at 11:39
  • "How many rows that has `DK` as country". what if that `country` column is not present in all the tables or does not have the same type? – ughai Jun 22 '15 at 11:42
  • Then I should reconstruct the database and tables;) The data is there and it is perfekt - just need the query to query it;) – user2199192 Jun 22 '15 at 11:44
  • what version of SQL? – Pixelated Jun 22 '15 at 11:48

1 Answers1

2

You can use the undocumented system procedure sp_msforeachtable like this

DECLARE @results TABLE(tbl VARCHAR(100),countresult INT);

INSERT INTO @results
EXEC sp_msforeachtable @command1 = 'SELECT ''?'',Count(*) FROM ? /* Add your WHERE clause here*/',@replacechar = '?'

SELECT * FROM @results

You can read more about it here and here

ughai
  • 9,830
  • 3
  • 29
  • 47