Synonyms - How to retrieve synonyms in a database

• mattaustin

  Old Hand

  Points: 375

  More actions

  July 31, 2009 at 2:47 am

  #135039

  Hi Everyone,

  Does anyone know how I can retrieve definitions of all the synonyms that exist in a SQL Server 2005 database via a SQL query?

  I know that I can retrieve a list of synonym names using something like

  SELECT * FROM sysobjects

  WHERE xtype = 'SN'

  I also know that definitions of stored procedures can be retrieved using sp_help_text (and therefore re-released/changed programmatically) but I haven't been able to find an equivalent for synonyms.

  Basically, I want to be able to read all the database's synonyms into a cursor, change the Database pointer and re-release all the synonyms. Any ideas or suggestions would be gratefully received.

  Thanks,

  Matt

• mattaustin

  Old Hand

  Points: 375

  More actions

  July 31, 2009 at 2:55 am

  #1032826

  After searching for an answer on the internet for about two days, I finally found it about 5 minutes after posting this topic - Always the way, Heh?

  It seems you can get to the underlying object that the synonym points to with

  SELECT *

  FROM sys.synonyms

  Thanks anyway...