[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL -- display the Rowcount  for two different databases of corresponding tables

Posted on 2012-09-19
5
Medium Priority
?
397 Views
Last Modified: 2012-09-20
I have two database  with same number of tables and corresponding name.
I like to generate a table to show rowscount for each table from two different tables
How can I do that?

The result should look like that


Tables     RowCounts For DB1        RowCounts For DB2
=============================================
T1              2010                                     2303
T2               190                                          190
0
Comment
Question by:tommym121
5 Comments
 

Expert Comment

by:KatieAndEmil
ID: 38416567
You can use information_schema.tables to get all tables from a database and then use stored procedure / some code to execute SQL (using exec(@SQL)) below is example without proper variables

SELECT
 (SELECT COUNT(*) FROM schemaname.tablename) as RowCounts ForDB1,
 (SELECT COUNT(*) FROM db2.schemaname.tablename) as RowCounts for DB2

table name is a variable and should be added dynamically

I hope that puts you on the right track.

Regards
Emil
0
 
LVL 11

Expert Comment

by:SAMIR BHOGAYTA
ID: 38417178
Hello, you can try this example

select
(select count(*) from accounts.account) as count1,
(select count(*) from players.player) as count2

or

select count(*) as `count`,"account" as `table` from accounts.account
union all
select count(*) as `count`,"player" as `table` from players.player

SELECT COUNT(*), 'Accounts' FROM Accounts.Account
UNION
SELECT COUNT(*), 'Players' FROM Players.Player
0
 

Author Comment

by:tommym121
ID: 38417446
I am hoping I can use INFORMATION_SCHEMA.TABLES or sys.tables to do that? (See the code below)  I just wonder how I can do that with two database together


use DB1;

SELECT
    t.NAME AS TableName,
    p.[Rows]
FROM
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND      
    i.index_id <= 1
GROUP BY
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY
    object_name(i.object_id)
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38418180
USE DB1;

SELECT
    ISNULL(t1.name, t2.name) AS Table_Name,
    ISNULL(ddps1.row_count, 0) AS DB1_Row_Count,
    ISNULL(ddps2.row_count, 0) AS DB2_Row_Count
FROM
    sys.tables t1
FULL OUTER JOIN
    DB2.sys.tables t2 ON t2.name = t1.name
LEFT OUTER JOIN      
    sys.indexes i1 ON t1.object_id = i1.object_id
LEFT OUTER JOIN
    sys.dm_db_partition_stats ddps1 ON ddps1.object_id = t1.object_id AND ddps1.index_id IN ( 0, 1 )
LEFT OUTER JOIN      
    DB2.sys.indexes i2 ON t2.object_id = i2.object_id
LEFT OUTER JOIN
    DB2.sys.dm_db_partition_stats ddps2 ON ddps2.object_id = t2.object_id AND ddps2.index_id IN ( 0, 1 )
WHERE
    t1.name NOT LIKE 'dt%' AND
    t2.name NOT LIKE 'dt%' AND
    i1.object_id > 255 AND
    i2.object_id > 255 AND
    i1.index_id <= 1 AND
    i2.index_id <= 1    
ORDER BY
    ISNULL(t1.name, t2.name)
0
 

Author Closing Comment

by:tommym121
ID: 38418488
Thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question