6

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.

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

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

11
SELECT name FROM sys.procedures
WHERE OBJECTPROPERTY([object_id], 'IsEncrypted') = 1;
3
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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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