What is wrong with this specific t-sql query for a table located on a linked Server?-CodePudding

I am using SQL Server 2014 and I have this following simple t-sql query which lists all the columns and their relevant data types from a table on my local database.

USE [MyDatabase]

exec sp_columns [Table1]

I want to query that same Table which is located on a linked server. However, there must be something wrong in my query as I am getting an "incorrect syntax" error:

USE [MyDatabase]

exec sp_columns [Server IP].[Database Name on linked Server].dbo.[Table1]

Any help would be appreciated.

CodePudding user response：

The problem is your lack of quotes, you aren't making your value a literal string. This can be easily replicated without a linked server:

CREATE TABLE dbo.MyTable (ID int);
GO
EXEC sys.sp_columns MyTable;
GO
EXEC sys.sp_columns dbo.MyTable;
GO
DROP TABLE dbo.MyTable;

The first call to sys.sp_columns works fine, as the literal MyTable is interpreted as a literal string, per the documentation:

If you pass a single word that does not begin with @ and that's not enclosed in quotation marks - for example, if you forget @ on a parameter name - the word is treated as an nvarchar string, in spite of the missing quotation marks.

So MyTable is treated as N'MyTable'. For the latter call, this is not true. This is also documented (per the above), and also explicitly called out for object names:

If the value of a parameter is an object name, character string, or qualified by a database name or schema name, the whole name must be enclosed in single quotation marks. If the value of a parameter is a keyword, the keyword must be enclosed in double quotation marks.

Emphasis mine. You aren't quoting your string, so it errors.

Incorrect syntax near '.'.

So, for the above, quote your strings. It's literally that simple:

EXEC sp_columns N'dbo.MyTable';

CodePudding user response：

You can query the system tables via the normal Linked Server name

select
  typename = typ.name,
  c.*
from [Server IP].[Database Name on linked Server].sys.columns c
join [Server IP].[Database Name on linked Server].sys.tables t on t.object_id = c.object_id
join [Server IP].[Database Name on linked Server].sys.schemas s on s.schema_id = t.schema_id
join [Server IP].[Database Name on linked Server].sys.types typ on typ.system_type_id = c.system_type_id
where t.name = 'Table1'
  and s.name = 'SchemaName'