Menu
I have a utility database (customers) on my db server where I store all of the routines used to modify data on the other databases. We recently found out that using synonyms will greatly benefit us.
use Customers
IF EXISTS (SELECT * FROM employees.sys.synonyms WHERE name = 'tblPerson2') begin
drop synonym [dbo].tblPerson2
end
This doesn't work because I am using the Customers database but need to drop the synonym from my employees database.
SQL Server 2008 doesn't support this syntax -
drop synonym [employees].[dbo].tblPerson2
Anyone have any ideas on how to modify synonyms accross databases. My solution involves having to add an identical stored procedure to every database, which seems prone to error.
952
To drop a private synonym, either the synonym must be in your own schema or you must have the DROP ANY SYNONYM system privilege. To drop a PUBLIC synonym, you must have the DROP PUBLIC SYNONYM system privilege. You must specify PUBLIC to drop a public synonym. You cannot specify schema if you have specified PUBLIC .
The syntax to drop a synonym in Oracle is: DROP [PUBLIC] SYNONYM [schema .] synonym_name [force];
Use the ALTER SYNONYM statement to modify an existing synonym. To modify a private synonym in another user's schema, you must have the CREATE ANY SYNONYM and DROP ANY SYNONYM system privileges. To modify a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM system privileges.
The syntax for creating a Synonym in SQL Server Database is:CREATE SYNONYM [ name_of_schema. ]
EXEC('USE employees;
DROP SYNONYM [dbo].tblPerson2;')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
