Fetch row count of specific table against all database in SQL Server

Below query will list out the count of rows against your SQL server all databases.
And If you want to exclude any Database then please mention them in @DBName variable.

Declare @MaxRow int, @MinRow int =1, @Sql nvarchar(Max), @DbName as nvarchar(Max)

Create table #B(RowNum  Bigint, DBName nvarchar(Max))

SELECT name, row_number() over(order by name ) as rowid into #A FROM master.dbo.sysdatabases

SET @MaxRow = (SELECT MAx(rowid) from #A);

WHILE (@MaxRow>@MinRow)

BEGIN

    SET @DbName= (Select name from #A where rowid = @MinRow);

    If (@DbName not in ('master', 'tempdb', 'model', 'msdb'))

    BEGIN

          SET @Sql = 'Use ['+@DbName +']

                           --GO

                       Select Count(1) as Cnt, '''+@DbName+''' as DBName from ['+@DbName+'].[dbo].[tblData] with(nolock)

                       --GO

                       '

          --print @Sql;

        Insert into #B  EXEC (@Sql) ;

    END

    SET @MinRow = @MinRow+1 ;

END

 Select * from #B order by RowNum DESC

 Drop table #A

 Drop table #B

 This query can be modified more but for and we avoid the use of temp table but for now its ok. If i or someone find any issue with SQL performance then i will update this blog with more optimized query,