x

sp_MSforeachdb against multiple databases

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 
more ▼

asked Sep 27, 2012 at 09:45 AM in Default

skan2dan1 gravatar image

skan2dan1
210 11 13 14

Just a warning - sp_msforeachDB is buggy and has a habit of missing databases. You would be better off dumping a list of database into a table variable and iterating through that to be sure you aren't missing anything.
Sep 27, 2012 at 05:42 PM SirSQL
Sep 27, 2012 at 05:49 PM DirkHondong
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

result.jpg (37.9 kB)
more ▼

answered Sep 27, 2012 at 10:06 AM

DirkHondong gravatar image

DirkHondong
1.3k 15 17 19

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Sep 27, 2012 at 07:54 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

Nice one.
Sep 28, 2012 at 08:31 AM DirkHondong
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x45

asked: Sep 27, 2012 at 09:45 AM

Seen: 2718 times

Last Updated: Sep 28, 2012 at 08:31 AM