We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

how to generate database scripts through command prompt in sql server 2005

Medium Priority
928 Views
Last Modified: 2012-05-06
Hi,

Can we generate the database scripts (Not all at a time, only few stored procedures/UDFs/Views) using command prompt in sql server 2005 ?
Or any work around for performing the above task?
Comment
Watch Question

Commented:
You can use SMO (SQL Server Management Object ) library to create scripts, a sample with code which can be adapted is available in the following link -

http://forloveofsoftware.blogspot.com/2008/11/scripting-sql-server-database-objects.html

Commented:
The easiest way is to use the Database Properties (right click on database in SMS) and Task -> Generate SQL Scripts which will give you a lot of options to customise the objects required and script options..
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Here is an excelent example doing exactly what you want. It says it is for Express, but will work on any edition. Also includes a VBS script to clean up "old" versions.

http://www.mssqltips.com/tip.asp?tip=1486
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Sorry about that, thought I saw a comment about script doing backup...
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
So, yes, all in T-SQL cerate a script file and use osql or sqlcmd to execute the script file :



DECLARE @cmd varchar(255)
DECLARE @object int
DECLARE @result int
 
SET NOCOUNT ON
EXEC @result = sp_OACreate 'SQLDMO.SQLServer', @object OUT
 
-- EXEC @result = sp_OASetProperty @object, 'LoginSecure', TRUE    -- used for integrated windows security
 
EXEC @result = sp_OASetProperty @object, 'Login', 'sa'
EXEC @result = sp_OASetProperty @object, 'password', 'sapassword'
 
SET @cmd = 'Connect(myservername)'
EXEC @result = sp_OAMethod @object,@cmd
 
SET @cmd = 'Databases("mydbname").Views("vw_myviewname").script(5,"c:\ee\vw_myviewname.sql")'
EXEC @result = sp_OAMethod @object, @cmd
 
-- Views, Tables, StoredProcedures, Functions pretty much use the same format as above e.g. databases("dbname").Tables("tablename")
-- indexes, triggers, checks, keys you need databases("dbname").Tables("tablename").indexes("idxname")
 
EXEC @result = sp_OADestroy @object
GO

Open in new window

Author

Commented:
Hi Mark,

Thank you very much for the quick solution.

The above sql script is working fine. My requirement is to generate the required tables/SPs/Views/UDFs in a single .sql file.

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Thought it was for "Not all at a time, only few stored procedures/UDFs/Views"

Do you have access to SSMS ? You can use the generate scripts wizard and select which objects you want.

Right click on the database, go into Tasks, then Generate scripts. The scripting wizard appears. If you get the banner page, press next.
Then select a database (the one you right clicked on should be selected), press next
Then in "Choose Script Options" scroll down and select things like Indexes etc... press next
Then select the types of objects, press next
Then for each of those objects (seperate screen at a time) select the individual items you want scripted
Repeat for next object type
then fill in export options and finish.

Using the "manual" / "code" method, how are you going to provide a list of items to script ? Can get a single file by doing a copy this1+this2+this3 that123 after the script has run and you are back in command line mode.

Author

Commented:
Hi Mark,

I am familiar with the Generate scripts using SSMS, but i need to automate that task through command prompt and task scheduler.

About the list of requied items, i will be running the below sql command.

select distinct [schema], object from Admin.DatabaseLog where PostTime between convert(datetime, '18/02/2009', 103) and convert(datetime, '19/02/2009 23:59:59', 103)

Once after executing the above script, i will get all the modified objects during the given time.

Now my requirement is to generate one sql script file which contains the above modified objects only.


Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Any indication as to object type ? will they be unique names ?  any indication of dependancies ? or just straight scripts concatenated together as they occure from the query...

Basic approach will be to :

1) create a script file
1.a) probably create a cursor to get each object
1.b) use script similar to above to generate scripts
2) create a batch file
2.a) tidy up a "work area"
2.b) run script (osql or sqlcmd)
2.c) copy concatenate individual scripts from "work area" to destination with datetime as part of name
2.d) remove work area files

Sound OK ?

Most Valuable Expert 2014

Commented:
Try this: SQL Server Database Publishing Wizard. It's designed for transferring whatever you need between your local database and say a hosted website that you can't directly insert data. It should be what you need.


