Scripting out SQL Server Objects without the GUI

 

I quite often find myself scripting objects out via T-SQL rather than using the GUI, it’s not that I have anything against using the GUI to script out a Stored procedure, it’s just that sometimes this can be time-consuming especially when there are alot of stored procedures in the Programmability folder.

I know that I can filter the list but this includes clicking the filter option, adding a keyword and then Applying – I then have to remember to remove the filter afterwards if i plan to script another Stored Procedure out, it’s all a bit messy…..

I can simple Open a new query window, Use the database I want to script an object from and run some T-SQL – how easy is that?

So for this blog post I wanted to run through a couple of options for obtaining the Object definitions of Stored procedures via T-SQL , for this demonstration I needed a large stored procedure as I want to demonstrate some interesting results that you may encounter scripting out object definitions .

Today I will be using a well know stored procedure sp_blitz  from the fine folks at Brent Ozar Unlimited  (I hope they don’t mind) , I wanted to use a real stored procedure for my demo rather than some contrived example. We all know how good this Stored Procedure is and what’s more it’s totally free! however my purpose for its use today is purely for its gargantuan size 🙂

So lets start with OBJECT_DEFINITION – Returns an NVARCHAR(MAX) which should be more than enough to house the definition of sp_blitz right?

Lets see…

SELECT [Object_id]
FROM sys.objects
WHERE name = 'sp_Blitz'

--Result:708197573 this will be the Object_id I will be using for the following Queries

SELECT
[Name],
OBJECT_DEFINITION([Object_id]) AS [Text]
FROM sys.objects
WHERE [Object_id] = 708197573 -- Object_id for sp_Blitz

 

RESULT:

 

Perfect – Lets copy and paste that to a new Query window

The image doesn’t show it very well but this is the very bottom of the Copy and paste and we can see that it has been cut short… hmm… well seeing as we were returning the results to the Grid there is a 8000 character row limit there per row so how can we get the rest of the code?

Unfortunately that’s one of the pitfalls of OBJECT_DEFINITION , however it is perfect for smaller Object definitions and will work just fine for those situations.

So where are all these object definitions stored?

sys.syscomments

Lets take a look at the table:

SELECT
OBJECT_NAME(id) as ObjectName,
ColID,
[Text]
FROM SYSCOMMENTS
WHERE id = 708197573 -- Object_id for sp_Blitz
ORDER BY OBJECT_NAME(id) ASC ,colid ASC 

 

There are a total of 78 rows for sp_blitz  and this is why I wanted to use it for this example , there is another reason too but we will get to that in a bit.

So the Definition is stored within sys.syscomments and each row contains 4000 characters.

So if we go back for a moment and look at our version that got truncated , lets search for a snippet of text near the end of the truncated definition:

SELECT
OBJECT_NAME(id) as ObjectName,
colid,
[Text]
FROM SYSCOMMENTS
WHERE id = 708197573 -- Object_id for sp_Blitz
and text like '%Make sure you understand what that trigger is doing - the less work it does, the better%'
ORDER BY OBJECT_NAME(id) ASC ,colid ASC

--Reached Column ID 11 of 78

 

As we can see , we only had the definition scripted out up to column id 11 out of 78.

 

Lets move onto an old favorite of mine….

Sp_HelpText

I like sp_HelpText but again as we are about to see – this too has its pitfalls…

 

 SP_HELPTEXT 'sp_blitz'

 

Ignore the caps attack 🙂

 

As we can see we have the definition scripted out over 6792 rows so all that is left is to copy and paste to a new window again right?

 

Lets do it…

 

Copied/ Pasted and Clicked Parse:

 

Won’t Parse as we have syntax issues…

 

And there is that pitfall I was talking about and reason number two for using sp_blitz as its gargantuan size and wide definition does not play nicely with sp_helptext below is an example of what has happened to the formatting:

 

 

Some of the wider rows in the Definition are being cut short with the remainder being populated on the following row,  for this stored procedure this occurred six times

We could of course go and fix these manually by bringing these inline as they once were , but we could alter sp_helptext too…or at least we could make a new version of it that will handle wide definitions…

 

lets sp_helptext sp_helptext

 

sp_helptext 'sp_helptext'

 

Original version snippet:

create procedure sys.sp_helptext
@objname nvarchar(776)
,@columnname sysname = NULL
as

set nocount on

declare @dbname sysname
,@objid int
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int --lengths of line feed carriage return
,@DefinedLength int

/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)

select @DefinedLength = 255
select @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
trailing blank spaces*/
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate catalog_default)

 

 

Lets change those 255 values to 4000:

 

Revision: 

,@SyscomText nvarchar(4000)
,@Line nvarchar(4000)

select @DefinedLength = 4000
select @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
trailing blank spaces*/
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(4000) collate catalog_default)

 

Using the revision above I created a new version of the sp_helptext stored procedure called ‘sp_helptext_extendedTextLimit’

 

Lets give it a whirl!

 

sp_helptext_extendedTextLimit 'sp_blitz'

 

As before lets copy and paste the results into a new query window and then click Parse:

Command(s) completed successfully.

 

There we have it, the complete definition with no truncation and no line breaks after 255 Characters 🙂

I like sp_helptext , its great for scripting View definitions too! also you can pass in the schema name and object name for objects that are not dbo such as:

sp_helptext ‘Alerting.AGCheck’

 

Thanks for reading.