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

A way to script all DBs from a server Expand / Collapse
Author
Message
Posted Friday, December 3, 2010 12:31 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Is there a way to script all Databases from a server?
Post #1030103
Posted Friday, December 3, 2010 12:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #1030105
Posted Friday, December 3, 2010 1:20 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Can you please show me the command that i need to pass to sp_MSForEachDB?
Post #1030128
Posted Friday, December 3, 2010 2:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #1030152
Posted Friday, December 3, 2010 4:24 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:23 AM
Points: 5,886, Visits: 13,047
what exactly do you mean by script all databases? what information are you after?


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

Post #1030195
Posted Friday, December 3, 2010 7:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1030217
Posted Monday, December 6, 2010 12:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:05 PM
Points: 2,834, Visits: 8,544
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



Post #1030770
Posted Monday, December 6, 2010 2:15 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195


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
Post #1030838
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse