列印另一個數據庫中表的同義詞的列名

我正在使用一個數據庫 A。如何列印出另一個數據庫 B 中表的同義詞的列名？

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

如果表在目前數據庫 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

如果在數據庫 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

我想讓它在目前數據庫中使用同義詞（已經指向其他數據庫 B）。目的：查找客戶的列，而不指定數據庫 B

我在這裡提供了一個類似的答案，它利用了sys.dm_exec_describe_first_result_set。這是一個如何實現目標的範例。

--demo setup
SET NOCOUNT ON
USE [master]
GO
--Create two sample databases
DROP DATABASE IF EXISTS [DB1]
DROP DATABASE IF EXISTS [DB2]
CREATE DATABASE [DB1]
CREATE DATABASE [DB2]
GO

--Connect to [DB2]
USE [DB2]
GO
--Create and load a table
CREATE TABLE [dbo].[CUSTOMER](
   [CustomerID] [int] NOT NULL,
   [FirstName] [varchar](20) NOT NULL,
   [LastName] [varchar](20) NOT NULL,
   [Email] [varchar](30) NOT NULL,
   [PhoneNo] [int] NOT NULL,
   [StreetAddress] [varchar](40) NULL,
   [City] [varchar](20) NULL,
   [OrderID] [int] NOT NULL
)
GO

--Connect to [DB1] and create a synonyn that points to a table in [DB2]
USE [DB1]
GO
CREATE SYNONYM DB2_CUSTOMER FOR [DB2].dbo.CUSTOMER
GO

--The solution
--Use sys.dm_exec_describe_first_result_set to retrieve the metadata for the synonym
DECLARE @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + NAME + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM DB2_CUSTOMER', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');
SET @cols = replace(@cols,',',',' + char(10))
print @cols

---

結果：

CustomerID int,
FirstName varchar(20),
LastName varchar(20),
Email varchar(30),
PhoneNo int,
StreetAddress varchar(40),
City varchar(20),
OrderID int

引用自：https://dba.stackexchange.com/questions/223525