Script out database schema

Question

Script out database schema

ranvir_2k

SSC-Addicted

Points: 462
More actions
January 14, 2010 at 3:51 am

#136548

Hi all,
I'm trying to write a script to script out the schemas for all the non system databases on a server.
I was wondering if someone can help me out on where to start.
Which table is this information stored?
I only want to script out the Create Database command not the objects within the database.
Thanks

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SQL 2000 or SQL 2005? You posted one thread in each forum.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

take a look at the view sys.schemas;

this seems to give me the lsit I think you were looking for; note you have to run this on each database:

select * from sys.schemas

--avoid dbo,guest,INFORMATION_SCHEMA and sys

where schema_id > 4

--avoid db_owner,db_accessadmin,db_securityadmin,db_ddladmin,db_backupoperator,db_datareader,

--db_datawriter,db_denydatareader,db_denydatawriter

and schema_id < 16384

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Right click database, tasks --> generate scripts. Follow the wizard.

That will allow you to script out the database schema. Unless you are talking about the user schema, and to get just a list of the user schemas. Then Lowell's script can list that for you.

http://msdn.microsoft.com/en-us/library/ms190387(SQL.90).aspx Discusses the user schema in 2005.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 4

Just curious, why would you want to script out creating the databases on your system if you do not want any of the tables, keys, indexes, etc that the database contains? Maybe I am misunderstanding your question.

Joie Andrew
"Since 1982"