Script All object for database

  • aksh.2411

    Right there with Babe

    Points: 761

    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

  • GSquared

    SSC Guru

    Points: 260824

    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

  • SQLback

    SSCrazy

    Points: 2897

    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

  • aksh.2411

    Right there with Babe

    Points: 761

    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

  • MysteryJimbo

    SSC-Insane

    Points: 24203

    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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply