Solved

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

Posted on 2009-02-13
819 Views
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?
0
Question by:arvapal

LVL 25

Expert Comment

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
0

LVL 25

Expert Comment

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..
0

LVL 51

Expert Comment

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
0

LVL 51

Expert Comment

Sorry about that, thought I saw a comment about script doing backup...
0

LVL 51

Expert Comment

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'

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")

GO

0

Author Comment

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.

0

LVL 51

Expert Comment

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.
0

Author Comment

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.

0

LVL 51

Expert Comment

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 ?

0

LVL 38

Expert Comment

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
0

LVL 51

Expert Comment

I take it you did not like the approach in  ID:23678131
0

LVL 57

Expert Comment

Ok.. Just create a procedure given here:

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.
0

LVL 67

Expert Comment

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'

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

go

0

LVL 51

Expert Comment

@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.
0

LVL 51

Expert Comment

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
0

LVL 38

Expert Comment

Mark and Qlemo,

Have you looked at the publishing wizard? It is just sweet. Makes it really easy without having to code it yourself.
0

LVL 51

Expert Comment

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).
0

LVL 38

Expert Comment

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.

0

LVL 42

Expert Comment

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
0

LVL 42

Expert Comment

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
0

LVL 42

Expert Comment

0

LVL 51

Expert Comment

@eugeneZ - whats wrong with the scripts here ?
0

LVL 38

Expert Comment

I'm thinking PAQ-Refund. A bunch of valuable info, but no real standout answer.
0

LVL 57

Expert Comment

Many scripts were provided here..
arvapal can try it out and can list how many works out so that it can be closed accordingly.
0

Accepted Solution

Question PAQ'd, 500 points not refunded, and stored in the solution database.
0

## Featured Post

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.