C# 以编程方式获取所有存储过程的简单方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/169330/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Simple way to programmatically get all stored procedures
提问by Douglas Anderson
Is there a way to get stored procedures from a SQL Server 2005 Express database using C#? I would like to export all of this data in the same manner that you can script it our using SQL Server Management Studio, without having to install the GUI.
有没有办法使用 C# 从 SQL Server 2005 Express 数据库中获取存储过程？我想以与您可以使用 SQL Server Management Studio 编写脚本相同的方式导出所有这些数据，而无需安装 GUI。
I've seen some references to do thing via the PowerShell but in the end a C# console app is what I really want.
我已经看到一些通过 PowerShell 执行操作的参考资料，但最终 C# 控制台应用程序才是我真正想要的。
To clarify....
澄清....
I'd like to script out the stored procedures. The list via the Select * from sys.proceduresis helpful, but in the end I need to script out each of these.
我想编写存储过程的脚本。通过 列出的列表Select * from sys.procedures很有帮助，但最后我需要编写出其中的每一个。
采纳答案by SqlACID
Just read the output of SELECT NAME from SYS.PROCEDURES , then call EXEC sp_HelpText SPNAME for each stored procedure, you'll get a record set with one line of text per row.
只需从 SYS.PROCEDURES 读取 SELECT NAME 的输出，然后为每个存储过程调用 EXEC sp_HelpText SPNAME，您将获得每行一行文本的记录集。
回答by Matt Hamilton
This blog postsuggests running this against your database:
这篇博文建议对你的数据库运行这个：
select * from sys.procedures
回答by Brian Kim
You can write C# code to run the following query on your database.
您可以编写 C# 代码以在您的数据库上运行以下查询。
Select * from sys.procedures
回答by Andy S
I think this is what you're really looking for:
我认为这就是您真正要寻找的：
select SPECIFIC_NAME,ROUTINE_DEFINITION from information_schema.routines
There are a ton of other useful columns in there too...
还有很多其他有用的专栏......
回答by Pavel Chuchuva
You can use SMO for that. First of all, add references to these assemblies to your project:
您可以为此使用 SMO。首先，将这些程序集的引用添加到您的项目中：
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.SmoEnum
- Microsoft.SqlServer.ConnectionInfo
- 微软.SqlServer.Smo
- Microsoft.SqlServer.SmoEnum
They are located in the GAC (browse to C:\WINDOWS\assembly folder).
它们位于 GAC 中（浏览到 C:\WINDOWS\assembly 文件夹）。
Use the following code as an example of scripting stored procedures:
使用以下代码作为编写存储过程的脚本示例：
using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
class Program
{
static void Main(string[] args)
{
Server server = new Server(@".\SQLEXPRESS");
Database db = server.Databases["Northwind"];
List<SqlSmoObject> list = new List<SqlSmoObject>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.StoredProcedure);
foreach (DataRow row in dataTable.Rows)
{
string sSchema = (string)row["Schema"];
if (sSchema == "sys" || sSchema == "INFORMATION_SCHEMA")
continue;
StoredProcedure sp = (StoredProcedure)server.GetSmoObject(
new Urn((string)row["Urn"]));
if (!sp.IsSystemObject)
list.Add(sp);
}
Scripter scripter = new Scripter();
scripter.Server = server;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ToFileOnly = true;
scripter.Options.FileName = @"C:\StoredProcedures.sql";
scripter.Script(list.ToArray());
}
}
See also: SQL Server: SMO Scripting Basics.
另请参阅：SQL Server：SMO 脚本基础。
回答by Cade Roux
;WITH ROUTINES AS (
-- CANNOT use INFORMATION_SCHEMA.ROUTINES because of 4000 character limit
SELECT o.type_desc AS ROUTINE_TYPE
,o.[name] AS ROUTINE_NAME
,m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.object_id = o.object_id
)
SELECT *
FROM ROUTINES
回答by Carl
You can use:
您可以使用：
DataTable dtProcs = sqlConn.GetSchema("Procedures", new string[] { databaseName });
DataTable dtProcParams = sqlConn.GetSchema("ProcedureParameters", new string[] { databaseName });
You can also get all sorts of other schema info like tables, indexes etc. if you need them.
如果需要，您还可以获得各种其他模式信息，如表、索引等。
You can get info on GetSchema() hereand info on the SQL Server Schema Collections here
您可以在此处获取有关 GetSchema() 的信息以及在此处获取有关 SQL Server 架构集合的信息
Edit: Sorry, this doesn't help with actually scripting the info, but I guess it's useful info to have.
编辑：抱歉，这对实际编写信息没有帮助，但我想这是有用的信息。
回答by Yordan Georgiev
begin
--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Products'
--Declare the Table variable
DECLARE @GeneratedStoredProcedures TABLE
(
Number INT IDENTITY(1,1), --Auto incrementing Identity column
name VARCHAR(300) --The string value
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT
declare @sqlCode varchar(max)
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT
--Populate the TABLE variable using some logic
INSERT INTO @GeneratedStoredProcedures SELECT name FROM sys.procedures where name like 'procGen_%'
--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1
--Determine the number of rows in the Table
SELECT @Count=max(Number) from @GeneratedStoredProcedures
--A variable to hold the currently selected value from the table
DECLARE @CurrentValue varchar(300);
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count
BEGIN
--Load current value from the Table
SELECT @CurrentValue = name FROM @GeneratedStoredProcedures WHERE Number = @CurrentDelimiterPositionVar
--Process the current value
--print @CurrentValue
set @sqlCode = 'drop procedure ' + @CurrentValue
print @sqlCode
--exec (@sqlCode)
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END
end
回答by bzlm
If you open up a can of reflector on sqlmetal.exe (a stand-alone part of LINQ-to-SQL that generates code from a database), you can see the SQL statements it uses to get a list of all stored procedures and functions. The SQL is similar, but not identical, to the one in this answer.
如果您在 sqlmetal.exe（从数据库生成代码的 LINQ-to-SQL 的独立部分）上打开一罐反射器，您可以看到它用于获取所有存储过程和函数列表的 SQL 语句. SQL 与此答案中的 SQL 相似，但不完全相同。
回答by Nag
public static void GenerateTableScript()
{
Server databaseServer = default(Server);//DataBase Server Name
databaseServer = new Server("yourDatabase Server Name");
string strFileName = @"C:\Images\Your FileName_" + DateTime.Today.ToString("yyyyMMdd") + ".sql"; //20120720`enter code here
if (System.IO.File.Exists(strFileName))
System.IO.File.Delete(strFileName);
List<SqlSmoObject> list = new List<SqlSmoObject>();
Scripter scripter = new Scripter(databaseServer);
Database dbUltimateSurvey = databaseServer.Databases["YourDataBaseName"];//DataBase Name
//Table scripting Writing
DataTable dataTable1 = dbUltimateSurvey.EnumObjects(DatabaseObjectTypes.Table);
foreach (DataRow drTable in dataTable1.Rows)
{
//string strTableSchema = (string)drTable["Schema"];
//if (strTableSchema == "dbo")
// continue;
Table dbTable = (Table)databaseServer.GetSmoObject(new Urn((string)drTable["Urn"]));
if (!dbTable.IsSystemObject)
if (dbTable.Name.Contains("SASTool_"))
list.Add(dbTable);
}
scripter.Server = databaseServer;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ToFileOnly = true;
scripter.Options.FileName = strFileName;
scripter.Options.DriAll = true;
scripter.Options.AppendToFile = true;
scripter.Script(list.ToArray());//Table Script completed
//Store Procedures scripting Writing
list = new List<SqlSmoObject>();
DataTable dataTable = dbUltimateSurvey.EnumObjects(DatabaseObjectTypes.StoredProcedure);
foreach (DataRow row in dataTable.Rows)
{
string sSchema = (string)row["Schema"];
if (sSchema == "sys" || sSchema == "INFORMATION_SCHEMA")
continue;
StoredProcedure sp = (StoredProcedure)databaseServer.GetSmoObject(
new Urn((string)row["Urn"]));
if (!sp.IsSystemObject)
if (sp.Name.Contains("custom_"))
list.Add(sp);
}
scripter.Server = databaseServer;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ToFileOnly = true;
scripter.Options.FileName = strFileName;
scripter.Options.DriAll = true;
scripter.Options.AppendToFile = true;
scripter.Script(list.ToArray()); // Stored procedure Script completed
}
