A way to script all DBs from a server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » A way to script all DBs from a server

A way to script all DBs from a server

Rate Topic

Display Mode

Topic Options

Author

Message

ramadesai108

Posted Friday, December 03, 2010 12:31 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

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

Post #1030103

GSquared

Posted Friday, December 03, 2010 12:32 PM

SSCoach

Group: General Forum Members
Last Login: Monday, June 17, 2013 1:45 PM
Points: 15,442, Visits: 9,572

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

ramadesai108

Posted Friday, December 03, 2010 1:20 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

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

Post #1030128

GSquared

Posted Friday, December 03, 2010 2:03 PM

SSCoach

Group: General Forum Members
Last Login: Monday, June 17, 2013 1:45 PM
Points: 15,442, Visits: 9,572

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

george sibbald

Posted Friday, December 03, 2010 4:24 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 5,317, Visits: 11,307

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

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

Post #1030195

Steve Jones - SSC Editor

Posted Friday, December 03, 2010 7:14 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:09 AM
Points: 31,526, Visits: 13,864

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

homebrew01

Posted Monday, December 06, 2010 12:02 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:02 PM
Points: 2,581, Visits: 7,295

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

parthi-1705

Posted Monday, December 06, 2010 2:15 PM

Mr 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 »

Permissions