This article presents two ways to return a list of stored procedures in a SQL Server database.
Tag: stored procedures
How to Encrypt a Stored Procedure in SQL Server
In SQL Server, you can encrypt a stored procedure at the time you create it, or you can alter it later to include encryption.
To create a stored procedure with T-SQL, you use the CREATE PROCEDURE syntax. To encrypt it, you add the WITH ENCRYPTION argument.
You can also use the same argument to encrypt an existing procedure when using ALTER PROCEDURE.
“Internal connection fatal error” When Executing a Natively Compiled Stored Procedure in SQL Server 2019 (Known Bug)
If you’re getting the following error (or similar error) while trying to execute a natively compiled stored procedure in SQL Server 2019, read on.
Query failed: Internal connection fatal error.
How to Create a Schema Bound Stored Procedure in SQL Server
If you’ve ever created a schema bound UDF, you’ll know that schema binding it is just a matter of adding WITH SCHEMABINDING to your definition. The same applies when you create a schema bound view.
Stored procedures are a bit different.
Only natively compiled stored procedures can be schema bound. In fact, natively compiled stored procedures must be schema bound. You can’t create a natively compiled stored procedure without schema binding it.
But if you try to schema bind a regular (non-natively compiled) procedure, you’ll get an error.
Check if an Object is a Stored Procedure by Using OBJECTPROPERTY() in SQL Server
In SQL Server you can use the OBJECTPROPERTY() function to find out whether or not an object is a stored procedure.
This function accepts two parameters: the object ID, and the property for which you’re checking it for.
Therefore, you can pass the object ID as the first argument, and IsProcedure as the second, and the function will return either a 1 or a 0 depending on whether or not it’s a stored procedure.
A return value of 1 means that it is a stored procedure, and a value of 0 means that it’s not.
4 Ways to Get a Stored Procedure’s Definition using Transact-SQL
This article presents 4 ways of using T-SQL to get the definition of a stored procedure in SQL Server.
The definition is the actual T-SQL statement used to create the stored procedure.
Three of the methods here are exactly the same as the ones used for returning the definition of a view (except here, they’re being used on stored procedures instead of views).
Return Stored Procedures & Functions in a SQL Server Database: ROUTINES (T-SQL Examples)
In SQL Server, you can use the Transact-SQL ROUTINES system information schema view to return a list of stored procedures and functions in the current database.
More specifically, it returns a list of all stored procedures and functions that can be accessed by the current user in the current database.
You can also use ROUTINES simply to return information about a specific procedure or function if required.
To use this view, use the fully qualified name of INFORMATION_SCHEMA.ROUTINES.
How to Get a List of All Languages in SQL Server (T-SQL)
Here are a couple of ways to return information on all languages in SQL Server. One method uses a system compatibility view, the other method executes a system stored procedure. Both methods return the same result.
How to Create a Stored Procedure in SQL Server
To create a stored procedure in SQL Server:
- Click New Query on the SSMS toolbar
- Type (or paste) a CREATE PROCEDURE statement (example below)
- Click the Execute button on the toolbar
This will add the stored procedure to the database. You can see it under the Stored Procedures node in the Object Explorer.
Below are screenshots and more detail for the above steps.
What is a Stored Procedure?
A stored procedure is a series of SQL statements compiled and saved to the database.
Stored procedures can be as simple or as complex as you like. However, one of the benefits of stored procedures is that they allow you to store complex scripts on the server.
Stored procedures often contain conditional programming such as IF... ELSE statements for example. Stored procedures can also accept parameters.