sql-servert-sql

using sys.columns from a different databases and servers

Solution specified here, works as follows (I run from server1)

select * from server1.blahdbname.sys.columns c 
where c.[object_id] = OBJECT_ID('blahdbname.dbo.blahtablename')

It is fine. Works as intended. But below query doesn't work (changed servername) (returns empty set)

select * from server2.blahdbname.sys.columns c 
where c.[object_id] = OBJECT_ID('blahdbname.dbo.blahtablename')

What is the universal way if I want to query different servers too? Above queries generated dynamically, so I want them to work on any server and db

NOTE: blahdbname and blahtablename both exist in server1 and server2. server1 and server2 are linked

Solution

  • This is because you are using the function OBJECT_ID. This runs against the current database, not the remote database.

    Instead you should use the system views on the remote server to make this happen.

    select c.* 
from server1.blahdbname.sys.columns c 
join server1.blahdbname.sys.tables t on c.object_id = t.object_id
where t.name = 'blahtablename'