CODEWITHSUNDEEP
sqlsql-server-2005stored-procedures
Jony Lalwani
Jony Lalwani

Reputation: 2359

Generating the stored procedures from command line

I want to generate Scripts i.e. Stored procedures and user defined functions from SQL server 2005. I have done it through SQL management studio but i want to to do it through Command line. Is there is any way to do it through command line arguments like sqlcmd.exe or any other scripting.

Upvotes: 1

Views: 5328

Answers (3)

Tara Raj
Tara Raj

Reputation: 391

Microsoft released a new tool a few weeks ago called mssql-scripter that's the command line version of the "Generate Scripts" wizard in SSMS. It's a Python-based, open source command line tool and you can find the official announcement here. Essentially, the scripter allows you to generate a T-SQL script for your database/database object as a .sql file. You can generate the file and then execute it. This might be a nice solution for you to generate the schema and/or of your db objects such as stored procs. Here's a quick usage example to get you started:

$ pip install mssql-scripter
# script the database schema and data piped to a file.
$ mssql-scripter -S localhost -d AdventureWorks -U sa --include-objects StoredProcs --schema-and-data > ./myFile.sql

More usage examples are on our GitHub page here: https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md

Upvotes: 0

Taylor Gerring
Taylor Gerring

Reputation: 1835

If you prefer something with a little more flexibility, check out the sys.sql_modules system view, as it gives you a bit extra information about the object and has the nice ability of being able to join to other system tables.

The rest of MauMen's answer is the right direction though: Use SQLCMD to generate the output to a file with the "-o OutputFile.sql".

For example, create a proc:

create proc dbo.pr_procDefTest
as
print 'This is a proc definition'
go

Show the definition:

select m.definition
from sys.procedures p
inner join sys.sql_modules m on p.object_id = m.object_id
where p.name = 'pr_procDefTest'

Output with SQLCMD:

sqlcmd -E -S .\sqlexpress -d test -o OutputFile.sql -Q "set nocount on; select m.definition from sys.procedures p inner join sys.sql_modules m on p.object_id = m.object_id where p.name = 'pr_procDefTest'" -h -1 -w 65535

There are a variety of paramaters passed in, which you can look up on the sqlcmd Utility MSDN page. The important thing to note is the use of "set nocount on;" in the beginning of the script to prevent the "(1 rows affected)" footer.

Upvotes: 0

MauMen
MauMen

Reputation: 1369

use sp_helptext with a query from SQLCMD http://msdn.microsoft.com/en-us/library/ms176112.aspx

Upvotes: 1

Related Questions