Viewing stored procedure in a window forms control from management studio

Question

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.

Senthilkumar · 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.

Senthilkumar · Answer

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.

David Smith · Answer

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

Viewing stored procedure in a window forms control from management studio

Answers (3)