Introduction

If you have to generate create script for bulk number of stored procedures, what will you do?? Use Sp_Helptext sql server inbuilt stored proc. But it consumes time.This Article I how to generate SQL Server Stored Procedure and Create Table defination Script. I have made an Windows Application for Generating SQL Server Stored Procedure and Create Table Create Script with click of single button only.

Using the code

Open up New Windows Forms Application Project in Visual Studio 2008. Name it GenerateScripts

On Windows form drag GroupBox Name it DBObjects. Inside Group box drag two radio buttons name them Stored Procedure and Table respectively.

Also drag 2 labels, 3 textboxes and 3 buttons.

Name Labels as Connection String and Path. Buttons as GenerateScripts,Clear and Close

The above method uses SP_ScriptTable for generating Create Table Defination script. Run the following Sql Server Script in your SQL Server database. This Script will run only in SQL Server 2005 and above.

Explaination

I have used two classes FileStream class and Stream Class.The FileStream class is derived from Stream class. This class is used for reading from and writing to files such as bytes, characters, strings, and other data-type values to a file. Other class I have used is StreamWriter. The StreamWriter class's Write and WriteLine members write to a file.In this above code, I have used FileStream class to create a sql file and StreamWrite to write text to the sql file.

In the above Code, you can see that two methods are defined named GenerateStoredProc and GenerateTableScript.In the First Method i have used SP_HELPTEXT Command that generates the stored Procedure scripts and in the second method i have called an stored Procedure that generates create table script and saves into the path which is given by the user.