Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script system stored procedures in master?


Script system stored procedures in master?

Author
Message
R. van Laake
R. van Laake
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
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
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
"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."
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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."
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Just open a cursor, loop and print it out. Copy / paste from there into the script window.
Gary Johnson-259336
Gary Johnson-259336
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 184
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
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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."
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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. Smile

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
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
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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."
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search