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»»

Script system stored procedures in master? Expand / Collapse
Author
Message
Posted Wednesday, September 3, 2008 9:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 27, 2009 1:14 AM
Points: 47, Visits: 104
Hi,

I made quite a lot of stored procedures in the master's *system* stored procedures folder. (The reason is discussed in another topic, but it boils down to wanting to have 1 SP that can be called from many databases).

Now I want to script those sp's in order to copy them to a production server.

But, when I script "all objects" from the master database, those system stored procedures are not scripted. What must I do to get them included in a script?

Thanks,

Raymond
Post #563131
Posted Wednesday, September 3, 2008 11:41 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
"Select [text] from master.sys.syscomments" works but is limited to 4000 characters.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #563243
Posted Wednesday, September 3, 2008 11:47 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
OK, this will get the entire length:
select o.name, m.definition
from master.sys.system_sql_modules m
join master.sys.system_objects o
ON o.object_id = m.object_id

Of course, getting the entire definition out of there and into a script is a whole other trick (text output window will cut it off at 8000 characters).


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #563248
Posted Wednesday, September 3, 2008 12:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Just open a cursor, loop and print it out. Copy / paste from there into the script window.
Post #563280
Posted Wednesday, September 3, 2008 12:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 10, 2013 4:59 PM
Points: 79, Visits: 183
Hey, this is neat. I hadn't found the sys_modules before. So much easier than having to concatenate the syscomments text field!

Run the following with results to text. It'll script out all the SPs. Doesn't matter what database it is run in either.
SET NOCOUNT ON
DECLARE @sText nvarchar(max),
@c cursor,
@ObjectID int

SET @c = cursor for
SELECT sp.object_id
from sys.procedures sp
WHERE is_ms_shipped = 0
AND left(sp.name, 2) = 'sp' -- might not want this!! Change as needed.
ORDER BY sp.name

OPEN @c
FETCH @c INTO @ObjectID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sText = ''


SELECT @sText = sc.definition
FROM sys.sql_modules sc
WHERE sc.object_id = @ObjectID

PRINT '----------------------------------------------------------------------------'
SELECT '-- ' + object_name(@ObjectID)
PRINT '----------------------------------------------------------------------------'
PRINT @sText
PRINT 'GO'
PRINT '----------------------------------------------------------------------------'
FETCH @c INTO @ObjectID
END
CLOSE @c
DEALLOCATE @c
GO




Gary Johnson
Sr Database Engineer
Post #563320
Posted Wednesday, September 3, 2008 1:23 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
This is easy enough to do without a cursor:
--======
SET NOCOUNT ON

select N'
----------------------------------------------------------------------------
-- ' + p.name + N'
----------------------------------------------------------------------------
' + m.definition + N'
GO
----------------------------------------------------------------------------
'
from sys.sql_modules m
join sys.procedures p
ON p.object_id = m.object_id
WHERE is_ms_shipped = 0
AND left(p.name, 2) = 'sp' -- might not want this!! Change as needed.
ORDER BY p.name

Heck, you could even make it into a view.

