C# Example of reading SQLServer database stored procedure list and parameter information

select * from  where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name

select * from syscolumns where ID in
 (SELECT id FROM sysobjects as a
  WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1
  and id = object_id(N'[dbo].[mystoredprocedurename]'))

(mysqlcommand);

select * from  where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name

select  as ColumnName,  as ColumnOrder,  as DataType,  as DataTypeName, ,  from  c inner join  t
on  = 
inner join  typ on  = 
where OBJECTPROPERTY(, N'IsUserTable') = 1
and ='mytable' order by ;

using ;
private void GetStoredProceduresList()
{
  string sql = "select * from  where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name";
  string connStr = @"Data Source=(local);Initial Catalog=mydatabase; Integrated Security=True; Connection Timeout=1;";
  SqlConnection conn = new SqlConnection(connStr);
  SqlCommand cmd = new SqlCommand(sql, conn);
   = ;
  try
  {
    ();
    using (SqlDataReader MyReader = ())
    {
      while (())
      {
        //Get stored procedure name
        this.(MyReader[0].ToString());
      }
    }
  }
  finally
  {
    ();
  }
}

using ;
private void GetArguments()
{
  string connStr = @"Data Source=(local);Initial Catalog=mydatabase; Integrated Security=True; Connection Timeout=1;";
  SqlConnection conn = new SqlConnection(connStr);
  SqlCommand cmd = new SqlCommand();
   = conn;
   = "mystoredprocedurename";
   = ;
  try
  {
    ();
    (cmd);
    foreach (SqlParameter var in )
    {
      if ((var) == 0) continue;//Skip return value
      ((("Param: {0}{1}Type: {2}{1}Direction: {3}",
        ,
        ,
        (),
        ())));
    }
  }
  finally
  {
    ();
  }
}

using System;
using ;
using ;
using ;
using ;
using ;
private static string connString =
      "Persist Security Info=True;timeout=5;Data Source=192.168.1.8;User ID=sa;Password=password";
/// <summary>
/// List all databases/// </summary>
/// <returns></returns>
public string[] GetDatabases()
{
  return GetList("SELECT name FROM sysdatabases order by name asc");
}
private string[] GetList(string sql)
{
  if ((connString)) return null;
  string connStr = connString;
  SqlConnection conn = new SqlConnection(connStr);
  SqlCommand cmd = new SqlCommand(sql, conn);
   = ;
  try
  {
    ();
    List<string> ret = new List<string>();
    using (SqlDataReader MyReader = ())
    {
      while (())
      {
        (MyReader[0].ToString());
      }
    }
    if ( > 0) return ();
    return null;
  }
  finally
  {
    ();
  }
}

private static string connString =
 "Persist Security Info=True;timeout=5;Data Source=192.168.1.8;Initial Catalog=myDb;User ID=sa;Password=password";
/* select name from sysobjects where xtype='u' ---
 C = CHECK constraint
 D = Default value or DEFAULT constraint
 F = FOREIGN KEY Constraint
 L = log
 FN = scalar function
 IF = Inline Table Function
 P = Stored Procedure
 PK = PRIMARY KEY constraint (type is K)
 RF = Copy filtering stored procedure
 S = System Table
 TF = table function
 TR = Trigger
 U = User Table
 UQ = UNIQUE constraint (type is K)
 V = View
 X = Extended stored procedure
 */
public string[] GetTableList()
{
  return GetList("SELECT name FROM sysobjects WHERE xtype='U' AND name  <>  'dtproperties' order by name asc");
}

public string[] GetViewList()
{
   return GetList("SELECT name FROM sysobjects WHERE xtype='V' AND name  <>  'dtproperties' order by name asc");
}

public string[] GetFunctionList()
{
  return GetList("SELECT name FROM sysobjects WHERE xtype='FN' AND name  <>  'dtproperties' order by name asc");
}

public string[] GetStoredProceduresList()
{
  return GetList("select * from  where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name asc");
}

public TreeNode[] GetTableIndex(string tableName)
{
  if ((connString)) return null;
  List<TreeNode> nodes = new List<TreeNode>();
  string connStr = connString;
  SqlConnection conn = new SqlConnection(connStr);
  SqlCommand cmd = new SqlCommand(("exec sp_helpindex {0}", tableName), conn);
   = ;
  try
  {
    ();
    using (SqlDataReader MyReader = ())
    {
      while (())
      {
        TreeNode node = new TreeNode(MyReader[0].ToString(), 2, 2);/*Index name*/
         = ("{0}{1}{2}", MyReader[2].ToString()/*index keys*/, ,
          MyReader[1].ToString()/*Description*/);
        (node);
      }
    }
  }
  finally
  {
    ();
  }
  if(>0) return  ();
  return null;
}

public string[] GetTableFields(string tableName)
{
  return GetList(("select name from syscolumns where id =object_id('{0}')", tableName));
}

public TreeNode[] GetTableFieldsDefinition(string TableName)
{
  if ((connString)) return null;
  string connStr = connString;
  List<TreeNode> nodes = new List<TreeNode>();
  SqlConnection conn = new SqlConnection(connStr);
  SqlCommand cmd = new SqlCommand(("select ,,, from syscolumns a,systypes b,sysobjects d where = and = and ='U' and  =object_id('{0}')",
         TableName), conn);
   = ;
  try
  {
    ();
    using (SqlDataReader MyReader = ())
    {
      while (())
      {
        TreeNode node = new TreeNode(MyReader[0].ToString(), 2, 2);
         = ("Type: {0}{1}Length: {2}{1}Nullable: {3}", MyReader[1].ToString()/*type*/, ,
          MyReader[2].ToString()/*length*/, (MyReader[3]));
        (node);
      }
    }
    if ( > 0) return ();
    return null;
  }
  finally
  {
    ();
  }
}