Script Database with T-SQL

[Motlatjo]

Hi

I want to have a Stored Procedure that will script my Databases, i do not want to back up data, only to script database through T-SQL

Thank you
Motlatjo

 

[Olaf Doschke]

Which SQL SErver Version?

Today you can use Visual Studio and create an SQL Server Database Project, connect to a database and get the full database schmea.
That project can be your documentation of the schema, you can also compare against another db or db project and you can change schema in the project and apply changes to a database.

Bye, Olaf.

 

[Motlatjo]

Thanks Olaf

I have decided to use the the sys.sql_modules and sys.objects system tables
But i wonder why the user tables are omitted, maybe you can help

Thanks

 

[gk53]

use instead
select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.VIEWS

 

[Motlatjo]

The thing is, with
select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.VIEWS

I dont get to find the Object Structures, e.g. CREATE TABLE.......

 

[gk53]

this query will give you all code for all stored procedures in database you are connected
select object_definition(object_id(routine_name)) from information_schema.routines
next one all views
select * from information_schema.VIEWS
and for table you can write a code to generate script base on data from
select * from information_schema.COLUMNS

 

[philhege]

If you are interested in an alternative method, there's a plug-in to Visio that allows you to script out all of the database objects that you select in the reverse-engineer process on a database diagram.

http://forwardengineer.codeplex.com/

-----------
With business clients like mine, you'd be better off herding cats.

 

[Olaf Doschke]

Why do this via sys.sql_modules, sys.objects or INFORMATIONSCHEMA?
Why all the hassle?

If you really just want a script, go into SQL Server Management Studio, connect to a database, right click on a database node and then choose Tasks->Generate scripts, follow the wizard.

Bye, Olaf.

 

[Motlatjo]

Hi Olaf

I wanted this to happen automatically everyday, but do not worry, i managed to get a solution, only the tables that i cannot script now, im looking at that now

Thanks