Sunday, January 2, 2011

Counts Across Multiple Instances

In one of my previous roles, I was constantly needing to watch counts across multiple servers.  This is a clever method I put together to check table counts within static/definitional tables, on all servers, after daily manipulations occurred.  Three servers, one execution -- in this example, the table is 'Symbols':

/* Insert current lookup table counts, each instance. */
DECLARE @counts TABLE (
 ServerA char(13),ServerB char(13),ServerC char(13),
 TableName char(8),CountA int,CountB int,CountC int
)
INSERT @counts (ServerA,ServerB,ServerC,TableName,CountA,CountB,CountC)
SELECT a.ServerA,b.ServerB,ServerC,a.TableName,a.CountA,b.CountB,c.CountC
FROM
(
  SELECT @@SERVERNAME ServerA,'Symbols' TableName,COUNT(*) CountA 
  FROM SQLSERVER1.DatabaseName.dbo.Symbols
) a
INNER JOIN
(
  SELECT 'SQLSERVER2' ServerB,'Symbols' TableName,COUNT(*) CountB 
  FROM SQLSERVER2.DatabaseName.dbo.Symbols
) b
ON a.TableName = b.TableName
INNER JOIN
(
  SELECT 'SQLSERVER3' ServerC,'Symbols' TableName,COUNT(*) CountC 
  FROM SQLSERVER3.DatabaseName.dbo.Symbols
) c
ON a.TableName = c.TableName
ORDER BY a.TableName


/* Return table counts side by side. */
SELECT
LTRIM(RTRIM(TableName)) AS TableName,  
LTRIM(RTRIM(ServerA)) AS ServerA, 
LTRIM(RTRIM(CountA)) AS CountA,
LTRIM(RTRIM(ServerB)) AS ServerB, 
LTRIM(RTRIM(CountB)) AS CountB,
LTRIM(RTRIM(ServerC)) AS ServerC, 
LTRIM(RTRIM(CountC)) AS CountC
FROM @counts

Sample result:

  TableName ServerA           CountA   ServerB             CountB   ServerC              CountC
  Symbols       SQLSERVER1  35131      SQLSERVER2   35131      SQLSERVER3     35131

2 comments:

  1. What if your corporate standard doesn't allow linked servers like mine?

    ReplyDelete
    Replies
    1. Well, I suppose in that case, you can dump the data from multiple instances, and load to working tables within a single instance. Then, you use the same construct without linking any servers.

      Delete