The problem with both of these routines, however, is that they will get cut off at whatever your text output column display width is set to and that cannot be set higher than 8000. To fix this you need a specialized Split() function.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #563345
Posted Wednesday, September 3, 2008 1:47 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
If you want to solve the "text output limit" problem then you will first need a specialized split function like this:
ALTER function [dbo].[fnSplit3]( 
@parameter varchar(Max) -- the string to split
, @Seperator Varchar(64) -- the string to use as a seperator
)
RETURNS @Items TABLE(
ID INT -- the element number
, item VARCHAR(8000) -- the split-out string element
, OffSet int -- the original offest
--( not entirley accurate if LEN(@Seperator) > 1 because of the Replace() )
)
AS
BEGIN
/*
"Monster" Split in SQL Server 2005

From Jeff Moden, 2008/05/22

BYoung, 2008/06/18: Modified to be a Table-Valued Function
And to handle CL/LF or LF-only line breaks
(Note: make it inline later, to make it faster)

Test: (scripts all triggers in your database)

Select Lines.Item
From sys.sql_modules M
Join sys.objects O on O.object_id = M.object_id
cross apply dbo.fnSplit1(M.definition, char(13)+char(10)) Lines
Where O.Type = 'TR'
Order by O.create_date, Lines.ID
*/
Declare @Sep char(1)
Set @Sep = char(10) --our seperator character (convenient, doesn't affect performance)
--NOTE: we make the @Sep character LF so that we will automatically
-- parse out rogue LF-only line breaks.

--===== Add start and end seprators to the Parameter so we can handle
-- all the elements the same way
-- Also change the seperator expressions to our seperator
-- character to keep all offsets = 1
SET @Parameter = @Sep+ Replace(@Parameter,@Seperator,@Sep) +@Sep
-- This reduces run-time about 10%

;WITH cteTally AS
(--==== Create a Tally CTE from 1 to whatever the length
-- of the parameter is
SELECT TOP (LEN(@Parameter))
ROW_NUMBER() OVER (ORDER BY t1.ID) AS N
FROM Master.sys.sysColumns t1
CROSS JOIN Master.sys.sysColumns t2
)
INSERT into @Items
SELECT ROW_NUMBER() OVER (ORDER BY N) AS Number,
SUBSTRING(@Parameter, N+1, CHARINDEX(@Sep, @Parameter, N+1)-N-1) AS Value
, N+1
FROM cteTally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter, N, 1) = @Sep --Notice how we find the seperator

Return
END

Then use it with Cross Apply like this:
--======
SET NOCOUNT ON

Select [item]
From (select p.name, 0 as [num], N'
----------------------------------------------------------------------------
-- ' + p.name + N'
----------------------------------------------------------------------------
' as [item]
from sys.sql_modules m
join sys.procedures p ON p.object_id = m.object_id
WHERE is_ms_shipped = 0

UNION ALL
Select p.name, Lines.ID as num, Lines.item
from sys.sql_modules m
join sys.procedures p ON p.object_id = m.object_id
Cross Apply dbo.fnSplit3(m.definition, (char(13)+char(10))) Lines
WHERE is_ms_shipped = 0

UNION ALL
Select p.name, 999999,N'
GO
----------------------------------------------------------------------------
' as [item]
from sys.procedures p
WHERE is_ms_shipped = 0
) S
WHERE left([name], 2) = 'sp' -- might not want this!! Change as needed.
ORDER BY [name], [num]

Of course, as Jeff and Gus will tell you, this would be even faster if you replace the Function call with an in-line CTE, but I find this easier. :)


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #563367
Posted Wednesday, September 3, 2008 1:53 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
Try this one:
DECLARE @A nvarchar(max)
SELECT @A = ISNULL(@A,'') + OBJECT_DEFINITION(object_id) + '
GO
' FROM sys.procedures
SELECT CAST(@A AS XML)

Explanation:
1. Use OBJECT_DEFINITION to get the full script of the object in nvarchar(max)
2. Use the per row assignment to @A to add each object_definition to the variable.
3. Cast the result to XML and switch to Grid Mode, to avoid the result being cut off at x chars.


Best Regards,
Chris Büttner
Post #563373
Posted Wednesday, September 3, 2008 1:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
Christian Buettner (9/3/2008)
Try this one:
DECLARE @A nvarchar(max)
SELECT @A = ISNULL(@A,'') + OBJECT_DEFINITION(object_id) + '
GO
' FROM sys.procedures
SELECT CAST(@A AS XML)

Explanation:
1. Use OBJECT_DEFINITION to get the full script of the object in nvarchar(max)
2. Use the per row assignment to @A to add each object_definition to the variable.
3. Cast the result to XML and switch to Grid Mode, to avoid the result being cut off at x chars.


Here's what I get when I execute your script:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Msg 9421, Level 16, State 1, Line 5
XML parsing: line 149, character 30, illegal name character



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #563376
Posted Wednesday, September 3, 2008 2:04 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
If I add "ORDER BY name" the error goes aways but it only lists one routine.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #563382
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse