Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What query will identify the names of stored procedures which are encrypted? Here's what I got so far -

select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
-- need another condition here to identify just the encrypted SPROC's
order by ROUTINE_NAME asc

Thanks in advance.

share|improve this question
add comment

migrated from stackoverflow.com Mar 15 '12 at 18:53

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 6 down vote accepted
SELECT name FROM sys.procedures
WHERE OBJECTPROPERTY([object_id], 'IsEncrypted') = 1;
share|improve this answer
1  
(And here's why I prefer sys.<whatever> over INFORMATION_SCHEMA.<whatever> - sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/… –  Aaron Bertrand Mar 15 '12 at 18:07
    
Great article. Very informative. –  Thomas Stringer Mar 15 '12 at 18:21
add comment
select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
and ROUTINE_DEFINITION is null
order by ROUTINE_NAME asc

An encrypted Stored Procedure will have a NULL ROUTINE_DEFINITION.

share|improve this answer
    
That seems to be an implicit assumption - are you sure that's the only case? I prefer to trust OBJECTPROPERTY since that's explicit. –  Aaron Bertrand Mar 15 '12 at 18:09
1  
@AaronBertrand Straight from BOL: "Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL." –  Thomas Stringer Mar 15 '12 at 18:18
    
But don't you consider it possible that there may be cases in the future where definition is NULL for other reasons? Also what does the ROUTINES view return for CLR procedures? –  Aaron Bertrand Mar 15 '12 at 18:20
    
@AaronBertrand Absolutely, and good point. +1 to your answer, I think that's the best approach as well. –  Thomas Stringer Mar 15 '12 at 18:21
2  
Thanks, I think they're equally valid today, I just think the catalog views and objectproperty are more future-proof. :-) –  Aaron Bertrand Mar 15 '12 at 18:28
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.