sp_MSforeachdb against multiple databases

Question

Hi,

I am using the following query using sp_MSforeachdb to get the table count of All the tables in specific databases, say about db1, db2, db3 & db4.

This query working fine, But, On the result set I would like to include a column for the Database name.

Including the ''?'' as one of the column is providing some errors and throwing errors on the other databases on the instance except db1, db2, db3 & db4.

Help to include the Database column is very much appreciated and thanks in advance.

------ Script to get all User table counts on specific databases ------ Requirement : Need to include the Database Name on the result set.

create table #hd_rc ( objectid VARCHAR(100),tname VARCHAR(100),row_cnt  INT )
DECLARE @command varchar(1000)

SELECT @command = 'IF ''?'' IN(''db1'', ''db2'', ''db3'', ''db4'') 
                      BEGIN USE ? EXEC(''
    INSERT INTO #hd_rc                      
           SELECT o.object_id,
           o.name,
           ddps.row_count
    FROM   sys.indexes AS i
           INNER JOIN sys.objects AS o
                   ON i.object_id = o.object_id
           INNER JOIN sys.dm_db_partition_stats AS ddps
                   ON i.object_id = ddps.object_id
                      AND i.index_id = ddps.index_id
    WHERE  i.index_id < 2
           AND o.is_ms_shipped = 0;     
    '')     
    END' 
    EXEC sp_MSforeachdb @command

Answer 1

Just a quick and dirty solution, but it may fit your needs. I've added a column in the temp table and just using dbname()

create table #hd_rc ( dbname sysname, objectid VARCHAR(100),tname VARCHAR(100),row_cnt  INT )
DECLARE @command varchar(1000)

SELECT @command = 'IF ''?'' IN(''Adventureworks2012'', ''Adventureworks2008R2'', ''db3'', ''db4'') 
                      BEGIN USE ? EXEC(''
    INSERT INTO #hd_rc                      
           SELECT db_name(),o.object_id,
           o.name,
           ddps.row_count
    FROM   sys.indexes AS i
           INNER JOIN sys.objects AS o
                   ON i.object_id = o.object_id
           INNER JOIN sys.dm_db_partition_stats AS ddps
                   ON i.object_id = ddps.object_id
                      AND i.index_id = ddps.index_id
    WHERE  i.index_id < 2
           AND o.is_ms_shipped = 0;     
    '')     
    END' 
    EXEC sp_MSforeachdb @command 


 select * from #hd_rc

 drop table #hd_rc

the result will look like this: alt text

Regards Dirk

Answer 2

I like Dirk's answer (+1 to him), but I think this may be more what you were expecting:

create table #hd_rc ( objectid VARCHAR(100),tname VARCHAR(100),row_cnt  INT, dbname varchar(128) )
DECLARE @command varchar(1000)

SELECT @command = 'IF ''?'' IN(''TestDb'', ''TestDb2'') 
                      BEGIN USE ? EXEC(''
    INSERT INTO #hd_rc                      
           SELECT o.object_id,
           o.name,
           ddps.row_count,
           ''''?'''' as dbname
    FROM   sys.indexes AS i
           INNER JOIN sys.objects AS o
                   ON i.object_id = o.object_id
           INNER JOIN sys.dm_db_partition_stats AS ddps
                   ON i.object_id = ddps.object_id
                      AND i.index_id = ddps.index_id
    WHERE  i.index_id < 2
           AND o.is_ms_shipped = 0;     
    '')     
    END' 
    EXEC sp_MSforeachdb @command 

select * from #hd_rc

The tricky part is you need 4 single quotes on either side of the ? in the select statement because it is already inside of two layers of nested quotes (one starts before the if, the other starts after Exec).

Something like this:

exec sp_msforeachdb 'select ''?'' as dbname'

works with just two becuase it is only inse of one layer of quotes at that point.

sp_MSforeachdb against multiple databases

Follow this question

Related Questions