CODEWITHSUNDEEP
sql-servert-sqllinked-server
user3115933
user3115933

Reputation: 4443

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

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?

Upvotes: 0

Views: 28

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 1

Related Questions