I am using "Microsoft SQL Server Management Studio Express 9.00.2047.00" as the interface for SQL Server 2005 Developer Edition. I wanted to create a separate SQL file for each stored procedure in my database. The generated script should have the format like this.
I am very new to this area. Please help me
(comments are locked)
|
It's very easy to do this with SMO, if you can use .NET:
This example connects to a server called MyServer, and saves the script for all stored procedures in the database called MyDatabase into the folder c:\procs. It will need two modifications, potentially, which I will leave up to you: 1) You will need to ensure that the file names used are valid, if you have any stored procedures with invalid file name characters in them 2) You will need to modify the code to emit the 'if sp exists drop sp' part. SMO can do the 'if sp does not exist create sp' version, but not the 'if sp exists drop sp' AFAIK.
(comments are locked)
|
SQL Compare will do it for you quite nicely. It's what we actually use to version control the database schemata that are used on SSC. We dump the whole database schema to a folder hierarchy and then put that in source control, along with the C# code.
(comments are locked)
|
Any particular reason to not just use the Generate Scripts feature in Management Studio? Right-click the database, Tasks, Generate Scripts, pick the options you want, done.
(comments are locked)
|
You can do this a number of ways. Basically, the process is built in to SMO/DMO, which does it very well, and allows you several alternatives, depending on your method of source control. Nowadays, the best way of doing it involves Powershell or another scripting language. I find it most convenient to use a stored procedure to do it, as I can execute it from a query window or put it on the SQL Server Agent, and twiddle the knobs to get the exact scripting I want without leaving the query window. This involves using the rather antiquated DMO. Deprecation of DMO was announced in SQL Server 2005. In SQL Server 2008,is shipped but not automatically installed. There are two places to get DMO with SQL Server 2008, if you have the intallation DVD, you'll find it here ..\x86\Setup\x86\SQLServer2005_BC.msi, or you can download it from here SQL Server 2008 Feature Pack @ the Microsoft Download Center but it is best to google the "SQL Server Feature Pack" for the latest version. The script is a bit long to paste into this answer but it is described in this article here Automating Common SQL Server Tasks using DMO and the code is here
(comments are locked)
|
Easiest way I know of that doesn't quite meet your layout criteria but acheives the same purpose is to simply highlight all the procedures in the Object Explorer Details in SSMS, and then right click and choose Script as Drop and Create... Please ignore this as I didn't read the full spec... Ho-hum. (hangs head in developer shame).
(comments are locked)
|
1 2 next page »