To get the coluns datatype information from Linkedserver(MYSQL) fro MS SQL server by TSQL query?

Question

Hi,

Could you please help me to get the column information from MYSQL table from MS SQL. I am trying to get the data by using OPEN query but I am not able to get the columns information? SSMS has already had the linked server connection. do we have any general query ?

Answer 1

Check if the next approach returns the column information:

declare @sql nvarchar(max) = N'select * from OPENQUERY( . . . . )'

select * 
from sys.dm_exec_describe_first_result_set(@sql, null, null)

Answer 2

Hi @Vivaan Shresth
Try this:

select * from  [LinkedServerInstanceHere].[DatabaseOnLinkedServerHere].information_schema.columns where Table_name = '...'  

If it doesn't work,you can also use the sp_columns_ex system stored procedure to return column information about the columns from a specified linked server.
For more examples about sp_columns_ex, please refer to this article: Return Column Information from a Linked Server in SQL Server

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Check out Microsoft's documentation on this:

[https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-columns-ex-transact-sql?view=sql-server-ver16

Here is the example query they provided that returns the data type of the JobTitle column of the HumanResources.Employee table in the AdventureWorks2019 database on the linked server Seattle1:

EXEC sp_columns_ex 'Seattle1',   
   'Employee',   
   'HumanResources',   
   'AdventureWorks2012',   
   'JobTitle';