CODEWITHSUNDEEP
sqlsql-server
I A Khan
I A Khan

Reputation: 8869

How to get count across multiple tables

I am using this code to get ABC count from all tables having 72 table

if I use

declare @SQL nvarchar(max)
declare @Countt bigint

SELECT @SQL = STUFF(( SELECT ' ; SELECT COUNT(ABC) FROM   ' + INFORMATION_SCHEMA.TABLES.TABLE_NAME 

FROM INFORMATION_SCHEMA.TABLES LEFT OUTER JOIN  INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME where INFORMATION_SCHEMA.TABLES.TABLE_TYPE =N'BASE TABLE' AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME =N'ABC'
FOR XML PATH('')),1,2,'')

SET @SQL = @SQL

PRINT @SQL

EXECUTE (@SQL)

but I am getting 72 results one by one but I just want to get sum of all 72 results,for example if ABC have 10 rows in 4 Tables so it should be return 40 please suggest where I am wrong or any other better way

Upvotes: 1

Views: 449

Answers (4)

Aheho
Aheho

Reputation: 12821

Use a cursor and iterate over each table one by one. When you generate your dynamic sql string, select the results into a variable like so:

    select @TableCount = Count(ABC) From SomeTable
    set @TotalCount = @Totalcount + @TableCount

Upvotes: -1

Darka
Darka

Reputation: 2768

Everyone is right just need to add schema if there is different ones:

declare @SQL nvarchar(max)
declare @Countt bigint



SELECT @SQL = STUFF(( 

SELECT DISTINCT ' UNION ALL SELECT COUNT(ABC) AS CountAmount FROM   ' + INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA + '.' + INFORMATION_SCHEMA.TABLES.TABLE_NAME AS [text()]
FROM INFORMATION_SCHEMA.TABLES 
LEFT OUTER JOIN  INFORMATION_SCHEMA.COLUMNS 
    ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME 
WHERE INFORMATION_SCHEMA.TABLES.TABLE_TYPE =N'BASE TABLE' 
    AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME =N'ABC'
FOR XML PATH('')),1,11,'')

SET @SQL = 'SELECT SUM( CountAmount ) AS TotalSum FROM (' + @SQL + ' ) AS T '

PRINT @SQL

EXECUTE (@SQL)

Upvotes: 2

Sabin B
Sabin B

Reputation: 293

declare @SQL nvarchar(max)
declare @Countt bigint

SELECT @SQL = STUFF(( SELECT ' UNION ALL SELECT COUNT(ABC) AS noCount FROM   ' + INFORMATION_SCHEMA.TABLES.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES LEFT OUTER JOIN  INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME where INFORMATION_SCHEMA.TABLES.TABLE_TYPE =N'BASE TABLE' AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME =N'ABC' FOR XML PATH('')),1,10,'')

SET @SQL = 'SELECT COUNT(*) FROM  (' + @SQL + ')A'

PRINT @SQL

EXECUTE (@SQL)

Upvotes: 2

Robbington
Robbington

Reputation: 11

You need to use an aggregate function and group your results. So if Ive read your sql correctly, group by INFORMATION_SCHEMA.TABLES.TABLE_NAME

Upvotes: -1

Related Questions