CODEWITHSUNDEEP
sqlsql-servert-sql
Eric Klaus
Eric Klaus

Reputation: 955

Join two sys.columns tables from different servers

I have two similar dbs on two different servers. I'm trying to join their sys.columns tables, but I don't understand why this wouldn't work

SELECT *
             FROM server1.db1.sys.columns t
             INNER JOIN server2.db2.sys.columns s
             ON        OBJECT_NAME(t.[object_id]) = OBJECT_NAME(s.[object_id])
             AND       t.name        = s.name 
             WHERE     OBJECT_NAME(t.[object_id]) = 'commonTable'

commonTable exists in both dbs. Above query returns empty set

I join on OBJECT_NAME because their object_ids obviously different, since they are located on different dbs, so I join on their names in that way

Upvotes: 0

Views: 198

Answers (1)

anon
anon

Reputation:

OBJECT_NAME() works locally so it is never going to return the name of some object id in another database. Join to the foreign sys.columns and sys.objects view. 

SELECT *
  FROM server1.db1.sys.columns AS loc_c
  INNER JOIN server2.db2.sys.columns AS rem_c
  ON loc_c.name = rem_c.name
  INNER JOIN server1.db1.sys.tables AS loc_t
  ON loc_t.[object_id] = loc_c.[object_id]
  INNER JOIN server2.db2.sys.tables AS rem_t
  ON loc_t.name = rem_t.name
  AND rem_t.[object_id] = rem_c.[object_id]
  WHERE loc_t.name = N'commonTable';

You may want to add local and remote joins to sys.schemas, too, since dbo.foo and otherschema.foo will both match.

You may also consider synonyms and/or views to reduce complexity, if you are doing this a lot.

CREATE VIEW dbo.server1cols
AS 
  SELECT [table] = loc_t.name, [column] = loc_c.name
    FROM server1.db1.sys.columns AS loc_c
     INNER JOIN server1.db1.sys.tables AS loc_t
     ON loc_t.[object_id] = loc_c.[object_id];
GO

CREATE VIEW dbo.server2cols
AS 
  SELECT [table] = rem_t.name, [column] = rem_c.name
    FROM server2.db2.sys.columns AS rem_c
     INNER JOIN server2.db2.sys.tables AS rem_t
     ON rem_t.[object_id] = rem_c.[object_id];
GO

CREATE VIEW dbo.MatchDB1DB2Cols
AS
  SELECT s1.[table],
         db1column = s1.[column],
         db2column = s2.[column]
  FROM dbo.server1cols AS s1
  INNER JOIN dbo.server2cols AS s2
  ON s1.[table] = s2.[table]
  AND s1.[column] = s2.[column];
GO

Now your query is simply:

SELECT [table], db1column, db2column
  FROM dbo.MatchDB1DB2Cols
  WHERE [table] = N'commonTable';

You may also want to consider a full outer join somewhere so you can also note differences between the tables.

Upvotes: 4

Related Questions