How to get count of multiple tables from linked and local servers

Question

How can I get records count of multiple tables from linked servers.

I have two servers named production and local. I need to write query to get result as mention in this picture.

I tried

SELECT 'TableA' AS Tables, COUNT(*) AS 'QA' FROM [Database].[schema].[tablename]
UNION
SELECT 'TableB' AS Tables, COUNT(*) AS 'QA' FROM [Database].[schema].[tablename]

How to display records count of linked server?

Answer 1

Use four-part-naming, like this:

SELECT 'TableA' AS Tables
    , COUNT(*) AS 'LinkedServer' FROM [Linked-Server-Name].[Database].[schema].[tablename]
UNION
SELECT 'TableB' AS Tables
    , COUNT(*) AS 'LocalServer' FROM [Database].[schema].[tablename]

Assuming your linked server is named Production, you could use:

SELECT Tables = 'TableA'
    , Production = (SELECT COUNT(*) FROM [Production].[Database].[schema].[tableA])
    , Local = (SELECT COUNT(*) AS FROM [Database].[schema].[tableA])

UNION
    , Tables = 'TableB'
    , Production = (SELECT COUNT(*) FROM [Production].[Database].[schema].[tableB])
    , Local = (SELECT COUNT(*) FROM [Database].[schema].[tableB])