Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problem with scripting objects through query Expand / Collapse
Author
Message
Posted Friday, October 30, 2009 12:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 11:52 PM
Points: 143, Visits: 552
Comments posted to this topic are about the item Problem with scripting objects through query

--Divya
Post #811370
Posted Friday, October 30, 2009 2:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 11:38 PM
Points: 52, Visits: 6
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


Post #811404
Posted Friday, October 30, 2009 7:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160
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.



Post #811575
Posted Friday, October 30, 2009 7:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 2:50 PM
Points: 1,937, Visits: 2,107
I typically just use a bunch of UNION ALL statements in my script building scripts
Post #811590
Posted Friday, October 30, 2009 7:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160
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'




Post #811603
Posted Friday, October 30, 2009 8:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 8:52 AM
Points: 13, Visits: 46
.. 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??
Post #811955
Posted Saturday, October 31, 2009 1:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 12:34 AM
Points: 344, Visits: 178
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.
Post #811981
Posted Sunday, November 1, 2009 6:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 3:27 AM
Points: 313, Visits: 382
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?
Post #812172
Posted Sunday, November 1, 2009 7:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 8:52 AM
Points: 13, Visits: 46
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?
Post #812175
Posted Sunday, November 1, 2009 7:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 8:52 AM
Points: 13, Visits: 46
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?
Post #812176
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse