Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Generate SQL Scripts from Database

By Absinthe,

Modify the script to have your server in sServer and your database in sDatabase. By default the output will be in a directory on the C:\<databasename> . This should also be changed but is left as an exercise for the reader.

I attempted to use DMO to exactly reproduce the output that I get from Microsoft SQL Server Enterprise manager when I choose a database and then "Generate SQL Scripts" and choose the following settings:

General:

Script All Objects

Formatting:

Generate the CREATE command for each object

Generate the DROP command for each object

Generate scripts for all dependent objects

Include extended properties

Options:

Script database

Script database users and database roles

Script SQL Server Logins (Windows and SQL Server logins)

Script object-level permissions

Script indexes

Script full-text indexes

Script PRIMARY keys, FOREIGN keys, defaults, and check constraints

Windows text (ANSI)

Create one file per object

However, I do not want it to be exactly like that, as some things I do not want to delete first, but rather check if they exist first then only add them if they do. Specifically: Defaults,Users, Roles, and Logins. It turns out that when I tried to do this, generating the scripts for views, would for some reason trigger making several other objects at the same time regardless of your settings. So it required being made differently all by itself. I am posting this code here, because basically I have yet to see it all in one place. So in the spirit of the season, and just because I am an all around nice guy. Here is the code in VBScript. It is ugly and not commented at all... but I think you can get the point...Replace the values in sServer and sDatabase with stuff that works for you.

Total article views: 3818 | Views in the last 30 days: 8
 
Related Articles
FORUM

Generate script for all objects in a database

Generate script for all objects in a database

FORUM

Generate scripts for all databases

Generate scripts for all databases

FORUM

Generating Scripts for Database Objects

Creating Seperate Scripts Per Object

FORUM

Generate Scripts - why isn't db_datareader priv granted??

Right-click database, Generate Scripts, All Objects, yes to Object Level permissions..what else?

FORUM

how get generate script for database user with their database and server roles

how get generate script for database user with their database and server roles

Tags
dmo    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones