I am utilizing one database A. How do I print out the columns names for synonym for table in another database B?
create table DatabaseB.dbo.Customer
(
CustomerId int primary key,
CustomerName varchar(100),
ZipCode varchar(9)
)
Answer if table was in Current Database A:
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
How to Print columns for table if in Database B?
use Database A;
go;
create synonym dbo.Customer for DatabaseB.dbo.Customer
declare @ColumnList varchar(max) =
(select STUFF((
SELECT ',
'
+ QUOTENAME(c.name)
FROM sys.columns c
where c.object_id = object_id('dbo.Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))
print @ColumnList
I want have it use synonym in current database, (which already points to other database B). Purpose: To find columns for Customer, without specifying Database B