Drop all the synonyms using cursor

Question

I wanted to drop all the synonyms of a database (sql server 2008 r2) using cursor. environment-database name- 'mydatabase', schema name- 'dbo'.. Can you please guide me as i did try but the statement of while .. end, is not able to drop the synonym. what logic should be apply w.r.t cursor?

Answer 1

No need to use a cursor. Do it as set:

declare @n char(1)
set @n = char(10)

declare @stmt nvarchar(max)

select @stmt = isnull( @stmt + @n, '' ) +
'drop synonym [' + SCHEMA_NAME(schema_id) + '].[' + name + ']'
from sys.synonyms

exec sp_executesql @stmt