
How do I programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui
By: a guest on
Feb 26th, 2012 | syntax:
None | size: 1.45 KB | hits: 34 | expires: Never
SELECT
NULL AS [Text],
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(sp.name=N'#test___________________________________________________________________________________________________________________00003EE1' and SCHEMA_NAME(sp.schema_id)=N'dbo')
SELECT
NULL AS [Text],
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(sp.name=N'#test___________________________________________________________________________________________________________________00003EE1' and SCHEMA_NAME(sp.schema_id)=N'dbo')
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
...
...
string connectionString = [some connection string];<br/>
ServerConnection sc = new ServerConnection(connectionString);
Server s = new Server(connection);
Database db = new Database(s, [database name]);
StoredProcedure sp = new StoredProcedure(db, [stored procedure name]);<br/>
StringCollection statements = sp.Script;