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.

Your Answer

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