September 3, 2008 at 9:46 am
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
September 3, 2008 at 11:41 am
"Select [text] from master.sys.syscomments" works but is limited to 4000 characters.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 11:47 am
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).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 12:16 pm
Just open a cursor, loop and print it out. Copy / paste from there into the script window.
September 3, 2008 at 12:53 pm
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
September 3, 2008 at 1:23 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 1:47 pm
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. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 1:53 pm
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
September 3, 2008 at 1:57 pm
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
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 2:04 pm
If I add "ORDER BY name" the error goes aways but it only lists one routine.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 2:33 pm
IMHO, even though you can use TSQL to get the information, I would still simply right click on Master in the Object Explorer window and then click on Tasks/Generate Scripts... Then select the database and stored procedures in the wizard. Then I would make sure to script each SP into its own file name. Far easier than writing the TSQL. Plus when you are pointed to master only the User Defined SPs show up to be scripted. Of course, this way you end up with Microsofts screwball naming convention of files... [schemaName].[SPName].StoredProcedure.sql.
Ah well. A simple VS Studio Macro fixes that. Now, if you had just scripted the files out to begin with... 🙂
Gary Johnson
Sr Database Engineer
September 3, 2008 at 3:06 pm
The script generator does not always work (bugs, fails on certain syntaxes), also cannot be automated and isn't very flexible. Plus there are things that it will not even offer to script (like the system procedures).
Being able to script from SQL has a lot of value for many of us.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 3:12 pm
rbarryyoung (9/3/2008)
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
Hm, I should have tested against more realistic data than simple SELECTs and PRINTs...
Using CDATA section around the nvarchar value seems to resolve the issue for up to 13 system stored procedures on my machine. If I use more, the output in the data grid is totally empty (I cannot click on the results anymore), while the text output still returns the cut-off text.
Besides that, encasing the procs in a CDATA section converts many special characters (eg. >) which would need to be replaced as well.
So overall my solution fails completely.
But here is something similar that might work out:
sqlcmd -S TheServerNameHere -o C:\Results.txt -Q "SET NOCOUNT ON;SET ROWCOUNT 200;DECLARE @a nvarchar(max);SELECT @a = ISNULL(@A,'') + ISNULL(OBJECT_DEFINITION(id), object_name(id) + ':N/A') + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM sysobjects WHERE xtype = 'P';SELECT @a;" -y 0
The limit for output is 1 MB which is approximately reached with 200 system stored procedures on my machine.
If I set the ROWCOUNT to 0, SQLCMD seem to hang (maybe due to the excessive string handling? not sure).
EDIT:
Add SET NOCOUNT ON in script
Add GO after each PROC in script
Truncated quoted text
Best Regards,
Chris Büttner
September 3, 2008 at 3:28 pm
Christian Buettner (9/3/2008)
But here is something similar that might work out:
sqlcmd -S TheServerNameHere -o C:\Results.txt -Q "SET NOCOUNT ON;SET ROWCOUNT 200;DECLARE @a nvarchar(max);SELECT @a = ISNULL(@A,'') + ISNULL(OBJECT_DEFINITION(id), object_name(id) + ':N/A') + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM sysobjects WHERE xtype = 'P';SELECT @a;" -y 0The limit for output is 1 MB which is approximately reached with 200 system stored procedures on my machine.
Thats too bad. My larger customer sites are more in the 8-12MB range.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 7:24 pm
rbarryyoung (9/3/2008)
The script generator does not always work (bugs, fails on certain syntaxes), also cannot be automated and isn't very flexible. Plus there are things that it will not even offer to script (like the system procedures).Being able to script from SQL has a lot of value for many of us.
Yes being able to script from SQL is a handy utility. But to me, that is all it is. IMHO, Any object in the database should be created from a script to start with. Then a batch process can be used to deploy those scripts to the DB. It shouldn't be done the other way around. Too hard to have version control and too hard to maintain multiple environments.
On the few times I have used the script generator it has worked just fine. But I would never rely on it for production purposes.
Gary Johnson
Sr Database Engineer
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply