Q:

Get row count for all tables across dbs and pivot sql server 2012

I am using the following script to get a row count for all databases
These databases are all identical in structure.
USE [MyTestDb1];

SELECT 
    'MyTestDb1'as [Database Name], so.name AS [Table Name],   
    rows AS [RowCount]   
FROM 
    sysindexes AS si   
JOIN 
    sysobjects AS so on si.id = so.id   
WHERE 
    indid IN (0,1)   
    AND xtype = 'U'

USE [MyTestDb2];

SELECT 
   'MyTestDb2'as [Database Name], so.name AS [Table Name],   
    rows AS [RowCount]   
FROM 
    sysindexes AS si   
JOIN 
    sysobjects AS so on si.id = so.id   
WHERE 
    indid IN (0,1)  
    AND xtype = 'U'

At run time from c# I build the sql script depending how many dbs I am comparing and then execute.
Given that I want to compare the rows across many dbs on the same server
I cannot seem to achieve the following output.I believe is a pivot what I need 
but cannot seem to work it out.
TableName   Db1     Db2   Db3    Db4
------------------------------------
Table1      10      10    10     10
Table2      13      13    13     13
Table3      10      10    10     10
Table4      10      10    10     10
Table5      10      10    10     10

Any suggestion/help on how to return in the above format?
Many thanks.

A:

Yes. You need PIVOT at this point.
First of all declare a variable to get column names dynamically
DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + [Database Name] + ']', '[' + [Database Name] + ']')
               FROM    
               (
                    SELECT DISTINCT 'MyTestDb1'as [Database Name]   
                    FROM sysindexes AS si   
                        join sysobjects AS so on si.id = so.id   
                    WHERE indid IN (0,1)   
                    AND xtype = 'U'
                ) PV 
               ORDER BY [Database Name]

Now pivot the result as database names in columns and table names in rows.
DECLARE @query NVARCHAR(MAX)
SET @query = '-- This outer query forms your pivoted result
             SELECT * FROM 
             (
                SELECT ''MyTestDb1'' as [Database Name], so.name AS [Table Name],   
                rows AS [RowCount]   
                FROM sysindexes AS si   
                    join sysobjects AS so on si.id = so.id   
                WHERE indid IN (0,1)   
                AND xtype = ''U''
             ) x
             PIVOT 
             (
                 --Defines the values in each dynamic columns
                 MIN([RowCount])
                 -- Get the names from the @cols variable to show as column
                 FOR [Database Name] IN (' + @cols + ')
            ) p            
            ORDER BY [Table Name];' 

EXEC SP_EXECUTESQL @query

EDIT : 
I have inserted the data to temporary table for the easy understanding. CTE1 converts csv to rows, CTE2 gets the data from sys..... In the last part, we use CROSS JOIN as there is no need of a INNER JOIN as per your requirement.
DECLARE @PARAM VARCHAR(MAX)='DB1 | DB2 | DB3'

;WITH CTE1 AS
(
    SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Database Name' 
    FROM  
    (         
         SELECT CAST ('<M>' + REPLACE(@PARAM, '|', '</M><M>') + '</M>' AS XML) AS Data              
    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)
)
,CTE2 AS
(
    SELECT so.name AS [Table Name],   
        rows AS [RowCount]   
    FROM sysindexes AS si   
        join sysobjects AS so on si.id = so.id   
    WHERE indid IN (0,1)   
    AND xtype = 'U'
)
SELECT [Database Name],[Table Name],[RowCount]
INTO #NEWTABLE
FROM CTE1 
CROSS JOIN CTE2

Now declare the variable to get columns for pivot
DECLARE @cols NVARCHAR (MAX)

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(DatabaseName) 
            FROM 
            (
                SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'DatabaseName' 
                FROM  
                (         
                     SELECT CAST ('<M>' + REPLACE(@PARAM, '|', '</M><M>') + '</M>' AS XML) AS Data              
                ) AS A 
                CROSS APPLY Data.nodes ('/M') AS Split(a)
            ) c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

Now pivot the query
DECLARE @query NVARCHAR(MAX)
SET @query = '
SELECT * 
FROM
(
    SELECT [Database Name],[Table Name],[RowCount]
    FROM #NEWTABLE  
)TAB
PIVOT 
(
     MIN([RowCount])
     for [Database Name] in (' + @cols + ')
) as P' 

EXEC SP_EXECUTESQL @query