Microsoft SQL Server Database Publishing Wizard 1.1
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
I take it you did not like the approach in  ID:23678131
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Ok.. Just create a procedure given here:

http://vyaskn.tripod.com/code/generate_inserts.txt

Once you have created it, then you can execute that sql from command prompt using sqlcmd and then create the Database Scripts. Hope this helps you out.
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
The following should create a single file with all changed tables with their dependent objects (indexes, PK, FK, default, ...). If you have to differenciate for Views aso., you need to check the type of the object and execute e.g. Database(...).Views(...) accordingly.

For the strange integer constant of script method, see BOL, Index SQLDMO_SCRIPT_TYPE.





DECLARE @cmd varchar(255)
DECLARE @dmo int
DECLARE @result int
DECLARE @object varchar(100);
DECLARE @schema varchar( 25);
 
SET NOCOUNT ON
EXEC @result = sp_OACreate 'SQLDMO.SQLServer', @dmo OUT
 
-- EXEC @result = sp_OASetProperty @object, 'LoginSecure', TRUE    -- used for integrated windows security
 
EXEC @result = sp_OASetProperty @dmo, 'Login', 'sa'
EXEC @result = sp_OASetProperty @dmo, 'password', 'sapassword'
 
SET @cmd = 'Connect(myservername)'
EXEC @result = sp_OAMethod @dmo,@cmd
 
declare csr cursor for select distinct [schema], object from Admin.DatabaseLog where PostTime between convert(datetime, '18/02/2009', 103) and convert(datetime, '19/02/2009 23:59:59', 103) for read only;
open csr
fetch csr into @schema, @object
while @@fetch_status = 0
begin
   set @cmd = 'Databases("mydbname").Tables("'+@object+'","'+@schema+'").script(256+73736+469762048+16777216+33554432+67108864,"c:\ee\new_objects.sql")'
   EXEC @result = sp_OAMethod @object, @cmd
   fetch csr into @schema, @object
end
deallocate csr
 
EXEC @result = sp_OADestroy @object
go

Open in new window

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
@qlemo, nice work, I was still wanting to hear back what was wrong with the basic approach. Yes, you can use script options to append, but wasn't too sure if it was going to pan out that way - you show it can.

This magic number are part of the script object : http://msdn.microsoft.com/en-us/library/ms142610.aspx (there is also a 2005 link) to understand what the options are.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Ummmm, some of those combo's might not work in conjunction with some of the object types - fine for tables, but is there anything else in the list of objects ?

Would be inclined to have a case on the type of object, and use the 256 which says append, and forget the rest... or use 532676608 for all DRI options (linked as "or")

But it really does depend on what we get from ADMIN.Databaselog
Most Valuable Expert 2014

Commented:
Mark and Qlemo,

Have you looked at the publishing wizard? It is just sweet. Makes it really easy without having to code it yourself.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Yep, I have seen it before - actually used it. It is very good, no questions.
The only reason why I went to code was the selective nature of the objects to be altered - thought it would be easier (go figure).
Most Valuable Expert 2014

Commented:
From the nature of the question -- "Not all at a time, only few stored..." It sounds like he's trying to do some sort of update to distribute with a new version of an application.

EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
if only
stored procedures/UDFs/Views
you can use

select o.name,c.text from syscomments c inner join sysobjects o on c.id=o.id
where o.xtype in ('P','FN','V')
 
and via osql\sqlcmd just send the result into file from cmd (xp_cmdshell).etc
---
if more objects you need to use .NET SMO code
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
like this SMO based  cmd apps
How to Script Out SQL Server Objects
By ozkar garcia
 
http://www.codeproject.com/KB/vbscript/0g_SqlExtract.aspx
 
just in case
SQL Server objects in SourceSafe using SMO and Visual SourceSafe automation
By Levent Soyalp
http://www.codeproject.com/KB/database/DBScriptSMO.aspx 
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
@eugeneZ - whats wrong with the scripts here ?
Most Valuable Expert 2014

Commented:
I'm thinking PAQ-Refund. A bunch of valuable info, but no real standout answer.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Many scripts were provided here..
arvapal can try it out and can list how many works out so that it can be closed accordingly.
Commented:
Question PAQ'd, 500 points not refunded, and stored in the solution database.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.