SQL SERVER – 2005 – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

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

SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects databasecopy1

Step 2 : Welcome Screen

SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects databasecopy2

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)

SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects databasecopy3

Step 4 : Select database options

SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects databasecopy4

Step 5 : Select output option

SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects databasecopy5

Step 6 : Review Summary

SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects databasecopy6

Step 7 : Observe script generation process

SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects databasecopy7

Step 8 : Database object script generation completed in new query window

SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects databasecopy8

Reference : Pinal Dave (https://blog.sqlauthority.com), All images are protected by copyright owner SQLAuthority.com

Solarwinds
, , , , , , , , ,
Previous Post
SQLAuthority News – Principles of Simplicity
Next Post
SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code

Related Posts

673 Comments. Leave new

  • Tommy Reynolds
    June 8, 2014 9:40 pm

    Did you get an answer to your question here?

    Reply
    • hello pinal,

      i want to create script with data in sql server 2005 express as u shown. but i m not getting data.

      Reply
  • Msg 15401, Level 16, State 1, Line 2
    Windows NT user or group ‘ALWAR-BAGH-PC\Guest’ not found. Check the name again.
    15401, Level 16, State 1, Line 2
    Windows NT user or group ‘ARYANIWAS\Guest’ not found. Check the name again.
    Msg 15023, Level 16, State 1, Line 2
    User, group, or role ‘BUILTIN\Administrators’ already exists in the current database.
    Msg 15023, Level 16, State 1, Line 2
    User, group, or role ‘BUILTIN\Users’ already exists in the current database.
    Msg 15401, Level 16, State 1, Line 2
    Windows NT user or group ‘EBOX5\Dinesh’ not found. Check the name again.
    Msg 15401, Level 16, State 1, Line 2
    Windows NT user or group ‘EBOX5\Guest’ not found. Check the name again.
    Msg 15401, Level 16, State 1, Line 2
    Windows NT user or group ‘EBOX5\SQLServer2005MSFTEUser$EBOX5$SQLEXPRESS’ not found. Check the name again.

    these errors are happens pls tell me the solution

    Reply
  • i got one requirement Iike I want collect all dependencies like tables,functions, storedprocedures, views,triggers, constrains and so on by using PIVOT table can any one give the same SQL script.

    Reply
  • Hi, Is it possible to backup original SP name as usp_updBuy to usp_updBuy _YYYYMMDD in same database with script?

    Reply
  • how can db schema including Partition in SQL 2005

    Reply
  • hii Pinal,

    I want scheduled database backup in a drive.I am using SQL server 2005 . please send me solution ..

    i have addscheduletask but after that what i have to to i dont know please send me the solution immediately.

    Thanks in advance!

    Reply
  • How can script out particular schema dependent objects without selecting one by one db objects in Generate script wizard.?

    Reply
  • hello pinal,

    how are you?

    I have a question … i have three database and having a single table in each have the same name like table name is “customer amount”. which contains date and amount. and i want that whenever i choose the date from calender the sum(amount) from all the three db come in a grid in different column.
    how do i do so with database query.

    thank you in advance!

    Reply
  • Gabriel Vale
    May 26, 2015 10:10 pm

    Pinal, awesome tutorial.

    Could you send to me how to automate this process?

    I need export data of 300 databases

    Reply
  • Madhuparna Das
    July 1, 2015 2:11 pm

    i want to copy all tables,preocedures,packages,functions etc from a database user(Oracle10g) to a different location so that i can restore the database structure in my home m/c and work on the database

    Reply
  • But the indexes were missing. How to get them too?

    Reply
  • Hi Pinal,

    It was really an awesome tutorial and more helpful.

    I also wanted the scripts to be generated automatically. So could you please send me an copy of how to automate this process?

    Thanks in advance,
    Girish

    Reply
  • how to restore sql server 2008 R2 64 bit database on sql server 2005 32 bit database ?
    Is it possible?

    Reply
  • How do you automate this process?

    Reply
  • how can we get this script through t-sql[without uI]

    Reply

Leave a Reply

Menu