Script all object in database with TSQL

Question

Script all object in database with TSQL

fasttrack

SSCertifiable

Points: 6071
More actions
November 28, 2007 at 1:43 pm

#180860

Hello,
in management studio (sql 2005) there is a useful task that provide the "script" of all object (triggers, tables, constraints and so on) in database.
I would like to do this with TSQL, is there any command, stored proc or anything else that has the same functionality?
Thank

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Not that I can recall but there have been several scripts posted to use the system tables or schema views in the past to do this or you could build your own. Or you might use SMO but that is not T-SQL.

fasttrack SSCertifiable Points: 6071 More actions · Answer 2

I would like to use something that is provided by Microsoft SQL without creating by self .

any help will be very appreciated.

thank

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 3

You can script view, functions, stored procedures and triggers using this

select object_definition(object_id)

from sys.objects

where type_desc in ('SQL_SCALAR_FUNCTION',

'SQL_STORED_PROCEDURE',

'SQL_TABLE_VALUED_FUNCTION',

'SQL_TRIGGER',

'VIEW')

There are plenty of scripts around for tables.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

Are you trying to script database objects dynamically, for a specified database, or trying to avoid all the SSMS clicking to generate the scripts?

There are third party tools that will script all database objects, but Microsoft has only provided the generate scripts ability.

My blog: http://jahaines.blogspot.com

fasttrack SSCertifiable Points: 6071 More actions · Answer 5

Hello,

I have to copy the database structure, but the problem is that the dataabse structure change everytime, so I need something dinamically that create all objects present in the database automatically, otherwise I have another but not common solution: making a bakup and delete all data inside (just to have an dentical structure database).

Thank

Antares686 SSC Guru Points: 125444 More actions · Answer 6

How are the changes made? I would setup maybe a second database with the same sturcutre and require anytime someone makes a change they do to both databases (sorry thought you were using 2000). From there I believe you can setup notifications when schema changes occurr and then just write something to compare the two to be sure match.

fasttrack SSCertifiable Points: 6071 More actions · Answer 7

hello,

the problem is that DBs are different from several client so it is very hard to follow each change.

thank