List of all databases and corresponding synonyms in entire instance

Question

I am trying to see a list of all synonyms in all databases in an instance. In majority of databases there is only 1 synonym, but there are hundreds of databses in each instance. Ideally I would like results to look something like this:

Column 1: DatabaseName

Column 2: Synonym name

Column 3: base object name (or anything else that will show the exact text of the synonym)

This query does more of less what I want it to do, but it works on a single database only.

select db_id(parsename(base_object_name, 3)) as dbid
     , object_id(base_object_name) as objid
     , base_object_name
from sys.synonyms;'

I tried to change the above query to show results for all databases and I ended up with the query below. While it doesn't fail furing execution it doesn't produce results either. It prints hundreds of empty tables with the correct column names.

DECLARE @command varchar(1000)
SELECT @command = 'select db_id(parsename(base_object_name, 3)) as dbid
     , object_id(base_object_name) as objid
     , base_object_name
from sys.synonyms;'
EXEC sp_MSforeachdb @command

I would be very grateful if you could help me with finding a way to get one clean list of all synonyms on each database on the instance.

score 6 · Accepted Answer · answered Sep 20 '16 at 07:27

You're very close. You need to add a use, otherwise all of your statements run inside master:

DECLARE @command varchar(1000)
SELECT @command = 'use [?] select ''[?]'', db_id(parsename(base_object_name, 3)) as dbid
     , object_id(base_object_name) as objid
     , base_object_name
from sys.synonyms;'
EXEC sp_MSforeachdb @command

I've added the database name as another column so that you can see the database names, and I'm sure you'll probably want to adapt this query further now that it's actually running against each separate database.

List of all databases and corresponding synonyms in entire instance

1 Answers1