Viewing stored procedure in a window forms control from management studio

David Smith

14y1.8k

Is there away to show the actually stored procedure from SQL management studio in windows application, Basically I pulling information from a specific database from a server, and then showing the user whats going on. Now that I am pulling information. I want to give the user the ability to look at an read only view inside some type of windows control, is there away. if so how do you open an stored from the database itself and view it.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Accepted answer
Hi Darnell,

As you know you can show the stored procedures list in any control like TreeView,etc., using the following statement

SELECT * FROM sys.procedures

This will returns the list and when user click on any stored procedure name you can get the definition of the stored procedure schema from the database.

1) SELECT text FROM syscomments WHERE id = object_id('Proc_SplitWordsToChar')

2) SP_HELPTEXT Proc_SplitWordsToChar

3) SELECT * FROM sys.all_sql_modules WHERE object_id = object_id('Proc_SplitWordsToChar')

You can use the following statement

//Connection string to the Student database
string _ConnectionString = "Data source=servernameoripaddress;Initial Catalog=Students;Integerity Security=true;";

//Dataset will have the list of procedures.
SqlConnection sqlConnection = new SqlConnection(_ConnectionString);
sqlConnection.Open();
string strProcedures = "SELECT text FROM syscomments WHERE id = object_id('Proc_SplitWordsToChar')";
SqlCommand sqlCommand = new SqlCommand(strProcedures,sqlConnection);
sqlCommand.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
sqlConnection.Close();

I have used the query. Only you need to pass the correct database and query. There are three ways to get and you can use the sp_helptext system stored procedure aswell.
0
Senthilkumar14y ago
Hi,

Yes! you are right!!.

It will give the actual stored procedure text. You can show it in the multiline text box as well.

The Proc_SplitWordsToChar is the actual stored procedure name. Similarly when user click on any stored procedure name, you can pass it as parameter in the query. But the underlying database should be correct.

0
David Smith14y ago
Explain 'Proc_SplitWordsToChar', is that going to give me the entire query, what would the result look like.
Say for instance if I have a stored procedures that says

CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location

Will the output display the text below. I am basically trying to display this stored procedure in a control, when I query from database

CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
0