Problem with scripting objects through query

  • Comments posted to this topic are about the item Problem with scripting objects through query

    --Divya

  • I would change query a little

    select 'DROP PROCEDURE ['+ si.name + '].[' + so.name + ']'+ CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+

    OBJECT_DEFINITION(object_id)+ CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)

    from sys.objects so inner join sys.schemas si ON so.schema_id = si.schema_id

    where so.type='P'

    1. Add brackets to procedure name if proc name is "non standard"

    2. Add join with sys.schemas for procedures not in dbo/current user schema

  • You don't need carriage return for a valid end-of-line sequence, only line feed. Drop the CHAR(13) and just use CHAR(10) and it works fine.

  • I typically just use a bunch of UNION ALL statements in my script building scripts

  • The line breaks can be included in the literal strings, so you don't need to worry about which characters are used. You do have to put " ' + ' " in front of any GO however.

    SELECT 'DROP PROCEDURE [' + SCHEMA_NAME([schema_id]) + '].[' + [name] + ']

    ' + 'GO

    ' + OBJECT_DEFINITION([object_id])+ '

    ' + 'GO'

    FROM sys.objects

    WHERE [name] = 'Test SP'

  • .. All fine... but Why do I need OBJECT_DEFINITION(1234567) there...?? Is this to create the stored proc back?

    If so, it may not be possible as the function OBJECT_DEFINITION(1234567) returns only first few hundred chars I think...

    Thoughts??

  • ankitwaitshere (10/30/2009)


    .. All fine... but Why do I need OBJECT_DEFINITION(1234567) there...?? Is this to create the stored proc back?

    If so, it may not be possible as the function OBJECT_DEFINITION(1234567) returns only first few hundred chars I think...

    Thoughts??

    According to BOL, OBJECT_DEFINITION returns nvarchar(max) in both SQL2005 and SQL2008 so it should be fine.

  • Then again....would someone tell me why would I go about generating scripts like the way that was shown here when SQL server itself provides all the necessary options to script your db objects?

  • Select datalength(object_definition(669622570))

    returns only 28700 - might be for this object with above ID.

    But when I display the value of

    Select object_definition(669622570)

    It just gives me the first few lines from the stored proc. Thoughts?

  • Linson.Daniel (11/1/2009)


    Then again....would someone tell me why would I go about generating scripts like the way that was shown when SQL server itself provides all the necessary options to create your db objects?

    May be if someone wants scripts for many objects all in one shot... not sure if SQL-Server does that for many objects at a time. Does it?

  • May be if someone wants scripts for many objects all in one shot... not sure if SQL-Server does that for many objects at a time. Does it?

    Ohh hell ya!!! It does........

    From SQL 2K onwards it does have the option to script your entire database.....not sure about the earlier versions..

  • Linson.Daniel (11/1/2009)


    Then again....would someone tell me why would I go about generating scripts like the way that was shown here when SQL server itself provides all the necessary options to script your db objects?

    For instance, when I want to see the definition of all objects that reference a particular linked server, or call a certain stored proc, or use OPENQUERY, or whatever else I'm interested in. I usually use something like "SELECT definition FROM [?].sys.sql_modules WHERE definition LIKE '%xyz%' " as a parameter for sp_msforeachdb.

  • I have scripted the objects this way to script the triggers for a particular list of tables.

    --Divya

Viewing 13 posts - 1 through 13 (of 13 total)

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