Query with Group by across all tables in database

Question

I have a database (lets call it DB) containing 150+ tables, (eg: table1, table2 etc.)

Problem

I want to loop through all tables and get count of rows by groups, as below

Current Approach

As of now I was thinking of appending all tables or doing so manually!

Table structure

name code
A    code1
A    code2
A    code6
A    code98
B    code1

Expected Output

table_name name code   count
table1     A    code1  100
table1     B    code2  941
table2     A    code1  98

Code for each table

SELECT name, code, count (*) AS count 
FROM table1
GROUP BY name, code

Possible duplicate of [Get row count of all tables in database: SQL Server](https://stackoverflow.com/questions/22548938/get-row-count-of-all-tables-in-database-sql-server) — SMor, Oct 25 '17 at 04:43

score 1 · Answer 1 · answered Oct 25 '17 at 04:16

1

You can use the SP ForEachTable that will execute the code against each table stored in your database. Try something like this:

sp_MSforeachtable @command1="select '?' AS TABLE_NAME,count(*) from ?"

answered Oct 25 '17 at 04:16

Erikson T F

33
4

Thank you for this, I will try this today and let you know! – Anubhav Dikshit Oct 25 '17 at 04:36

AB_87 · Accepted Answer · 2017-10-25T05:24:30.987

1

As you want result a single result set, below will work:

CREATE TABLE #Temp
(
tableName VARCHAR(100)
,name VARCHAR(10)
,code VARCHAR(10)
, [Count] INT
)

EXEC sys.sp_MSforeachtable @command1=" insert into #Temp select '?' AS tableName,Name,Code,count(*) from ? group by Name, Code"

SELECT * FROM #Temp
DROP TABLE #temp

Just as an alternative, using dynamic sql:

CREATE TABLE #Temp
(
tableName VARCHAR(100)
,name VARCHAR(10)
,Code VARCHAR(10)
, [Count] INT
)

DECLARE @TableName VARCHAR(100)

DECLARE tableCursor CURSOR FAST_FORWARD FOR
SELECT name FROM sys.tables WHERE type ='U'

OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName


WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @Query NVARCHAR(MAX)= N'select @table as TableName, Name, Code,Count(*) as [Count] from  '+  @tablename + ' Group by Name, Code'
INSERT INTO #Temp
EXEC sp_executesql @Query,  N'@table varchar(100)', @table = @TableName
FETCH NEXT FROM tableCursor INTO @TableName

END
CLOSE tableCursor
DEALLOCATE tableCursor
SELECT * FROM #temp

DROP TABLE #temp

edited Oct 25 '17 at 05:24

answered Oct 25 '17 at 04:51

AB_87

1,126
8
18

Thank you for this, if its not too much to ask (will be happy to upvote) can you tell if what if I want to query only selected table, like run query only for table1, table46, table 23 etc. – Anubhav Dikshit Oct 26 '17 at 03:35
you can filter required tables while setting up your cursor. `SELECT name FROM sys.tables WHERE type ='U' AND name IN ('table1', 'table46', 'table 23')` – AB_87 Oct 26 '17 at 03:38
No worries. You can achieve the same with `EXEC sys.sp_MSforeachtable @command1=' insert into #Temp select ''?'' AS tableName,Name,Code,count(*) from ? group by Name, Code' , @whereand = ' And Object_id In (Select Object_id From sys.tables Where name in (''temp1'', ''temp3'', ''table 23''))'` – AB_87 Oct 26 '17 at 04:23

Query with Group by across all tables in database

2 Answers2