sp_sproc_columns – List out parameters of stored procedure

There are several methods to list out parameters used in the stored procedure. One of them is to make use of System catalogue procedure named SP_SPROC_COLUMNS

Let us create the following stored procedure

use tempdb

GO

create procedure proc_add_customers
(
@customer_name varchar(100),
@dob date,
@address varchar(200),
@contact_no varchar(15)
)
as
insert into customers(customer_name,dob,address,concat_no)
select @customer_name,@dob,@address,@contact_no

Now to know the parameters of this store procedure , use sp_sproc_columns
as shown below

EXEC sp_sproc_columns proc_add_customers

The result is shown in the following picture. Look at the column named COLUMN_NAME

sp_col

For tables, you have SP_COLUMNS. Similarly, for Stored Procedures, you have SP_SPROC_COLUMNS.

Note : You can also use the same catalogue procedure for User Defined function as well.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s