2

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.

enter image description here

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?

6

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])

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.