Skip to content
Advertisement

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

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

Advertisement

Answer

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.

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.

Now your query is simply:

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

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement