读取SQLServer数据库存储过程列表及参数信息

2023年6月21日发(作者：)

读取SQLServer数据库存储过程列表及参数信息得到数据库存储过程列表：select * from ects 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]'))得到某个存储过程的参数信息：(⽅法)Parameters(mysqlcommand);得到数据库所有表：select * from ects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name得到某个表中的字段信息：select as ColumnName, er as ColumnOrder, as DataType, as DataTypeName,, able from umns c inner join ects t

on =

inner join es typ on = here OBJECTPROPERTY(, N'IsUserTable') = 1

and ='mytable' order by er;

C# 代码⽰例：

1. 得到数据库存储过程列表：using ent;

private void GetStoredProceduresList(){ string sql = "select * from ects 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); dType = ;

try { ();

using (SqlDataReader MyReader = eReader()) { while (()) { //Get stored procedure name (MyReader[0].ToString()); } } } finally { (); }}2. 得到某个存储过程的参数信息：(⽅法)

using ent;

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(); tion = conn; dText = "mystoredprocedurename"; dType = Procedure;

try { ();

Parameters(cmd);

foreach (SqlParameter var in ters) { if (f(var) == 0) continue;//Skip return value

((("Param: {0}{1}Type: {2}{1}Direction: {3}", terName, e, ng(), ng()))); } } finally { (); }

}3. 列出所有数据库：using System;using ;using c;using ;using ;using ent; private static string connString =

"Persist Security Info=True;timeout=5;Data Source=192.168.1.8;User ID=sa;Password=password";

///

/// 列出所有数据库///

/// public string[] GetDatabases(){ return GetList("SELECT name FROM sysdatabases order by name asc");}

private string[] GetList(string sql){ if (OrEmpty(connString)) return null;

string connStr = connString;

SqlConnection conn = new SqlConnection(connStr); SqlCommand cmd = new SqlCommand(sql, conn); dType = ;

try { ();

List ret = new List();

using (SqlDataReader MyReader = eReader()) { while (()) { (MyReader[0].ToString()); } }

if ( > 0) return y(); return null; } finally { (); }}

4. 得到Table表格列表：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 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = ⽇志 FN = 标量函数 IF = 内嵌表函数 P = 存储过程 PK = PRIMARY KEY 约束（类型是 K） RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = 触发器 U = ⽤户表 UQ = UNIQUE 约束（类型是 K） V = 视图 X = 扩展存储过程 */

public string[] GetTableList(){ return GetList("SELECT name FROM sysobjects WHERE xtype='U' AND name <> 'dtproperties' order by name asc");}5. 得到View视图列表：public string[] GetViewList(){ return GetList("SELECT name FROM sysobjects WHERE xtype='V' AND name <> 'dtproperties' order by name asc");}6. 得到Function函数列表：public string[] GetFunctionList(){ return GetList("SELECT name FROM sysobjects WHERE xtype='FN' AND name <> 'dtproperties' order by name asc");}7. 得到存储过程列表：public string[] GetStoredProceduresList(){ return GetList("select * from ects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name asc");}8. 得到table的索引Index信息：public TreeNode[] GetTableIndex(string tableName){ if (OrEmpty(connString)) return null;

List nodes = new List(); string connStr = connString;

SqlConnection conn = new SqlConnection(connStr); SqlCommand cmd = new SqlCommand(("exec sp_helpindex {0}", tableName), conn); dType = ;

try { ();

using (SqlDataReader MyReader = eReader()) { while (()) { TreeNode node = new TreeNode(MyReader[0].ToString(), 2, 2);/*Index name*/ pText = ("{0}{1}{2}", MyReader[2].ToString()/*index keys*/, e, MyReader[1].ToString()/*Description*/); (node); } } } finally { (); }

if(>0) return y (); return null;}9. 得到Table，View，Function，存储过程的参数，Field信息：public string[] GetTableFields(string tableName){ return GetList(("select name from syscolumns where id =object_id('{0}')", tableName));}10. 得到Table各个Field的详细定义：public TreeNode[] GetTableFieldsDefinition(string TableName){ if (OrEmpty(connString)) return null;

string connStr = connString;

List nodes = new List();

SqlConnection conn = new SqlConnection(connStr); SqlCommand cmd = new SqlCommand(("select ,,,able from syscolumns a,systypes b,sysobjects d where

=ype and = and ='U' and =object_id('{0}')", TableName), conn); dType = ;

try { ();

using (SqlDataReader MyReader = eReader()) { while (()) { TreeNode node = new TreeNode(MyReader[0].ToString(), 2, 2); pText = ("Type: {0}{1}Length: {2}{1}Nullable: {3}", MyReader[1].ToString()/*type*/, e, MyReader[2].ToString()/*length*/, ean(MyReader[3])); (node); } }

if ( > 0) return y(); return null; } finally { (); }

}11. 得到存储过程内容：类似“8. 得到table的索引Index信息”，SQL语句为：EXEC Sp_HelpText '存储过程名'

12. 得到视图View定义：类似“8. 得到table的索引Index信息”，SQL语句为：EXEC Sp_HelpText '视图名'

(以上代码可⽤于代码⽣成器，列出数据库的所有信息)

2023年6月21日发(作者：)

读取SQLServer数据库存储过程列表及参数信息得到数据库存储过程列表：select * from ects 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]'))得到某个存储过程的参数信息：(⽅法)Parameters(mysqlcommand);得到数据库所有表：select * from ects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name得到某个表中的字段信息：select as ColumnName, er as ColumnOrder, as DataType, as DataTypeName,, able from umns c inner join ects t

on =

inner join es typ on = here OBJECTPROPERTY(, N'IsUserTable') = 1

and ='mytable' order by er;

C# 代码⽰例：

1. 得到数据库存储过程列表：using ent;

private void GetStoredProceduresList(){ string sql = "select * from ects 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); dType = ;

try { ();

using (SqlDataReader MyReader = eReader()) { while (()) { //Get stored procedure name (MyReader[0].ToString()); } } } finally { (); }}2. 得到某个存储过程的参数信息：(⽅法)

using ent;

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(); tion = conn; dText = "mystoredprocedurename"; dType = Procedure;

try { ();

Parameters(cmd);

foreach (SqlParameter var in ters) { if (f(var) == 0) continue;//Skip return value

((("Param: {0}{1}Type: {2}{1}Direction: {3}", terName, e, ng(), ng()))); } } finally { (); }

}3. 列出所有数据库：using System;using ;using c;using ;using ;using ent; private static string connString =

"Persist Security Info=True;timeout=5;Data Source=192.168.1.8;User ID=sa;Password=password";

///

/// 列出所有数据库///

/// public string[] GetDatabases(){ return GetList("SELECT name FROM sysdatabases order by name asc");}

private string[] GetList(string sql){ if (OrEmpty(connString)) return null;

string connStr = connString;

SqlConnection conn = new SqlConnection(connStr); SqlCommand cmd = new SqlCommand(sql, conn); dType = ;

try { ();

List ret = new List();

using (SqlDataReader MyReader = eReader()) { while (()) { (MyReader[0].ToString()); } }

if ( > 0) return y(); return null; } finally { (); }}

4. 得到Table表格列表：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 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = ⽇志 FN = 标量函数 IF = 内嵌表函数 P = 存储过程 PK = PRIMARY KEY 约束（类型是 K） RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = 触发器 U = ⽤户表 UQ = UNIQUE 约束（类型是 K） V = 视图 X = 扩展存储过程 */

public string[] GetTableList(){ return GetList("SELECT name FROM sysobjects WHERE xtype='U' AND name <> 'dtproperties' order by name asc");}5. 得到View视图列表：public string[] GetViewList(){ return GetList("SELECT name FROM sysobjects WHERE xtype='V' AND name <> 'dtproperties' order by name asc");}6. 得到Function函数列表：public string[] GetFunctionList(){ return GetList("SELECT name FROM sysobjects WHERE xtype='FN' AND name <> 'dtproperties' order by name asc");}7. 得到存储过程列表：public string[] GetStoredProceduresList(){ return GetList("select * from ects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name asc");}8. 得到table的索引Index信息：public TreeNode[] GetTableIndex(string tableName){ if (OrEmpty(connString)) return null;

List nodes = new List(); string connStr = connString;

SqlConnection conn = new SqlConnection(connStr); SqlCommand cmd = new SqlCommand(("exec sp_helpindex {0}", tableName), conn); dType = ;

try { ();

using (SqlDataReader MyReader = eReader()) { while (()) { TreeNode node = new TreeNode(MyReader[0].ToString(), 2, 2);/*Index name*/ pText = ("{0}{1}{2}", MyReader[2].ToString()/*index keys*/, e, MyReader[1].ToString()/*Description*/); (node); } } } finally { (); }

if(>0) return y (); return null;}9. 得到Table，View，Function，存储过程的参数，Field信息：public string[] GetTableFields(string tableName){ return GetList(("select name from syscolumns where id =object_id('{0}')", tableName));}10. 得到Table各个Field的详细定义：public TreeNode[] GetTableFieldsDefinition(string TableName){ if (OrEmpty(connString)) return null;

string connStr = connString;

List nodes = new List();

SqlConnection conn = new SqlConnection(connStr); SqlCommand cmd = new SqlCommand(("select ,,,able from syscolumns a,systypes b,sysobjects d where

=ype and = and ='U' and =object_id('{0}')", TableName), conn); dType = ;

try { ();

using (SqlDataReader MyReader = eReader()) { while (()) { TreeNode node = new TreeNode(MyReader[0].ToString(), 2, 2); pText = ("Type: {0}{1}Length: {2}{1}Nullable: {3}", MyReader[1].ToString()/*type*/, e, MyReader[2].ToString()/*length*/, ean(MyReader[3])); (node); } }

if ( > 0) return y(); return null; } finally { (); }

}11. 得到存储过程内容：类似“8. 得到table的索引Index信息”，SQL语句为：EXEC Sp_HelpText '存储过程名'

12. 得到视图View定义：类似“8. 得到table的索引Index信息”，SQL语句为：EXEC Sp_HelpText '视图名'

(以上代码可⽤于代码⽣成器，列出数据库的所有信息)