1

I am in one database. How do I print out the columns names for table in another database?

create table dbo.Customer
(
     CustomerId int primary key,
     CustomerName varchar(100),
     ZipCode varchar(9)
)

Answer within Current Database:

declare @ColumnList varchar(max) = 
(select STUFF((
SELECT ', 
    '    
+ QUOTENAME(c.name) 
FROM .sys.columns c 
where c.object_id = object_id('Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList

Print columns for table in Database B

Added Database B below,

declare @ColumnList varchar(max) = 
(select STUFF((
SELECT ', 
    '    
+ QUOTENAME(c.name) 
FROM .sys.columns c 
where c.object_id = object_id('DatabaseB.dbo.Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList
2

You were almost there with your second answer - you just need to ensure you're selecting columns from the correct sys.columns table

declare @ColumnList varchar(max) = 
(select STUFF((
SELECT ', 
    '    
+ QUOTENAME(c.name) 
FROM DatabaseB.sys.columns c 
where c.object_id = object_id('DatabaseB.dbo.Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList

The above works for me, adding the DB reference into sys.columns for DatabaseB.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy