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
Pinal,
My Manager has asked me to write a job in TSQL to BACKUP SCHEMA only NO DATA. I became aware of the GENERATE SCRIPT option through this blog. I thank you for showing this and for the rest of you thank you for the discussion. I was able to get a jist of the good and bad of this GUI script. Now, to my question. Is there any way to do this via a TSQL or is the GUI the only choice. I do not want to sound ignorant when i recommend the GUI option.
Please help
Thank you
Yousuf
Have you ever used the red-gate dot com software products? Just google it and take a look. I have used SQL compare in the past. Great thing!
Great article! I couldn’t have migrated my database without your help.
Thank you. I am learning sql server 2005. This helped me copy the views from one database to another database.
Sir
Hello How r u
I Have Query Pls Help Me
I am Using SQL Server 2005
i make Secure My Database.
So Make Certificate for the Database
but how to use it how it Make Protected to External Use
of Database.
Dinesh Sharma
(Matrix Solution)
Hi
i want to ask that while am running my website first time than i want that the data base create itself at a specified location as the wensite execute first time.
thanks
it’s useful i search about it
Hi, There is also a database copy wizard in SQL Server 2005. Just wanted to know if the results of both (The generated script & Database Copy wizard) will be different?
Hi,Can u tell me how to create a transactional database using MS Sql server
Hasini,
Transactional DB means..? Can u say more detail…
Hi Pinal,
I want to copy table with its contents from one database to another database within the same SQL server 2000. May I know how to do that?
Hi,if anybody requires how to generate script of database object using vb.net then
In this example i have used SQL cmd Utility and BCP
Hi,if anybody requires how to generate script of database object using vb.net then
contact at vikiahireengg on gmail
In this example i have used SQL cmd Utility and BCP
keep it up
Sir,
I am working with .net, for one Save method i want to gengrate script ,But when i save 1 value and again try to save another it will not work.
Plz help.
Thank You,
Dhananjay.
Hi sir,
i m facing problem in creating database in SQL SERVER 2005.so plz it my request to send a step by step process for creating database and corresponding Tables under a database.
Hello Pinal!
first of all thanks for another great post! :)
i havent had the time to read all the comments on this one, but two questions that i see frequently asked on these comments (and also on my daily job) are: “how do i generate a create script for a given table or SP or view or trigger?” and “how do i copy all the tuples from a table on a given DB to the same table on other DB?”
i suppose you have answered these questions over and over again, so i’ll leave here my aproach so further comments here wont give you much trouble answering :P
first of all there are two very useful sys procs that are sp_helptext and sp_help. the first of them is very usefull to know the code for procedures or views or other “text objects”. with sp_help you can get all the info about a table.
to generate the create script, just need to right click over the table name on SQL Server Management Studio’s Object Explorer and click “script table as…” and choose if you need the code for creating or inserting, or whatever. This is when people ask: “but i thought you were talking about helpful scripts, other then using Management Studio’s feature…” true. Just open SQL Profiler and see the query that the Management studio itself runs for that given table, and you’ll understand the few changes you’ll need to do for changing the table name and/or schema ;)
to answer the second question, just need to google the following string: “sp_generate_insert_script”.
it is a very usefull script to get the insert into table(col,col2) values (val,val2) statement u need to migrate the contents of the table elsewhere :)
WARNING: it wont work for those imported temp tables that the name starts with $.
hope i could be helpful for someone!
tell me what you think about what i just said, and what are the pros and cons that you find here.
Kind Regards and apologies for my poor english,
Afonso from Lisbon, Portugal :)
Pinal Dave,
Thank you so much for this post and all of your other posts too.
You are helping me a lot.
Thanks,
Palani
I am trying to change the shema of objects in database.
How can I do?
I have a database like TestDB the table name is START.CUSTOMER where START is Schema and I want to change it agian in DBO. How can I do?
With Regards
@Ravi.
You can do it like this,
Alter Schema New_Schema_Name transfer Old_Schema_Owner.ObjectName
In your example you can do like this.
Use TestDB
ALTER SCHEMA DBO TRANSFER START.CUSTOMER
This will transfer object customer from START schema to DBO schema.
Hope this helps.
IM.