Menu
Is there a simple process in SQL 2005 for spitting all of my stored procedures out to individual .sql files. I'd like to move them into VSS, but am not too excited by the prospect of clicking on each one to get the source, dumping it into a text file and so on..
871
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
Export Stored Procedure in SQL ServerIn the Object Explorer, right-click on your database. Select Tasks from the context menu that appears. Select the Generate Scripts command.
Using SQL Server Management StudioExpand Stored Procedures, right-click the procedure and then select Script Stored Procedure as, and then select one of the following: Create To, Alter To, or Drop and Create To. Select New Query Editor Window. This will display the procedure definition.
According to the CTE documentation, Common Table Expression is a temporary result set or a table in which we can do CREATE, UPDATE, DELETE but only within that scope. That is, if we create the CTE in a Stored Procedure, we can't use it in another Stored Procedure.
In SQL Management Studio right click on the database, go to tasks -> Generate Scripts, walkthrough the wizard. One of the pages will let you script each object to its own file.
144
You can run this select:
select
O.name, M.definition
from
sys.objects as O
left join
sys.sql_modules as M
on O.object_id = M.object_id
where
type = 'P'
and you get the name and source code for stored procedures. Propably most easy way, how to put it in files is in some "classic" languge like c#, java, etc ...
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
