Get column names of table on linked server

_{Thursday, November 3, 2016 9:36 PM}

Hi,

I'm trying to create a t-sql statement  like this

            Insert Into Table2 (col1, col2, col3) 

                   Select * from Table1                     

Table2 on sql 2008 linking server; has other columns not shown

Table1 on linked sql 2008 server has columns col1, col2, col3

To create the Insert statement I need to get a list of the column names in Table1 on the linked server and append them to Table2 (viewed as a string). I tried varations of sys.columns from the linking server but that does NOT get info from the linked server only the linking server. I tried "sp_columns_ex" and it returns all columns on the linked server table but when I use 'COLUMN_NAME' for its column_name option no results are returned. Any ideas appreciated,

edm2

_{Thursday, November 3, 2016 10:01 PM ✅Answered}

I have created a linked server to an instance on my machine called Publisher\SQL2012.

I first query it and persist the results to a table called UniqueTableName - change this incase you have a like named table.

I then get a column list from it and drop this table:

declare @str varchar(max)=''
select * into UniquetableName from openrowset('SQLNCLI', 'Server=Publisher\SQL2012;Trusted_Connection=yes;', 'select * from sys.objects')

select @str =@str +name+',' from sys.columns where object_id =object_id('UniquetableName')
select  @str
drop table  UniquetableName

_{Friday, November 4, 2016 6:29 AM ✅Answered}

Hi edm2,

>> To create the Insert statement I need to get a list of the column names in Table1 on the linked server and append them to Table2 (viewed as a string).

You could have a look at following demo to build your insert SQL string.

declare @str varchar(max) = ' Insert Into Table2 ('

set @str = @str + stuff((

SELECT ','+ c.name 
FROM [linkedserver].[database].sys.columns c 
INNER JOIN [linkedserver].[database].sys.tables t ON c.object_id = t.object_id
where t.name = 'table1' and SCHEMA_NAME(t.schema_id)='schema of table1'
for xml path('')),1,1,'')

set @str = @str + ' ) Select * from Table1'

select @str

Best Regards,

Albert Zhang

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

_{Friday, November 4, 2016 12:18 AM}

Hilary,

That looks good. I had no issue using sys.objects but didn't know how to tie it into the linked server.

edm2

_{Friday, November 4, 2016 12:29 AM}

I was using sys.objects as a test table. Replace the name sys.objects with the name of the table you are interested in getting the columns from.