asked
Create synonyms for all the objects in a database.
Hi, I was looking for a script to create synonyms for all the objects( User defined Views, functions, stored procedures, Tables) in a database. A script which would run a cursor through the sysobjects finding the object name and print me an output... create synonym for Databasename.schemaname.objectname Example: create SYNONYM RiverCities for RiverResearch.River.Cities and the query to select distinct object types being select name from sysobjects where xtype = 'P' returns the name of all the user defined stored procedures Thanks in advance.
Why a cursor? You've pretty much got your answer there already select 'create synonym for '+db_name()+'.'+schema_name(uid)+'.'+name from sysobjects where xtype in('P''U','V','TF','FN') although you really should be using `sys.objects`
Yep - for 2005 SP2+, you want select 'create synonym for ' + QUOTENAME(db_name()) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME([name]) from [sys].[objects] where [type] in('P''U','V','TF','FN')
I think the cursor is mentioned in order to execute the output of the select ...
+1 to Matt's comment using QUOTENAME - I guess you've had plenty of use of that!
@fatherjack, yes shuddered at the same thought, waiting with baited breath for the OP to *actually* utter those words
