1

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

0

I provided a similar answer here which utilizes sys.dm_exec_describe_first_result_set. Here is an example of how you can achieve your goal.

--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

Results:

CustomerID int,
FirstName varchar(20),
LastName varchar(20),
Email varchar(30),
PhoneNo int,
StreetAddress varchar(40),
City varchar(20),
OrderID int
  • I am trying to have it use the synonym in the current database, which points to the other database, find columns for Customer, without specifying Database B, since its already in the synonymm create synonym dbo.Customer for DatabaseB.dbo.Customer – user162241 Nov 27 '18 at 15:43
  • I updated my answer. – Scott Hodgin Nov 27 '18 at 16:17

Your Answer

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