Simple way to programmatically get all stored procedures
Solution 1
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.
Solution 2
You can use SMO for that. First of all, add references to these assemblies to your project:
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.SmoEnum
They are located in the GAC (browse to C:\WINDOWS\assembly folder).
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.
Solution 3
;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
Solution 4
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
}
Solution 5
This blog post suggests running this against your database:
select * from sys.procedures
Douglas Anderson
Interested in abstracted tools and languages that allow focus to shift from programming to solving business problems. if (user.dad == true && user.drink == "rum" && user.arcade == "1942") { name = "douglas anderson"; }
Updated on July 05, 2022Comments
-
Douglas Anderson almost 2 years
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.
I've seen some references to do thing via the PowerShell but in the end a C# console app is what I really want.
To clarify....
I'd like to script out the stored procedures. The list via the
Select * from sys.procedures
is helpful, but in the end I need to script out each of these. -
Cade Roux over 15 yearsROUTINE_DEFINITION in INFORMATION_SCHEMA (in SQL 2005) unfortunately is truncated for long SPs. I will track down my code for this and post it in another answer.
-
user423430 over 12 yearsmay also find required references in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
-
user423430 over 12 yearsI also had to reference Microsoft.SqlServer.Management.Sdk.Sfc
-
Alexander Garden about 11 yearsAs of Sql Server 2008R2, the URN class has moved to namespace Microsoft.SqlServer.Management.Sdk.Sfc in assembly Microsoft.SqlServer.Management.Sdk.Sfc.
-
Sam Saarian almost 6 yearsSELECT NAME from SYS.PROCEDURES returns system stored procedures, not user defined stored procedures. If you run this statement from the SSMS then yes, you will get ALL stored procedures. Running this statement through the c# code will return, as I said, system stored procedures only.