Advertisement
Guest User

How do I programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui

a guest
Feb 26th, 2012
57
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.45 KB | None | 0 0
  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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement