A way to script all DBs from a server

  • Is there a way to script all Databases from a server?

  • You can query sys.databases, or you can use sp_MSForEachDB.

    - 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

  • Can you please show me the command that i need to pass to sp_MSForEachDB?

  • Bing or Google the name of it. You'll find data that way.

    - 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

  • what exactly do you mean by script all databases? what information are you after?

    ---------------------------------------------------------------------

  • I don't think you can generate a script from either the database or the objects from T-SQL. SMO has an method to support this, and you can right click a database in SSMS and select "Tasks....Generate Scripts"

    If you are looking to copy the database, a backup restore will work.

  • This thread deals with scripting database objects, and I run an automated process to dump them to files on a network share ... not quite sure what the OP had in mind though.

    http://www.sqlservercentral.com/Forums/Topic1025959-146-1.aspx

  • DECLARE @string VARCHAR(MAX)

    SET @string = ''

    SELECT @string = @string + 'EXEC ( ''sp_helptext ' + name + ' '')' from sys.sysobjects where xtype in('p','tr','v') order by Xtype

    EXEC ( @string )

    and for tables use

    http://www.stormrage.com/Portals/0/SSC/sp_GetDDL2005_V306.txt

    the usage is simple:

    exec sp_getDDL tablename

    or

    exec sp_getDDL 'schemaname.tablename'

    Refer

    http://www.sqlservercentral.com/scripts/SQL+Server+2005/67515/

    I have used the above queries to fetch data from single database, for each database try with sp_MSForEachDB where you can use each and every database

    Thanks

    Parthi

    Thanks
    Parthi

Viewing 8 posts - 1 through 7 (of 7 total)

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