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
Sir,
How to i Migrate Windows Sql server Database to Linux Sqllite Database ????. Please Help Me
– Jayaprakash Rajendran
Hi,
I want to move records from one database table to another database table ( both table structures are same) . But i want here i want to move first 1000 records and then next 1000 records like that. i don’t have any key in my table.
Thanks in advance.
Thanks,
its realy good demostration…
Thanks…
How can i retrieve list of user defined roles assigned to a user ? I tried with sp_helpuser gives only one role at a time…even though user has mutliple roles… again this is for SQL Server 2000 db
thanks in advance for the help……
Pinal,
You mentioned in the few top replies that you sent e-mails to people showing how to generate similar scripts programmatically (in other words, script table creation and all related information: indexes, constraints, triggers) using code.
Would you please send me this by e-mail as well and it may be a great new blog post.
Thanks in advance.
Hi Pinal,
I think the information you provide is great, please keep up the work.
If possible could you please send me the script to automate this process
Thanks in Advance
Brett
i want comparation between two data bases table wise, i want only diffent table names and different columns only
like
tablename col1,col2
if (ds.Tables[0].Rows.Count > 0 && ds1.Tables[0].Rows.Count > 0)
{
try
{
IList missedFields = new List();
foreach (DataRow dr in ds.Tables[0].Rows)
{
bool stts = false;
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
if (dr[“Column_name”].ToString() == dr1[“Column_name”].ToString())
{
stts = true;
break;
}
}
if (stts == false)
{
missedFields.Add(“from US:” + dr[“Column_name”].ToString());
}
}
if (missedFields.Count == 0)
{
Response.Write(“Success”);
}
else
{
DataTable dt = new DataTable();
DataColumn dc = new DataColumn(“Missed Field Names:”);
dt.Columns.Add(dc);
foreach (string str in missedFields)
{
DataRow dr = dt.NewRow();
dr[0] = str;
dt.Rows.Add(dr);
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
i want un matched Table name with all Rows
Hi,
Can we have such type of sql commands by which we can generate create script of any table.
I am thinking about to making a procedure which can copy a table with its whole data.
Thanks
Rahul
Hello Rahul.
Use Database Publishing Wizard to generate script of tables with data.
Try searching for key word Database Publishing Wizard either in Google Search Engine or SQLAuthority search.
~ IM.
Hi Rahul,
In SQL Server 2008, you can script the table with data (an insert statement for each row) using SQL Server Scripting wizard. In this wizard, on Choose Script Options page set the Script Data option to True.
Regards,
Pinal Dave
could you tell me how to run script in vs2008 it has inbuilt db
i have script for creating database
How can i scrip a database with all tables and its datas?
i am using sql 2005 express edition.
can u help on this
Can you kindly tell me which commands syntax are changed in SQL 2008 compare with SQL 2000?
Thanks
Hi,
To cover this topic completely a whole book can be written. Here I mentioned few enhancement in SQL Server 2005 that are also applied to SQL Server 2008.
New Data types
Error handling with TRY-CATCH
ROW_NUMBER and other ranking functions
CTE
OUTPUT clause
INTERSECT and EXCEPT operands
PIVOT and UNPIVOT functions
CROSS APPPLY operator
For more details please check the following article:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/cc721270(v=sql.100)
Regards,
Pinal Dave
Hello,
I was wondering what would be the best approach to creating a “template” to create a database, tables and indexes based off of current databases I have already created. I have used current tools within management studio and combined the ‘create database template’ with a the script created with one of the current databases which contains the tables and indexes I would like to be able to reuse. I don’t need data. Any help would be appreciated.
Thanks for your time.
Jake
hi pinal
can u tell me how to run database script / restore database script from command line in sql server 2005
Hello Aatish,
Use the SQLCMD utility for this purpose. At first right the complete t-sql script to restore database in a notepad for example in c:RestoreScript.txt
Now go to command prompt and write a command as below:
>sqlcmd -S “yourServerName” -U “UserName” -P “password” -i c:RestoreScript.txt
If you can login with windows credential then username and password are not required.
Regards,
Pinal Dave
plz tell me how to restore database from command line.
Dear Pinal
I have a problem with a database in SQL 2008.
I can not generate the script of this database. I only tried with the option of generating script in SQL Server Management Studio.
I can generate script for all other databases except this.
I also tried this database by taking backup and restoring it in another machine. The result is same.
The scripting utility can see all the tables, functions and procedures etc. but it will show it is validating the settings, if we try to do a script.
Any idea how to take script for this stubborn database..?
Your valuable suggestion is requested in this regard….
With Love
Thomas
Hi,
Can anybody tell me how can I write a stored procedure to copy a database from one server to another server. This procedure is going to be accessed by relevant parameters from an application.
Hi ,
I am Fresher with more curious to learn new things.
I am going to Design one web-Site for showing Details that had been stored on database (on Mysql which is existing one) ,Will it be possible to copy all that data in SQL-2005 database as it is.
Also which tool/technique i prefer to copy that to my Sql database ,i want to design website in C#.net,Firstly it’s an it’s intranet application,if successful may ploy on Internet.
Another question is that can i convert flash file application in to text ?
hope you will be there for me
thanks for your important suggestions!!!