A way to script all DBs from a server

Question

A way to script all DBs from a server

ramadesai108

SSChampion

Points: 10609
More actions
December 3, 2010 at 12:31 pm

#232144

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

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

ramadesai108 SSChampion Points: 10609 More actions · Answer 2

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

GSquared SSC Guru Points: 260824 More actions · Answer 3

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

george sibbald SSC Guru Points: 104210 More actions · Answer 4

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

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

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.

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 7

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