How to determine schema definitions of Stored Procedures in .Net

  • Article

 Here is some code to get an idea. Note that if a stored procedure sends back a temp table this does not work. If anyone has a way to do temp table schemas I would like to know.

 class Program
{

static void Main(string[] args)
{
string connectionString = GetConnectionString();
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Connect to the database then retrieve the schema information.
connection.Open();
DataTable table = connection.GetSchema("Procedures");
// Display the contents of the table.
DisplayData(table);
Console.WriteLine("Press any key to continue.");
Console.ReadKey();
}
}

private static string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
return "Data Source=<servername>;Database=<database>;Integrated Security=SSPI;";
}

private static void DisplayData(System.Data.DataTable table)
{
  foreach (System.Data.DataRow row in table.Rows)
{
    foreach (System.Data.DataColumn col in table.Columns)
{
      Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
}
    Console.WriteLine("============================");
}
}