Search and Print sprocs in SQL Server SUPER fast

Tired of using the SSMS Object Explorer to find sprocs/functions and the delay? 

Tired of the delay to view a sproc when clicking on the treeview in SSMS? Well here is the super duper fast way to view sprocs, If u know the name or part of the name of the sproc U want U can use the below script to BOOM....Get ur sproc definition printed in about...Oh....3 ms. Boom

1. Create the sp_LongPrint sproc (below), this is needed to PRINT large strings, because the default PRINT() function only allows like 8000, so create this first! credit from these guys : SP_LongPrint author

2. Run the below script GetSprocs.sql, enter the full or part name of your sproc u want, or a date it was altered, or search by a key word, Just plain bad ass. When U run the script, the sproc will show up in the Messages panel.

This is the difference between 1second and 1minute to find a sproc, Add that up over a day/week/month and U just saved a shitload of time, Time = $$$...now back to work...

--GetSprocs.sql

use master
go
set nocount on
DECLARE MY_CURSOR Cursor
FOR
SELECT r.Routine_Definition, routine_name
FROM INFORMATION_SCHEMA.Routines r
where
--last_altered > '2013-11-16 20:20:26.193' -- search by date?
routine_name like '%print%' -- search by name?
--ROUTINE_DEFINITION LIKE '%Metal_UID%' -- search by contents in sproc?
order by last_altered desc
DECLARE @sprocR VARCHAR(MAX)
set @sprocR = ''
OPEN MY_CURSOR
    DECLARE @sproc VARCHAR(MAX) , @name varchar(max), @txt varchar(max)
    FETCH NEXT FROM MY_CURSOR INTO @sproc, @name
    WHILE (@@FETCH_STATUS <> -1)
   BEGIN
        IF (@@FETCH_STATUS <> -2)
    select @name = 'dbo.' + @name
    select @txt = '-- *************************START procedure ' + @name
    exec [sp_LongPrint]  @txt
-- get sproc definition
    SELECT @txt= OBJECT_DEFINITION(OBJECT_ID(@name))
        exec [sp_LongPrint] @txt
    select @txt = 'go '
    exec [sp_LongPrint] @txt
    select @txt = '-- *************************END procedure ' + @name
    exec [sp_LongPrint] @txt
           FETCH NEXT FROM MY_CURSOR INTO @sproc, @name
    END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

--[sp_LongPrint]

USE master
GO
/****** Object:  StoredProcedure [dbo].[sp_LongPrint]    Script Date: 12/8/2013 8:53:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_LongPrint]
      @String NVARCHAR(MAX)

AS

/*
Example:

exec [sp_LongPrint] @string =
'This String
Exists to test
the system.'

*/

/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).

It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.

If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.

If it is passed a null value, it will do virtually nothing.

NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */

DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @offset tinyint /*tracks the amount of offset needed */


set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1
BEGIN

           
    IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN

           SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END
 

    PRINT SUBSTRING(@String, 1, @CurrentEnd)
                                               
    set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)
           
END /*End While loop*/