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

Answer 2

Similar to Jason's answer with some improvements

• Use Quotename() function to wrap the names in square brackets
• Initialize the @SQL variable to an empty string, this means that the isnull is not required, and means that when you concatenate the results of the query into a single string it doesn't get the type wrong. String literals in a concatenation can take the default nvarchar size and cause your resulting string to be truncated unexpectedly.
• Ensure the string literals are also nvarchar by using the N in front of them.
• Filter to the dbo schema only, as the OP requested.
• Add the sys schema to the sp_executesql call

Totally agree this is not something where you need a cursor.

DECLARE @SQL NVARCHAR(MAX) = N''
SELECT @SQL += N'DROP SYNONYM ' + QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME(name) + N';' + Char(13) + Char(10)
FROM sys.synonyms
WHERE SCHEMA_NAME([schema_id]) = N'dbo'

EXEC sys.sp_executesql @SQL