Genarate script in Sql server

Question

Genarate script in Sql server

kysphaneendra

SSC Veteran

Points: 232
More actions
April 29, 2015 at 2:30 am

#300212

Hi,
If we generate script from GUI in Sql server we are able to get Stored Procedures as they are (including structure).
But when we consider using "sys.sql_modules" we get all the stored procedure in a single line and if there is any singleline comments in the script all the following code is being commented.So we need to rearrange the script.
In this case we can not take script to move to production environment directly.
Can any one suggest some other way to generate script othere than GUI. so that We can get the scripts fast.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

The output appears to be in a single line in the SSMS results grid, but newline chars are actually preserved. If you copy/paste the object definition from the grid to a command window, you'll see the newlines.

-- Gianluca Sartori

kysphaneendra SSC Veteran Points: 232 More actions · Answer 2

Thank you for responding.

I did that and the problem still exists.

Multiline comments are not causing any problem.

Single line comments are making all the following code to comments.

consider.

<code>

--comments

<CODE>

Result in grid when copied to work area...

<code> --comments <CODE>

<code> is OK

But <CODE> is turning to Comments..

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Use the generate scripts task. Right click the database, tasks, generate scripts. Select all objects that apply, then generate the script file.

-- Gianluca Sartori

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

The generate scripts in SSMS works fine for me and handles comments properly.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

kysphaneendra SSC Veteran Points: 232 More actions · Answer 5

Currently I am also using GUI.

I am searching for any other way to get the scripts that are executable.

Consider taking scripts for 100 or 150 Stored procedures.

It Takes time to generate scripts.

I want to solve this case and searching on Internet.

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Two reads for you:

http://www.mssqltips.com/sqlservertip/1842/generating-sql-scripts-using-windows-powershell/

http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/04/use-powershell-to-script-sql-database-objects.aspx

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 7

The Powershell solution is the way to go. It's using SMO calls in the same way as the GUI. That will generate the code the way you're expecting it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning