question

munna avatar image
munna 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.
t-sqlsynonyms
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
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`
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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')
3 Likes 3 ·
Don't forget do mark an answer as accepted
2 Likes 2 ·
I think the cursor is mentioned in order to execute the output of the select ...
0 Likes 0 ·
+1 to Matt's comment using QUOTENAME - I guess you've had plenty of use of that!
0 Likes 0 ·
@fatherjack, yes shuddered at the same thought, waiting with baited breath for the OP to *actually* utter those words
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.