TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
David Smith
NA
2k
0
Viewing stored procedure in a window forms control from management studio
Apr 8 2012 11:04 AM
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.
Reply
Answers (
3
)
0
Senthilkumar
0
10.8k
4m
Apr 8 2012 1:03 PM
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.
Accepted Answer
0
Senthilkumar
0
10.8k
4m
Apr 8 2012 8:59 PM
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 Smith
0
2k
0
Apr 8 2012 1:34 PM
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
How to find the tables being affected, the actually relationship, and type of query used in the stored procedure?
Viewing the database diagram in winforms control