Script system stored procedures in master?

Question

Script system stored procedures in master?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Gary Johnson (9/3/2008)
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.

Heh... what a concept... source control for SQL Server just like anything else anyone does. 😉

In other words, I absolutely agree! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

R. van Laake SSCommitted Points: 1533 More actions · Answer 2

Thanks to all for the overwhelming response! What a great site and forum this is.

As for using the script wizard: it doesn't script master system sp's. I never realized this until I ran into this problem.

Yes, in an ideal world any database object would start with a script, but for me in the real world it just doesn't work like that. You try some things in your development enviroment, and before you realize it one thing you tried has become the way of doing things.

I'm gonna try some of the ideas posted here.... thanks again to all!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

Just in case someone comes across this again, I have a nice little update for all'ya'all. 😀

Step 1 is to create this function in your master database. Details are, of course, in the flower box.

 CREATE FUNCTION [dbo].[ShowLongString]
/**********************************************************************************************************************
 Purpose:
 Display a string of more than 8000 characters.  The string can be Dynamic SQL, XML, or just about anything else.

 Usage:
--===== Example with Dynamic SQL
DECLARE @SQL VARCHAR(MAX);
 SELECT @SQL = '
 SELECT somecolumnlist
   FROM some table with joins
;'
;
 SELECT LongString 
   FROM util.ShowLongString(@SQL)
;
--===== Example with a call to a table or view
 SELECT sm.Object_ID, Definition = ls.LongString 
   FROM sys.SQL_Modules sm
  CROSS APPLY util.ShowLongString(sm.Definition) ls
;
Credit to Orlando Colamatteo for his original idea on this.

Revision History:
Rev 00 - 20 Sep 2013 - Jeff Moden - Initial creation and test.
**********************************************************************************************************************/--===== Declare the I/O for this function
        (@pLongString VARCHAR(MAX))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
 SELECT LongString =
        (
         SELECT REPLACE(
                    CAST(
                        '--' + CHAR(10) + @pLongString + CHAR(10)
                    AS VARCHAR(MAX))
                ,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
             AS [processing-instruction(LongString)] 
            FOR XML PATH(''), TYPE
        )
;
GO

Then, run this little ditty in the MASTER database and any and all the code that you've added to the MASTER database will end up being just one click away. That click will be obvious after you run the code. And, it won't list all the stuff that's normally there for the system that MS puts there.

DECLARE @Def NVARCHAR(MAX) = ''
;
 SELECT @Def += OBJECT_DEFINITION(object_id) + CONVERT(NVARCHAR(MAX),'
GO
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
GO
')
   FROM sys.sql_modules
  WHERE OBJECT_NAME(object_id) NOT LIKE N'%sp_MS%'
    AND OBJECT_NAME(object_id) NOT LIKE N'spt_values'
;
 SELECT * FROM dbo.ShowLongString(@Def)
;

A word of caution. To make this totally compatible with virtually all previous revs to 2008, I used a "variable overlay" instead of something like STRING_AGG() (which is freakin' awesome, BTW). If you try to sort the input to the @Def variable, it may only return the code for 1 object. We'd have to do a little trick or two to get it to sort and it's not worth it to me.

If you need to make it run in 2005, remember that the OBJECT_DEFINITION() function and += didn't come out until 2008.

This is also why I'm against closing old threads or defeating email notifications on old threads. And there's nothing like necro'ing a 14 year old thread, is there now? 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Post deleted. This thread did end up showing up in "active" threads.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)