sys.sql_modules catalog view which got introduced from SQL 2005 onwards. The sql_modules which includes the objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module.
SQL
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition, CAST(CASE WHEN sm.definition IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted] FROM sys.sql_modules AS sm JOIN sys.objects AS o ON sm.object_id = o.object_id ORDER BY o.type;
sql_modules to list only stored procedures then you can join with sys.procedures
SQL
SELECT SP.*, CASE WHEN SM.definition IS NULL THEN 'Encyrpted' END AS IsEncrypted FROM sys.procedures AS SP LEFT JOIN sys.sql_modules AS SM ON SP.object_id = SM.object_id
PowerShell :-
Change the Servername and DBName in the below code. Execute the below code from PowerShell-ISE
$server = "<ServerName>" $database = "<DBName>" [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server $db = New-Object ("Microsoft.SqlServer.Management.SMO.Database") # Get the database and Its objects $db = $srv.Databases[$database] $storedProcs = $db.StoredProcedures | Where-object {$_.IsEncrypted} $storedProcs|select name
Download the code: -
