CODEWITHSUNDEEP
sql-servert-sql
Eric Klaus
Eric Klaus

Reputation: 955

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

Upvotes: 1

Views: 929

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

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 = c.object_id
where t.name = 'blahtablename'

Upvotes: 3

Related Questions