I think this is a rather valid question, and I'm not sure why it was marked for closure unless this is also a duplicate.
As Ben Thul points out in his comment:
One of the points of synonyms is to abstract the actual location of an object so that you can change it in only one place (i.e. the synonym definition) and anywhere the synonym is used automatically gets the right location.
This is certainly an excellent reason for synonyms (if not THE reason) but consider a scenario where you have a large SQL codebase that you inherited as the sole manager. You manage the data tables, views and stored procedures and inside this database there are near-thousands of database objects.
One day, (oops!) an update on a data feed breaks an internal process. It is now your task to inspect any broken code and fix the issue. After a bit of searching, you find an INSERT reference in an SP that doesn't seem to point to a data table? Therefore, you assume it is a synonym and you now need to find the underlying table so that you can further inspect what may be broken.
This is a valid case and, in fact, is exactly where I am today. The original poster takes the logical need one step further than just asking for a list of database object synonyms. Instead, he asks if there is a way to list all synonyms used in one stored procedure?
Personally, I'd be fine with a list of all synonyms, however, answering his question does get one step closer to the end-need.
Can some one please help me out, To get the list of Synonyms were used in the Stored procedure (Example: Procs.myproc)
I believe that you can not do that. The only thing you can do is to find all the name of synonyms and find these strings in stored procedure. You can get all names of synonyms by following query:
SELECT
s.name,
COALESCE(PARSENAME(s.base_object_name,4),@@servername) AS serverName,
COALESCE(PARSENAME(s.base_object_name,3),DB_NAME(DB_ID())) AS dbName,
COALESCE(PARSENAME(s.base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
PARSENAME(s.base_object_name,1) AS objectName
FROM sys.synonyms s
ORDER BY 2,3,4,5
then you go 1 by 1 in loop and check the presence "objectName" in the stored procedure