Read SQL Server database stored procedure list and parameter information

Get a list of database stored procedures:

SELECT * FROM dbo.sysobjects where OBJECTPROPERTY (ID, N ' isprocedure ') = 1 ORDER by name

Get parameter information for a stored procedure: (SQL method)

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]))

Get parameter information for a stored procedure: (ADO method)

Sqlcommandbuilder.deriveparameters (Mysqlcommand);

Get all tables for the database:

SELECT * FROM dbo.sysobjects where OBJECTPROPERTY (ID, N ' isusertable ') = 1 ORDER by name

To get 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.isnull Able 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 code example:

1. Get a list of database stored procedures:

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. Get parameter information for a stored procedure: (ADO 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 ();

    }

}

3. List all databases:

Using System;

Using System.Windows.Forms;

Using System.Collections.Generic;

Using System.Text;

Using System.Data;

Using System.Data.SqlClient;

            "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 (String.IsNullOrEmpty (connstring)) return null;

    string connstr = connstring;

    SqlConnection conn = new SqlConnection (CONNSTR);

    SqlCommand cmd = new SqlCommand (SQL, conn);

    Cmd.commandtype = CommandType.Text;

    Try

    {

        Conn. Open ();

        list<string> ret = new list<string> ();

        using (SqlDataReader myreader = cmd. ExecuteReader ())

        {

            while (Myreader.read ())

            {

                Ret. ADD (Myreader[0]. ToString ());

            }

        }

        if (ret. Count > 0) return ret. ToArray ();

        return null;

    }

    Finally

    {

        Conn. Close ();

    }

}

4. Get the table table list:

  "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 = defaults or DEFAULT constraints

   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 Filter 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");

}

5. Get a list of view views:

Public string[] Getviewlist ()

{

     Return GetList ("select name from sysobjects WHERE xtype= ' and name   <>   ' dtproperties ' ORDER by name ASC") ;

}

6. Get a list of function functions:

Public string[] Getfunctionlist ()

{

    Return GetList ("select name from sysobjects WHERE xtype= ' FN ' and name   <>   ' dtproperties ' ORDER by name ASC");

}

7. Get a list of stored procedures:

Public string[] Getstoredprocedureslist ()

{

    Return GetList ("select * from dbo.sysobjects where OBJECTPROPERTY (ID, N ' isprocedure ') = 1 ORDER BY name ASC");

}

8. Get the index of the table indexed index information:

Public treenode[] GetTableIndex (string tableName)

{

    if (String.IsNullOrEmpty (connstring)) return null;

    list<treenode> nodes = new list<treenode> ();

    string connstr = connstring;

    SqlConnection conn = new SqlConnection (CONNSTR);

    SqlCommand cmd = new SqlCommand (String.Format ("exec sp_helpindex {0}", TableName), conn);

    Cmd.commandtype = CommandType.Text;

    Try

    {

        Conn. Open ();

        using (SqlDataReader myreader = cmd. ExecuteReader ())

        {

            while (Myreader.read ())

            {

                TreeNode node = new TreeNode (myreader[0]. ToString (), 2, 2);/*index name*/

                Node. ToolTipText = String.Format ("{0}{1}{2}", Myreader[2]. ToString ()/*index keys*/, Environment.NewLine,

                    MYREADER[1]. ToString ()/*description*/);

                Nodes. ADD (node);

            }

        }

    }

    Finally

    {

        Conn. Close ();

    }

    if (nodes. count>0) return nodes. ToArray ();

    return null;

}

9. Get table,view,function, stored procedure parameters, field information:

Public string[] Gettablefields (string tableName)

{

    Return GetList (String.Format ("Select name from syscolumns where ID =object_id (' {0} ')", tableName));

}

10. Get a detailed definition of each field in table:

Public treenode[] Gettablefieldsdefinition (string TableName)

{

    if (String.IsNullOrEmpty (connstring)) return null;

    string connstr = connstring;

    list<treenode> nodes = new list<treenode> ();

    SqlConnection conn = new SqlConnection (CONNSTR);

    SqlCommand cmd = new SqlCommand (String.Format ("Select a.name,b.name,a.length,a.isnullable from syscolumns a,systypes B, sysobjects d where A.xtype=b.xusertype and A.id=d.id and d.xtype= ' U ' and a.id =object_id (' {0} '),

                  TABLENAME), conn);

    Cmd.commandtype = CommandType.Text;

    Try

    {

        Conn. Open ();

        using (SqlDataReader myreader = cmd. ExecuteReader ())

        {

            while (Myreader.read ())

            {

                TreeNode node = new TreeNode (myreader[0]. ToString (), 2, 2);

                Node. ToolTipText = String.Format ("Type: {0}{1}length: {2}{1}nullable: {3}", Myreader[1]. ToString ()/*type*/, Environment.NewLine,

                    MYREADER[2]. ToString ()/*length*/, Convert.toboolean (myreader[3]));

                Nodes. ADD (node);

            }

        }

        if (nodes. Count > 0) return nodes. ToArray ();

        return null;

    }

    Finally

    {

        Conn. Close ();

    }

}

11. Get the contents of the stored procedure:

Similar to "8. The index of the table is indexed by the SQL statement:EXEC sp_helptext ' stored procedure name '

12. Get the VIEW definition:

Similar to "8. Gets the index of the table indexed by the SQL statement:EXEC sp_helptext ' view name '

(The above code can be used in the code generator to list all information about the database)

Http://www.cnblogs.com/luluping/archive/2009/07/24/1530528.html

Read SQL Server database stored procedure list and parameter information