October 30, 2009 at 12:18 am
Comments posted to this topic are about the item Problem with scripting objects through query
--Divya
October 30, 2009 at 2:55 am
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
October 30, 2009 at 7:41 am
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.
October 30, 2009 at 7:51 am
I typically just use a bunch of UNION ALL statements in my script building scripts
October 30, 2009 at 7:59 am
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'
October 30, 2009 at 8:57 pm
.. 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??
October 31, 2009 at 1:01 am
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.
November 1, 2009 at 6:56 pm
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?
November 1, 2009 at 7:50 pm
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?
November 1, 2009 at 7:52 pm
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?
November 1, 2009 at 9:25 pm
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..
November 2, 2009 at 5:36 am
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.
November 2, 2009 at 6:00 am
I have scripted the objects this way to script the triggers for a particular list of tables.
--Divya
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply