getting stored procedure schema

Question

0.00/5 (No votes)

See more:

I want to get only schema of stored procedure... Not data, as a result set in my application...
Anyone knows how could i get the schema?

Thanks in advance...

Posted 17-Sep-12 4:03am

san83deep

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Amy · Answer 1 · 2012-09-17T04:07:00

You need to execute the command and get the schema of SP using SqlDataReader.GetSchemaTable().
Try this:

C#

public static DataTable SchemaReader(string tableName) 
{      
  conn.Open();      
  SqlCommand cmd = new SqlCommand("MyStoredProcedure", conn);
  cmd.CommandType = CommandType.StoredProcedure;      
  SqlDataReader reader = cmd.ExecuteReader();       
  DataTable schema = reader.GetSchemaTable();       
  reader.Close();      
  conn.Close();      
  return schema; 
}

--Amit

Rob Achmann · Answer 2 · 2015-04-16T05:08:00

You do not want to call the SP as you may affect some data in the DB.

Instead:

SQL

SELECT * FROM sys.dm_exec_describe_first_result_set ('owner.sprocName', NULL, 0) ;

This returns the result set:

is_hidden
column_ordinal 
name
is_nullable 
system_type_id 
system_type_name    
max_length 
precision 
scale 
collation_name      
user_type_id 
user_type_database  
user_type_schema    
user_type_name      
assembly_qualified_type_name
xml_collection_id 
xml_collection_database
xml_collection_schema  
xml_collection_name    
is_xml_document 
is_case_sensitive 
is_fixed_length_clr_type 
source_server   
source_database 
source_schema   
source_table    
source_column   
is_identity_column 
is_part_of_unique_key 
is_updateable 
is_computed_column 
is_sparse_column_set 
ordinal_in_order_by_list 
order_by_is_descending 
order_by_list_length 
error_number 
error_severity 
error_state 
error_message 
error_type  
error_type_desc

getting stored procedure schema

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0