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 Pinal,
I am using the same way as you mentioned in SQL 2008, but instead of “Scripts all objects in the selected database” i selected only the Stored procedures of my database.
and when I executed the script on some other DB few of the Stored procedures are skipped in the script,
On investigation about the name of the stored procedure, Its noticed that the skipped sp’s are the one which I had used in many other stored procedure.
Please advice , how can I get the scripts of all the Sps.
Thanks
Manish Kaushik
i want to take script for database schema and also want to take some table with values
Hi,
Does anyone know how to get SQL Server 2005 Generate Scripts to output ONLY the indexes, constraints and triggers (i.e. not the CREATE TABLEs)??
I can do it in SQL Server 2000 Generate SQL Script by:
1st Tab – Selecting All Tables
2nd Tab – Unselect Generate DROP and CREATE commands
3rd Tab – Selecting the Table Scripting Options in the third tab.
Thanks,
Niko
Hi,
I know how to generate the script by above mentioned steps.
But instead of manual generating script, have to write new storedprocedure with argument of table name.
This has to return the script of selected table.
Help me if u know the answer.
Thanks……..
This was usefull! And it’s not like writing help for Notepad. I wasted 10 minutes on SQL Server help, for nothing.
hi Pinal,
I want to do dynamic project which will get all database names dynamicaly.i want to generate dynamic query to get all data base names.Can you please help me for that?
Waiting for positive reply
Thanks in Advance.
Vilinda
Thanks, I have a task at work to copy from test to production and this does the trick!
hii
can u pls define the indexed view with syntax?
is sql 2005 support it?
But i think this procedure will not copy any data on the script, isn’t ?
Yes it will not copy data
If you want data too, better take a backup
Hi MaxWell
Copy the SQl2005 Database to a new temp database. Downgrade to SQL 200- Database ( in DB properties). If needed ( using 2005 only conventions ) remove/change them.
Once done . Backup and restore in 2000 server, Or detach and reattach.
– Gs
Hi,
Can I automate the generation of scripts (Schema)?
I need to schedule it as a part of daily backup process. Please help me in this regard.
-Hema
Yeah Hema, you can do it either by using powershell, or by using the SMO object.
Thanks
Manish
Thank you so much!
hi pinal,
i hv 1 problem . i want to take back of sql server database tables n all other objects. but i dont hv access to database mgmt. studio. so how can i take my database backup..?
i hv heard tht it is possible using writing script.. n running it on our server as .aspx page…
so can u help me out in this issue..?
thanks ..
waiting for yr response..
hi ,
i have one problem, i installed the sqlserver2005 in my system but i didnt get the sample databases like northwind.. and also i didnt get the reporting services.. how can i get these plz help meee and also tell me some points to do this…
my question is ..how make large table…in today projects..bcz i am a begineer
I have doubt regarding the convertion of database created in 2005 to sqlserver 2000
hi,
This one is really good.
If anybody want to Delete All Data from DB then Just Run the following query.
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
GO
EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
‘
GO
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO
Hi,
I want to know if is it possible to alter a table (adding a column) in live production SQL server 2005 instance?
If anyone know please reply.
Thanks
Hi,
I want to know : how to copy some table from database A to database B in microsoft sql server 2005 ? And then how to schedule it everyday ?
Thanks and regards,
Sandhi