Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

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  |  views: 36  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. SELECT
  2. NULL AS [Text],
  3. ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
  4. FROM
  5. sys.all_objects AS sp
  6. LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
  7. LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
  8. WHERE
  9. (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')
  10.        
  11. SELECT
  12. NULL AS [Text],
  13. ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
  14. FROM
  15. sys.all_objects AS sp
  16. LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
  17. LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
  18. WHERE
  19. (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')
  20.        
  21. using Microsoft.SqlServer.Management.Smo;
  22. using Microsoft.SqlServer.Management.Common;
  23. using System.Data.SqlClient;
  24. ...
  25. ...
  26.     string connectionString = [some connection string];<br/>
  27.     ServerConnection sc = new ServerConnection(connectionString);
  28.     Server s = new Server(connection);
  29.     Database db = new Database(s, [database name]);
  30.     StoredProcedure sp = new StoredProcedure(db, [stored procedure name]);<br/>
  31.     StringCollection statements = sp.Script;