In SQL Server, we can use the sys.synonyms system catalog view to get a list of synonyms in the current database. This view returns all objects in the sys.objects view of type SN (which stands for synonym).
Example
Here’s an example of returning all columns from the sys.synonyms view:
SELECT * FROM sys.synonyms;Result:
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published base_object_name --------- ---------- ------------ --------- ---------------- ---- --------- ------------------------ ------------------------ ------------- ------------ ------------------- ----------------------- Customers 1861581670 null 1 0 SN SYNONYM 2022-12-11T04:15:38.316Z 2022-12-11T04:15:38.316Z false false false [Sales].[Customers] Cats 1893581784 null 1 0 SN SYNONYM 2022-12-11T08:31:36.996Z 2022-12-11T08:31:36.996Z false false false [PetHotel].[dbo].[Cats] Orders 1909581841 null 1 0 SN SYNONYM 2022-12-12T02:36:11.883Z 2022-12-12T02:36:11.883Z false false false [Sales].[Orders] Products 1925581898 null 1 0 SN SYNONYM 2022-12-12T02:36:28.576Z 2022-12-12T02:36:28.576Z false false false [Warehouse].[Products] Vendors 1941581955 null 1 0 SN SYNONYM 2022-12-12T02:36:45.246Z 2022-12-12T02:36:45.246Z false false false [Purchasing].[Vendors] 5 row(s) returned
In my case, I have five synonyms in my database.
Let’s reduce the number of columns returned:
SELECT
name AS [Synonym],
base_object_name AS [Base Object]
FROM sys.synonyms;Result:
Synonym Base Object --------- ----------------------- Customers [Sales].[Customers] Cats [PetHotel].[dbo].[Cats] Orders [Sales].[Orders] Products [Warehouse].[Products] Vendors [Purchasing].[Vendors] 5 row(s) returned
The base_object_name column is handy, because it shows us the fully qualified path to the base object.