Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script All object for database Expand / Collapse
Author
Message
Posted Tuesday, August 30, 2011 11:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:48 AM
Points: 24, Visits: 268
Hi,

can anyone suggest how can i script all object from a SQL server database.I have thought about SSIS packages.
can anyone help me to create SSIS package to script all object within a database??

Thanks & Regards,
Akshay
Post #1167710
Posted Tuesday, August 30, 2011 11:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
You can right-click the database in SSMS, go to Tasks, Generate Scripts, and walk through that. Will that do what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1167712
Posted Tuesday, August 30, 2011 12:17 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:30 PM
Points: 341, Visits: 806

If you want to do automate the process of scripting all objects in a database consider SQL Server Database Publishing Wizard. You can execute this program from command line

Database Publishing wizard fails in the following scenarios

If the stored procedure is encrypted
If you use "Execute As user" in stored procedures

Another way of doing it is using Red Gate Tools. You can script out objects using command line feature of SQL Compare Professional Version (not Standard). This does not give error in the above mentioned scenarios.

Reddy
Post #1167750
Posted Wednesday, August 31, 2011 7:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:48 AM
Points: 24, Visits: 268
Hi,

No my exact requirement is that i want to automate this work.i want to take backup of all the object script on daily basis for all the producation databases on single server.

I will then keep a retention period for the same.requirement is raised to save time in retriving the script for any object.

Please suggest.

Thanks
Aksh
Post #1168091
Posted Wednesday, August 31, 2011 7:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
Blowing the trumpet for Powershell and SQLPSX.

You can use a scriptingoptions object to make the script output configurable.

Get-SqlDatabase -dbname test -sqlserver server | Get-SqlTable | Get-SqlScripter | Set-Content -Path C:\script.sql
Get-SqlDatabase -dbname test -sqlserver server | Get-SqlStoredProcedure | Get-SqlScripter
Get-SqlDatabase -dbname test -sqlserver server | Get-SqlView | Get-SqlScripter

Post #1168100
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse