SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Divya Agrawal / Problem with scripting objects through query / Latest Posts

RE: Problem with scripting objects through query

Divya Agrawal — Mon, 02 Nov 2009 06:00:19 GMT

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

RE: Problem with scripting objects through query

Scott Coleman — Mon, 02 Nov 2009 05:36:03 GMT

[quote][b]Linson.Daniel (11/1/2009)[/b][hr]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?[/quote]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.

RE: Problem with scripting objects through query

Linson.Daniel — Sun, 01 Nov 2009 21:25:43 GMT

[quote]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?[/quote]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..

RE: Problem with scripting objects through query

ankitwaitshere — Sun, 01 Nov 2009 19:52:38 GMT

[quote][b]Linson.Daniel (11/1/2009)[/b][hr]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? [/quote]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?

RE: Problem with scripting objects through query

ankitwaitshere — Sun, 01 Nov 2009 19:50:34 GMT

Select datalength(object_definition(669622570))returns only 28700 - might be for this object with above ID.But when I display the value ofSelect object_definition(669622570)It just gives me the first few lines from the stored proc. Thoughts?

RE: Problem with scripting objects through query

Linson.Daniel — Sun, 01 Nov 2009 18:56:38 GMT

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?

RE: Problem with scripting objects through query

AUSteve — Sat, 31 Oct 2009 01:01:31 GMT

[quote][b]ankitwaitshere (10/30/2009)[/b][hr].. 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??[/quote]According to BOL, OBJECT_DEFINITION returns nvarchar(max) in both SQL2005 and SQL2008 so it should be fine.

RE: Problem with scripting objects through query

ankitwaitshere — Fri, 30 Oct 2009 20:57:43 GMT

.. 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??

RE: Problem with scripting objects through query

Scott Coleman — Fri, 30 Oct 2009 07:59:37 GMT

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.[code]SELECT 'DROP PROCEDURE [' + SCHEMA_NAME([schema_id]) + '].[' + [name] + ']' + 'GO' + OBJECT_DEFINITION([object_id])+ '' + 'GO'FROM sys.objectsWHERE [name] = 'Test SP'[/code]

RE: Problem with scripting objects through query

Chris Harshman — Fri, 30 Oct 2009 07:51:05 GMT

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

RE: Problem with scripting objects through query

Scott Coleman — Fri, 30 Oct 2009 07:41:12 GMT

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.

RE: Problem with scripting objects through query

vitaly.maslevskiy — Fri, 30 Oct 2009 02:55:23 GMT

I would change query a little[code="sql"]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'[/code]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

Problem with scripting objects through query

Divya Agrawal — Fri, 30 Oct 2009 00:18:58 GMT

Comments posted to this topic are about the item [B]Problem with scripting objects through query[/B]