Do you know how to script database objects from a script?

Why yes? With this script it’s easy as ABC. Just fill in the list of items you want scripted and off you go. This way you can script any stored procedure, view, function or anything else that resides in the sys.sql_modules table.

There is a small piece of code that will loop through the definition if it’s longer than 4000 characters long. The PRINT statement will only print the first 4000 characters of a (n)varchar variable. To work around this, we split off 4000 characters, then find the last carriage return and throw away the remainder of the string. This way we don’t get any nasty splits on the screen. Then we print that string and we subtract the length of that string from the original string, and we go again.

DECLARE
    @FQObjectName NVARCHAR(257),
    @Definition   NVARCHAR(MAX),
    @Separator    NVARCHAR(80)  = REPLICATE('-', 80),
    @PrintBlock   NVARCHAR(4000)

DECLARE @tbDefinition TABLE (FQObjectName NVARCHAR(257), Definition NVARCHAR(MAX))

INSERT INTO
    @tbDefinition (FQObjectName, Definition)
SELECT
    CONCAT_WS('.', OBJECT_SCHEMA_NAME(SM.object_id), OBJECT_NAME(SM.object_id)) AS FQObjectName,
    SM.definition
FROM
    sys.sql_modules AS SM
WHERE
    OBJECT_NAME(SM.object_id) IN ('UnqualifiedObjectOne', 'UnqualifiedObjectOne')

DECLARE cDefinitions CURSOR FOR
SELECT  TD.FQObjectName, TD.Definition FROM @tbDefinition AS TD

OPEN cDefinitions

FETCH NEXT FROM cDefinitions
INTO
    @FQObjectName,
    @Definition

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Separator
    PRINT CONCAT('-- ', @FQObjectName)
    PRINT @Separator

    WHILE LEN(@Definition) > 4000
    BEGIN
        -- Fix because PRINT is limited to 4000 chars
        SET @PrintBlock = REVERSE(LEFT(@Definition, 4000))
        SET @PrintBlock = REVERSE(SUBSTRING(@PrintBlock, CHARINDEX(char(13), @PrintBlock), 99999))

        PRINT @PrintBlock
        SET @Definition = RIGHT(@Definition, LEN(@Definition) - LEN(@PrintBlock))
    END

    PRINT @Definition
    PRINT 'GO'
    FETCH NEXT FROM cDefinitions
    INTO
        @FQObjectName,
        @Definition
END

CLOSE cDefinitions
DEALLOCATE cDefinitions
GO

DECLARE

@FQObjectName NVARCHAR(257),

@Definition NVARCHAR(MAX),

@Separator NVARCHAR(80) = REPLICATE('-', 80),

@PrintBlock NVARCHAR(4000)

DECLARE @tbDefinition TABLE (FQObjectName NVARCHAR(257), Definition NVARCHAR(MAX))

INSERT INTO

@tbDefinition (FQObjectName, Definition)

SELECT

CONCAT_WS('.', OBJECT_SCHEMA_NAME(SM.object_id), OBJECT_NAME(SM.object_id)) AS FQObjectName,

SM.definition

FROM

sys.sql_modules AS SM

WHERE

OBJECT_NAME(SM.object_id) IN ('UnqualifiedObjectOne', 'UnqualifiedObjectOne')

DECLARE cDefinitions CURSOR FOR

SELECT TD.FQObjectName, TD.Definition FROM @tbDefinition AS TD

OPEN cDefinitions

FETCH NEXT FROM cDefinitions

INTO

@FQObjectName,

@Definition

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @Separator

PRINT CONCAT('-- ', @FQObjectName)

PRINT @Separator

WHILE LEN(@Definition) > 4000

BEGIN

-- Fix because PRINT is limited to 4000 chars

SET @PrintBlock = REVERSE(LEFT(@Definition, 4000))

SET @PrintBlock = REVERSE(SUBSTRING(@PrintBlock, CHARINDEX(char(13), @PrintBlock), 99999))

PRINT @PrintBlock

SET @Definition = RIGHT(@Definition, LEN(@Definition) - LEN(@PrintBlock))

END

PRINT @Definition

PRINT 'GO'

FETCH NEXT FROM cDefinitions

INTO

@FQObjectName,

@Definition

END

CLOSE cDefinitions

DEALLOCATE cDefinitions