In my case, I get the same result because both of my procedures are of type “P”. This view inherits the type column from sys.objects so you can filter the results by procedure type if you wish.
Tableplus store procedure code#
The sys.procedures catalog stored procedure contains a row for each object that is a procedure of some kind, with = P, X, RF, and PC.Įxecuting the following code will return all stored procedures that the user either owns or on which the user has been granted some permission. Option 3 – The sys.procedures Catalog View If you want to return each stored procedure’s definition, you can join it with the sys.sql_modules system view. The sys.objects view doesn’t include a column for the object’s definition. WHERE type_desc = 'SQL_STORED_PROCEDURE' The type P is presumable for “Procedure”.Īnother way to do this is filter by the type_desc column: SELECT
Option 2 – The sys.objects System Catalog ViewĪnother way to return a list of stored procedures is to query the sys.objects system catalog view. The INFORMATION_SCHEMA.ROUTINES view also has a ROUTINE_DEFINITION column, so you can easily return each stored procedure’s definition if required. You can use the ROUTINES information schema view to get a list of all user-defined stored procedures in a database. Option 1 – The ROUTINES Information Schema View This article presents two ways to return a list of stored procedures in a SQL Server database.
0 kommentar(er)
