Read the SQL Server database Stored Procedure List and parameter information
Obtain the database Stored Procedure List:
Select * from dbo. sysobjects where OBJECTPROPERTY (id, N 'isprocedure ') = 1 order by name
Obtain the parameter information of a stored procedure: (SQL method)
Select * from syscolumns where ID in
(SELECT id FROM sysobjects as
Where objectproperty (id, n' IsProcedure ') = 1
And id = object_id (n' [dbo]. [mystoredprocedurename] ')
Obtain the parameter information of a stored procedure: (ado.net method)
SqlCommandBuilder. DeriveParameters (mysqlcommand );
Obtain all tables in the database:
Select * from dbo. sysobjects where OBJECTPROPERTY (id, N 'isusertable') = 1 order by name
Obtain the field information in a table:
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. policypes typ on typ. xtype = c. xtype
Where OBJECTPROPERTY (t. id, n'isusertable') = 1
And t. name = 'mytable' order by c. colorder;
C # ado.net sample code:
1. Obtain the database Stored Procedure List:
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. Obtain the parameter information of a stored procedure: (Ado.net method)
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 ();
}
}