Click here to monitor SSC
SQLServerCentral is supported by Redgate
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:


Script All Objects


Generate the CREATE command for each object

Generate the DROP command for each object

Generate scripts for all dependent objects

Include extended properties


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: 3886 | Views in the last 30 days: 6
Related Articles

Generate script for all objects in a database

Generate script for all objects in a database


Generate scripts for all databases

Generate scripts for all databases


Generating Scripts for Database Objects

Creating Seperate Scripts Per Object


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

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


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

sql server 7