SQL Server, Generate scripts, unencrypted stored procedures only

Question

I have a SQL Server database with 3195 stored procedures. Most (approx 90%) of the stored procedures are encrypted (it's a 3rd party app) but there are many that are not encrypted (added by consultants later).

I need to get an overview of the unencrypted stored procedures created by consultants and then apply fixes so that they can work with UTC time.

I use Generate Scripts and select only stored procedures but that fails. I assume that is because the first stored procedure that it tries to write out is encrypted. Is there a way to write out the approx 300 stored procedures that are not encrypted?

James S · Answer 1 · 2013-10-31T14:19:40.587

2

EDIT: How about:

SELECT
    sp.NAME,
    ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
    sys.all_objects AS sp
    LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
    LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
    sp.type = 'P'
    AND SCHEMA_NAME(sp.schema_id)='dbo'
    AND ISNULL(smsp.definition, ssmsp.definition) IS NOT NULL

Encrypted stored procedures will have a NULL definition.

edited Oct 31 '13 at 14:19

answered Oct 30 '13 at 09:57

James S

3,558
16
25

That's pretty good but it can only write out the first 4000 characters of each sproc using management studio :-( – PeterO Oct 30 '13 at 15:10
try outputting the results to a file. That should get over the limit – James S Oct 30 '13 at 15:25
When I try that it limits it to 256 characters of the sproc :-( – PeterO Oct 30 '13 at 17:10
By tweaking management studio options I can get this number up to 4000 characters. – PeterO Oct 31 '13 at 10:29
OK, my bad - the system View INFORMATION_SCHEMA.ROUTINES has the ROUTINE_DEFINITION column declared as NVARCHAR(4000), hence the limit. I've edited the answer now, so that its basically the same as what SQL management studio's generate script function does. The limit should be gone now! – James S Oct 31 '13 at 14:18

score 1 · Accepted Answer · edited May 23 '17 at 10:29

It seems that using INFORMATION_SCHEMA limits the data returned to 4000 characters. See Mark Brittingham's anwser to this question: How do I programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?

I used what he suggested to write out the stored procedures: EXEC sp_HelpText 'your procedure name'

Starting with James S's suggestion and using Mark Brittingham's answer to the other question I put together the below t-sql. I then set Query Analyzer to output the results to text. The following SQL is not necessarily beautiful but it gets the job done. It generates a list of all the non-encrypted stored procedures and then loops through them and prints them out.

DECLARE @routineName nvarchar(255)
DECLARE cursorbob CURSOR FOR
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_DEFINITION IS NOT NULL

OPEN cursorbob   
FETCH NEXT FROM cursorbob INTO @routineName   

WHILE @@FETCH_STATUS = 0   
BEGIN   
    EXEC sp_HelpText @routineName

    FETCH NEXT FROM cursorbob INTO @routineName
END

CLOSE cursorbob   
DEALLOCATE cursorbob

score 0 · Answer 3 · answered Oct 30 '13 at 09:58

0

Try out this Decrypt Stored Procedure

get the script and then again encrypt the stored procedures like this Encrypt Stored Procedure

answered Oct 30 '13 at 09:58

Dusht

4,712
3
18
24

SQL Server, Generate scripts, unencrypted stored procedures only

3 Answers3