code_junkie

MSSQL: How do you script Stored Procedure creation with code

Nov 14th, 2011
76
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.98 KB | None | 0 0
  1. SELECT
  2. t1.Routine_Definition
  3. FROM
  4. [server1].MyDatabase.INFORMATION_SCHEMA.Routines t1
  5. LEFT JOIN
  6. [server2].MyDatabase.INFORMATION_SCHEMA.Routines t2 ON t1.Routine_Name = t2.Routine_Name
  7. WHERE
  8. t2.Routine_Name is null
  9.  
  10. --Some comment
  11. SELECT Column
  12. FROM Somewhere
  13.  
  14. declare @sql varchar(8000) -- varchar(max) in SQL 2005+
  15.  
  16. SELECT
  17. @sql = t1.Routine_Definition
  18. FROM
  19. INFORMATION_SCHEMA.Routines t1
  20.  
  21. print @sql
  22.  
  23. DECLARE MY_CURSOR Cursor
  24. FOR
  25.  
  26. SELECT
  27. t1.Routine_Definition
  28. FROM
  29. [server1].MyDatabase.INFORMATION_SCHEMA.Routines t1
  30. LEFT JOIN
  31. [server2].MyDatabase.INFORMATION_SCHEMA.Routines t2 ON t1.Routine_Name = t2.Routine_Name
  32. WHERE
  33. t2.Routine_Name is null AND
  34. LEN(t1.Routine_Definition) < 4000
  35.  
  36.  
  37.  
  38. Open My_Cursor
  39. DECLARE @sql VARCHAR(MAX)
  40.  
  41. FETCH NEXT FROM MY_Cursor INTO @sql
  42. While (@@FETCH_STATUS <> -1)
  43. BEGIN
  44. IF (@@FETCH_STATUS <> -2)
  45.  
  46. Print @sql
  47.  
  48. FETCH NEXT FROM MY_CURSOR INTO @sql
  49. END
  50. CLOSE MY_CURSOR
  51. DEALLOCATE MY_CURSOR
  52. GO
Add Comment
Please, Sign In to add comment