How to convert this existing query to retrieve the same information from a table located in a database on a linked server?

Question

I am using SQL Server 2014 and I have the following T-SQL query which gives me some nice information about any table on the database.

USE [MyDatabase]

SELECT COLUMN_NAME,* 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table1' AND TABLE_SCHEMA='dbo'

I would like to modifiy this query so that I get the same information from a table residing on a database on a linked server.

Assuming the full schema of the table is [xxx.xx.x.xx].Database2.dbo.[Table1], how do I rewrite my existing query?

I had a look at this Stackoverflow question but the answers do not meet my requirements: How to list all tables & columns names of a linked-server database in SQL Server?

Did you try `[server].[database].[information_Schema].[columns]`? — Zohar Peled, May 21 '20 at 07:39
No, I didn't but I just gave it a try and it works!! Please post as an answer. Thanks — user3115933, May 21 '20 at 07:41

score 1 · Accepted Answer · answered May 21 '20 at 08:37

1

Use a 4 parts identifier:

[server].[database].[information_Schema].[columns]

[server] being the linked server name,
[database] being the relevant database in the linked server.

answered May 21 '20 at 08:37

Zohar Peled

1 Answers1