Script entire database SQL-Server

Question

Is there a way I can get a scripting of all tables, procs, and other objects from a database? I know there's an option to script the database but it only gave me some sort of top level script, certainly not a script to create all tables, procs, udfs, .etc.

Plan

He tagged it SQL Server 2008, so I assume that's what he's using. – Kerguelen

Isn't this suppose to go to serverfault.com ? – Yama

Answer 1

From Management Studio Right-click on your database. Tasks -> Generate Scripts.

That should do it.

Stanzel

Another option is to use SQL SMO and script it out programatically (i.e. if regular scripting is required) – Thier

Is there a way to set up a script to do this so the settings are always the same no matter who runs it? I foresee people on our dev team overwriting this file with different settings each time... – Aec

@Joe, I would head down the route that RobS is talking about. You should be able to do that with PowerShell. Otherwise start looking at RedGate tools, or Visual Studio Team Systems with Database Developer. – Stanzel

By default it doesn't script the data. Under Table/View Options select "Script Data -> True". Another useful option is "Script Drop -> True" – Ormandy

The generated script won't contain any information about the collation of the columns, unless you have chosen the last option in the menu Extra > Options > Script generation. In German, the option is "Sortierung einschließen". Even of most of these options are included in the wizard generating the script, this one is not (SQL Server 2008). You really have to select the option before calling the wizard. – Terrill

Answer 2

I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data.

To generate scripts run:

schemazen.exe script --server localhost --database db --scriptDir c:\somedir

Then to recreate the database from scripts run:

schemazen.exe create --server localhost --database db --scriptDir c:\somedir

Kellyekellyn

Hi, I want to get the SQL QUERY from my local database created in visual studio, how can I do that with this tool? I have to use the .sdf file direction or how? thanks. – Winters

Its a compact SQL DataBase file. – Winters

@KarloA.López SQL Server Compact isn't currently supported. It shouldn't be too difficult to add support though so if you submit a feature request it's probably get done pretty quick. You can create a feature request here github.com/sethreno/schemazen/issues/new – Kellyekellyn

HOLY CRAP THIS IS FAST AND AWESOME. – Lactometer

@hobs I'm glad you found it useful. :D – Kellyekellyn

@SethReno corrupt sys indexes on my db = SMO cannot script anything out, your script can, so basically saved me a ton of time manually doing crap. BTW, impressively fast reverse engineering of everything, like 1000x faster than SSMS and it seems faster than SMO by itself (not the least because of startup time). – Lactometer

Answer 3

I wrote a utility for this task, SMOscript.

Script generation is performed by the SMO library, and supports the new object types in SQL 2005 and 2008.

Curling

Hmm I installed your smo app on my Vista 64-bit. Install successful but I do not see an item in my program directory or All Programs list – Plan

Answer 4

We ended up using a combination of SSMS script generation to extract schema and data, and then use our own database tool which allows keyword parsing and token based replacement in scripts. It also ensures scripts are only applied once.

Why?

• We need to support installations on SQL Server 2000, 2005 and 2008, and there are changes to data types between versions, e.g. 2005+ have nvarchar(max), whereas 2000 only supports ntext. So our scripts use a token and based upon the db choice replaces with the correct type.
• Execution of some scripts requires a wait period after execution, e.g. We found if you didn't wait a few seconds after creating a new databases via a script, the SQL Server might fail sometimes (because it hasn't had time to create the db files) when it went on to create tables, etc.
• We wanted to maintain a history of what scripts were executed and when.
• We wanted to allow our Wix MSI installer to specify connection string and credentials, and needed some way to pass these into the scripts, so once again, using tokens and some conditional logic.

Example script (edited for brevity)

-- Sleep: 5 
-- Sleep after creating database to allow file system to create db files
CREATE DATABASE [$Database$]
GO

EXEC sp_dbcmptlevel [$Database$], $CompatabilityLevel$
GO

USE [$Database$]
GO

IF '1'!='$IntegratedSecurity$'
BEGIN
    CREATE LOGIN [$Login$] WITH PASSWORD=N'$Password$', DEFAULT_DATABASE=[$Database$]
    CREATE USER [$User$] FOR LOGIN [$Login$]
    EXEC sp_addrolemember N'db_owner', N'$User$'
END
GO

Illona

Answer 5

Just looking at the table data, to output all of the table data content in Management Studio 2012 and 2014, it is a bit hidden but I found the option after some looking:

1. Right click the db
2. Select 'Tasks' > 'Generate Scripts...'
3. On 'Set Scripting Options', click 'Advanced'
4. Under 'General', set 'Types of data to script' to true (it is at the bottom of the 'General' group)

Snot

Answer 6

I recommend looking at RedGate SQL packager. It is not free, but has been useful enough to be worth the price.

Cochard

Answer 7

If you need to do it programmatically, you can use the SQL DMO library (OLE) against SQL Server 2000, but more likely you may wish to use the SQL SMO library (native .NET libraries) against SQL Server 2005 and later.

Both these libraries are integral to the SQL Server administrative tools install.

This is in the case that generating the full database script from SQL Server Management Studio is insufficient.

Surfactant