Iterate databases that contain a target table that has one or more records

originally posted 2014-12-29

1	originally posted 2014-12-29

Note: This T-SQL uses SQLCMD variables which can be enabled in SQL Server Management Studio.
Enable SQLCMD mode : Tools -&gt; Options -&gt; Query Execution -&gt; Check the box "By default, open new queries in SQLCMD mode".

1 2	Note: This T-SQL uses SQLCMD variables which can be enabled in SQL Server Management Studio. Enable SQLCMD mode : Tools -> Options -> Query Execution -> Check the box "By default, open new queries in SQLCMD mode".

:setvar TABLENAME "tblAttachments"
sp_MSforeachdb 'use [?]; 
if exists(select * from sys.tables where name = ''$(TABLENAME)'')
if exists (select count(*) from [$(TABLENAME)] having count(*) &amp;gt; 0)
  select [Db] = ''?'', [$(TABLENAME) Records] = count(*) from [$(TABLENAME)]';

1

2

3

4

5

:setvar TABLENAME "tblAttachments"

sp_MSforeachdb 'use [?];

if exists(select * from sys.tables where name = ''$(TABLENAME)'')

if exists (select count(*) from [$(TABLENAME)] having count(*) &gt; 0)

select [Db] = ''?'', [$(TABLENAME) Records] = count(*) from [$(TABLENAME)]';