CODEWITHSUNDEEP
c#sqlsql-server-2005stored-proceduresado.net
shamim
shamim

Reputation: 6770

Schema from stored procedure

I have a procedure, I want to read schema of the procedure. To retrieve view schema I use the query shown here. Same way I want to get schema of stored procedure. How to get it? Plz show some syntax.

public static DataTable SchemaReader(string tableName)
{
     string sql = string.Format("Select * from {0}", tableName);
     conn.Open();
     SqlCommand cmd = new SqlCommand(sql, conn);
     cmd.CommandType = CommandType.Text;
     SqlDataReader reader = cmd.ExecuteReader();

     DataTable schema = reader.GetSchemaTable();

     reader.Close();
     conn.Close();
     return schema;
}

If have any query plz ask.Thanks in advance.

Upvotes: 5

Views: 9198

Answers (6)

Manojkumar B Kolnurkar
Manojkumar B Kolnurkar

Reputation: 1

app.config:

<appSettings>
    <add key="Schema_Name" value ="[dev]."/> <!-- use any one [dev]. or [dbo]. -->
</appSettings>

C# read key:

string schema_Name = Configuration["Schema_Name"].ToString();

Executing the stored procedure:

SqlConnection objConn = new SqlConnection(Connection);
objConn.Open();
SqlCommand cmd = new SqlCommand("Exec WLTCVarification", objConn);
cmd.Parameters.Add("@SchemaName", SqlDbType.Text);
cmd.Parameters["@Schema_Name"].Value = schema_Name; // dev or dbo;
rowsAmount = (string)cmd.ExecuteScalar();
objConn.Close();

C# SQL query:

SqlConnection objConn = new SqlConnection(Connection);
objConn.Open();
SqlCommand cmd = new SqlCommand("select * from " + schema_Name + "receive_agv_onlyerror, objConn);
rowsAmount = (string)cmd.ExecuteScalar();
objConn.Close();

Upvotes: 0

Robert Achmann
Robert Achmann

Reputation: 2035

This is an answer that does not call the SP - if you do, you may inadvertently affect data:

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

Upvotes: 5

The Dag
The Dag

Reputation: 1841

I've created various code generators that use the output of stored procs. In my experience, most procedures that SELECT anything output their schema just the same if you call them with null (DbNull.Value) as the value for all parameters. You can get the parameter list itself from system views, though I find it convenient to use INFORMATION_SCHEMA.PARAMETERS.

By executing the procedure in a transaction and always rolling back you can safely execute stuff even when you have no idea what the procedure does.

You'll probably need a basic GUI and allow the user to modify the parameters - or a config file or some other way to provide parameter values for specific procedures. A stored proc may produce output with different schemas depending on the parameters, though I haven't seen many that do.

Upvotes: 0

FIre Panda
FIre Panda

Reputation: 6637

you could do

public static DataTable SchemaReader(string tableName) 
{      
  string sql = "MySP";//replace this with your store procedure name      
  conn.Open();      
  SqlCommand cmd = new SqlCommand(sql, conn);
  cmd.CommandType = CommandType.StoredProcedure;      
  SqlDataReader reader = cmd.ExecuteReader();       
  DataTable schema = reader.GetSchemaTable();       
  reader.Close();      
  conn.Close();      
  return schema; 
}

Hope this help

Upvotes: 9

Daniel Renshaw
Daniel Renshaw

Reputation: 34177

You could get information about a stored procedure's parameters but, without executing it, SQL Server cannot tell you the structure of the dataset(s) returned by the stored procedure. Since executing a stored procedure can have side effects, ADO.NET doesn't provide a method for telling you what the result set(s) would look like were the stored procedure to be executed. Furthermore, the result set(s) might change depending on the parameters passed to the procedure when it is executed.

Upvotes: 1

Gaurav
Gaurav

Reputation: 902

I am not getting your question clearly I think this would work with you

Select * 
from sys.objects 
where type='p' and name = (procedure name)

Replace your query with this and it will work fine

Upvotes: 0

Related Questions