得到数据库存储过程列表：
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name
得到某个存储过程的参数信息：(SQL方法)
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]'))
得到某个存储过程的参数信息：(Ado.net方法)
SqlCommandBuilder.DeriveParameters(mysqlcommand);
得到数据库所有表：
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name
得到某个表中的字段信息：
select c.name as ColumnName, c.colorder as ColumnOrder, c.xtype as DataType, typ.name as DataTypeName, c.Length, c.isnullable from dbo.syscolumns c inner join dbo.sysobjects t
on c.id = t.id
inner join dbo.systypes typ on typ.xtype = c.xtype
where OBJECTPROPERTY(t.id, N'IsUserTable') = 1
and t.name='mytable' order by c.colorder;
C# Ado.net代码示例：
1. 得到数据库存储过程列表：
using System.Data.SqlClient;
private void GetStoredProceduresList()
{
string sql = "select * from dbo.sysobjects 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);
cmd.CommandType = CommandType.Text;
try
{
conn.Open();
using (SqlDataReader MyReader = cmd.ExecuteReader())
{
while (MyReader.Read())
{
//Get stored procedure name
this.listBox1.Items.Add(MyReader[0].ToString());
}
}
}
finally
{
conn.Close();
}
}
2. 得到某个存储过程的参数信息：(Ado.net方法)
using System.Data.SqlClient;
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();
cmd.Connection = conn;
cmd.CommandText = "mystoredprocedurename";
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
SqlCommandBuilder.DeriveParameters(cmd);
foreach (SqlParameter var in cmd.Parameters)
{
if (cmd.Parameters.IndexOf(var) == 0) continue;//Skip return value
MessageBox.Show((String.Format("Param: {0}{1}Type: {2}{1}Direction: {3}",
var.ParameterName,
Environment.NewLine,
var.SqlDbType.ToString(),
var.Direction.ToString())));
}
}
finally
{
conn.Close();
}
}
