CODEWITHSUNDEEP
sqlsql-servert-sql
Iballan
Iballan

Reputation: 23

Count rows in all tables, that meets conditions

I have database with 7,000 tables, most of these have a column DataAreaId, but not all tables, since some are global.

I would like to list all tables that have the column DataAreaId and their row count where the column DataAreaId contains "FR".

So for one table it would be:

SELECT COUNT(*)
FROM Table
WHERE DataAreaId = 'FR'

Any suggestions?

Upvotes: 0

Views: 716

Answers (3)

GuidoG
GuidoG

Reputation: 12014

One way to do this is to query all tables containing that column, and build a query statement for each

select 'union all select ' + QUOTENAME(t.name,N'''') + ', count(1) from ' + t.name + ' where Dataareaid = ''FR'''
from sys.columns c
  join sys.tables t ON c.object_id = t.object_id
where c.name = 'Dataareaid'

each row would look like this

union all select 'SomeTable', count(1) from SomeTable where Dataareaid = 'FR'

Now just put all statements together and remove the first union all

Upvotes: 0

TJB
TJB

Reputation: 877

My answer gets the column metadata, executes the statement in a loop and publishes the results to a table variable:

if object_id('tempdb..#LoopList') is not null
    drop table #LoopList

select 
s.[name] as SchemaName
,t.[name] as TableName
,row_number() over (order by t.[object_id] asc) as RowNumber
into #LoopList
from sys.columns as c
inner join sys.tables as t
    on c.[object_id] = t.[object_id]
inner join sys.schemas as s
    on t.[schema_id] = s.[schema_id]
where c.[name] = 'Dataareaid'

declare 
    @a int = 1
    ,@b int = (select max(RowNumber) from #LoopList)
    ,@c nvarchar(max)
    ,@d nvarchar(max)
    ,@e int


declare @count table (RowCounter int)

declare @resultsTable table (TableName nvarchar(500), RowCounter int)


while @a <= @b

begin

    delete from @count
    set @c = concat ((select quotename(SchemaName) from #LoopList where RowNumber = @a)
                    ,'.'
                    ,(select quotename(TableName) from #LoopList where RowNumber = @a)
                    )

    set @d = concat(N'select count(*) from ' 
                    ,@c
                    ,N' where Dataareaid = ''FR'''
                    )
    insert into @count (
            RowCounter
            )
    exec sp_executesql @d

    set @e = (select top 1 RowCounter from @count)

    insert into @resultsTable (
        TableName
        ,RowCounter
        )
    values (@c,@e)

    set @a += 1;

end

select * from @resultsTable

Upvotes: -1

Ilyes
Ilyes

Reputation: 14928

You can use the following

CREATE TABLE T1(
  Dataareaid  VARCHAR(45)
);

CREATE TABLE T2(
  Dataareaid  VARCHAR(45)
);

INSERT INTO T1 VALUES
('FR'),
('ALG'),
('FR');

DECLARE @SQL NVARCHAR(max) = N'';

SELECT @SQL = (
   SELECT CONCAT(
        N'UNION ALL ',
        N'SELECT ''',
        t.name,
        N''' AS TableName, ',
        N'Cnt = (SELECT COUNT(1)',
        ' FROM ',
        QUOTENAME(t.name),
        N' WHERE [Dataareaid] = ''FR'')'
   )
   FROM sys.columns c
   JOIN sys.tables t ON c.object_id = t.object_id
   WHERE c.name = 'Dataareaid'
   FOR XML PATH('')
)
SET @SQL = STUFF(@SQL, 1, 10, N'');

EXEC sp_executesql @SQL;

Returns:

+-----------+-----+
| TableName | Cnt |
+-----------+-----+
| T1        |   2 |
| T2        |   0 |
+-----------+-----+

Live Demo

Upvotes: 4

Related Questions