Update: This article is re-written with SQL Server 2008 R2 instance over here: SQL SERVER – 2008 – 2008 R2 – Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
Following quick tutorial demonstrates how to create T-SQL script to copy complete database schema and all of its objects such as Stored Procedure, Functions, Triggers, Tables, Views, Constraints etc. You can review your schema, backup for reference or use it to compare with previous backup.
Step 1 : Start
Step 2 : Welcome Screen
Step 3 : Select One or Multiple Database
If Script all objects in the selected database checkbox is not selected it will give options to selected individual objects on respective screen. (e.g. Stored Procedure, Triggers and all other object will have their own screen where they can be selected)
Step 4 : Select database options
Step 5 : Select output option
Step 6 : Review Summary
Step 7 : Observe script generation process
Step 8 : Database object script generation completed in new query window
Reference : Pinal Dave (https://blog.sqlauthority.com), All images are protected by copyright owner SQLAuthority.com
673 Comments. Leave new
Hi all
Thanks dear it facilities my work a lot
But can I ask about how I can execute this procedure from the vb.net
Thanks for you in advance
first time coming to this blog..this page is very good so i may visit more often now. thanks
This was VERY useful. I am no SQL guru, although I do know my way around RDBMS…
My company needed to put together a validation process for the changeover from Mode 80 to Mode 90. We elected to force compile of all the scripts and deal with the fallout.
The trouble was that no one knew how to do this…
Although a little editing was required of the final script, this was a Very necessary first step.
Thank you.
Thank you so much for the info. of taking the database schema.
Actually I wanted to take a entire database backup, but which came around more than 30 gigs.
So I have taken the schema backup by using the info. given here.
Wow. I wonder if Pinal knows that he’s still getting comments and questions so many months later.
First: Pinal, thanks for this – I figured we’d been given a crappy scripting app courtesy of the SSMS team. Nope, just not where I expected it. Many thanks.
And to everyone looking for a different way to script everything, I found something on sqlteam dot com called ScriptIO. The downside is that it’s 2005 only, but it’ll script out whatever you need.
A couple answers to earlier questions:
81: Redgate Data Compare & Redgate SQL Compare. If you just want to compare data on the cheap, do a BCP out with -c (character version) and use kdiff3 (yes, it exists for Windows) to compare files. But Red-gate’s tools are cheap and work pretty durn well.
66: one easy way is to do a backup/restore – when you restore to 2005 it’ll convert it. Another way is to detach, copy, and attach the database. But it will be unavailable while it’s detached – so restoring the backup should be easier.
53: There’s a couple different ways to do it. (But, as always – if it works for you, use it!) Another way is to do a BCP out/in. If they’re exactly the same table definition you can use -n (native), but otherwise use -c, and make sure of your field delimiter (and check the flag for Identity inserts). If doing character-based BCP imports, I frequently have to use | instead of [tab]. I also found a script that’ll create the insert statements from within SQL:
Though, honestly, most of the time I’ll just use BCP/Bulk Insert.
Hi
I found this website is very useful
Can u plz give me some tutorial of sql server 2005 which contains concepts like triggers, stored procedure, cursors etc
Have you read about them in SQL Server help file?
It has example codes too
Respected Sir ,
I have a problem in sql server 2005
At front hand a user can login .
A username and password is saved in HASHED FORMAT
how can i get the a userpassword in orignal form
Pls sir give me a solution i am very much thanks ful to you .
hey pinal i generate d script by d procedure told by u….but now whn m insert dat script in my database n hit the execute button….it start processing but after some time it shows me the error dat request time out…..my script dat generated is of 21246 lines…..
plsss help me out…
i have executed and disabled sa log in in sql server 2005 version how can i fix it tnx
YOU RULE!!! Don’t worry if some people did not appreciate your column–you are right that even though it’s a Wizard, I didn’t know how to use it!
Thank you!
Where can I send you the help file for notepad?
sir,
how can i create a database in sql 2005 and create a create a table plz tell me
for example your database name = gold
your table name = tran
create database gold
go
use gold
go
create table tran
(id int, name char(6))
go
Please how to check if a distant sql server is running (a ping?), what is the data source instance (exemple SQLEXPRESS), and if a database name exists? of course having the ip port user and pass
Hello Pinal Dave,
Thank you for your web SQLAuthority.com and postings. That’s helped me in great deal…
after following your instruciton of “SQLSer 2005 – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects”, the SP, table names, Functions, Triggers are created, but i still have a trouble to load all rows into the tables. I tried the SSIS, import/export, copy database that none of are working. Can you make a suggestion for me to get this work done?
Thank You!
Denise
hi there,
what i really want is to copy the entire database to another Sql Server, this server in question is accessible via internet
if you can help, thanks
The simple way is to take a backup of the database and restore it in another server
I had 3 server on 3 different places. 3 server got same database with own datas.
This 3 datas i want to put on 4th server which located on my place.
How can i do this..??
Hey wait a minute… it doesn’t generate the ‘insert’ statements for the date.
Any equivalent for that?
Pls email me if you have a solution
Generate INSERT statement option is available only from version 2008
Hello,
I’d love to know the best way to copy a database/table from SQL Server 2005 to SQL Server 2000.
I have SQL Server 2005, but I can’t seem to find a good way to copy all my tables’ properties over to SQL Server 2000 — identities, primary and foreign keys and such.
I tried creating a few “CREATE TABLE” scripts in the Query Editor Window, and then tried executing the same script in the SQL Server 2000 server, but all I get are syntax errors.
Would love to know if there’s an easy answer out there!
Thank you,
Susan
Hi Neha we are not having any inbuilt schema comparision tools we should depend on third party tools
visit
.
or search forredgate comparision tool
HI Pinal,
I just want to know how to get the table’s (one or more table) script using T-SQL Command.
Pls let me knw
hi ,
i want to know database name through script in sql server 2000/2005. is it possible? if yes,plz let me know.
thanks in advance
select db_name()