Post reply

SQL definition statement for synonym ?

  • January 24, 2013 at 5:23 am

    #394013

    I have created a synonym as follows

    CREATE SYNONYM [dbo].[ms4] FOR [local].[Northwind].[dbo].[Products]

    GO

    and then i want to retrieve a same SQL definition statement from one of the system/db's view so i fired

    select text from syscomments where id = object_id('dbo.[ms4]')

    but 'text' from syscomments returns text for all other types other then synomym

    syscomments contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.

    so from which table/view i can get original SQL definition statements for synonym. Its not there in syscomments . so where is they stored?

  • January 24, 2013 at 5:29 am

    #1580495

    you want to use the system view sys.synonyms, instead, which has everything you need:

    select * from sys.synonyms

    --rebuilding the command:

    select

    'CREATE SYNONYM '

    + quotename(SCHEMA_NAME(schema_id))

    +'.'

    + quotename(name) --ie MyView

    + ' FOR '

    + base_object_name --ie [SandBox].[dbo].[VW_FIXEDWIDTH]

    FROM sys.synonyms

    --Results:

    /*

    CREATE SYNONYM [dbo].[MyView] FOR [SandBox].[dbo].[VW_FIXEDWIDTH]

    */

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 24, 2013 at 5:34 am

    #1580496

    Also syscomments is depreciated, use sys.sql_modules instead

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply