How can I script db from tsql script? 
Author Message
 How can I script db from tsql script?
Hi. I often need to script everything in a database, so I can compare the db
to another that exists on a different site. I make the complete script on
each of them, and then compare the resultant text files.

Is there a tsql command that will do this?  I find the enterprise manager
dialogs tedious to execute many times in a sleep-deprived state.

Thanks in advance.



Sat, 03 May 2003 03:00:00 GMT
 How can I script db from tsql script?

In Enterprise Manager, highlight db and right click on it. Choose All Tasks
and choose Generate SQL scripts. On the Options tab on the dialog box, there
is an option to script the db (among many other items)


Quote:
> Hi. I often need to script everything in a database, so I can compare the
db
> to another that exists on a different site. I make the complete script on
> each of them, and then compare the resultant text files.

> Is there a tsql command that will do this?  I find the enterprise manager
> dialogs tedious to execute many times in a sleep-deprived state.

> Thanks in advance.



Sat, 03 May 2003 03:00:00 GMT
 How can I script db from tsql script?
Save yourself a ton of time.

Go to www.red-gate.com and spend $50 for SQL Compare.  It will both identify
the changes but script out how to make it and run the script for you.

--
Doo
Senior Data Architect / DBA
PlanetJam Media Group


Quote:
> Hi. I often need to script everything in a database, so I can compare the
db
> to another that exists on a different site. I make the complete script on
> each of them, and then compare the resultant text files.

> Is there a tsql command that will do this?  I find the enterprise manager
> dialogs tedious to execute many times in a sleep-deprived state.

> Thanks in advance.



Sat, 03 May 2003 03:00:00 GMT
 How can I script db from tsql script?
Shmuel:

No, there is not a single T-SQL command available to script a database.
However,  the script for all objects is contained in the system table
'syscomments'

In fact, this is where SQL Enterprise Manager obtains the text for an
object script.

Ref: Books On Line,  Syscomments
==============================

syscomments (T-SQL)
Contains entries for each view, rule, default, trigger, CHECK constraint,
DEFAULT constraint, and stored procedure. The text column contains the
original SQL definition statements, which are limited to a maximum size of
4 MB. This table is stored in each database.

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

Important None of the entries in syscomments should be deleted. If an entry
in syscomments is manually removed or modified, the corresponding stored
procedure will not function properly. To hide or encrypt stored procedure
definitions, use CREATE PROCEDURE with the ENCRYPTION keyword.

----------------------------------------------------------------------------
----
Column name Data type Description
id int Object ID to which this text applies.
number smallint Number within procedure grouping, if grouped.  0 for
entries that are not procedures.
colid smallint Row sequence number for object definitions longer than 4,000
characters.
status smallint For internal use only.
ctext varbinary(8000) Actual text of the SQL definition statement.
texttype smallint 0 = User-supplied comment
1 = System-supplied comment
4 = Encrypted comment
language smallint For internal use only.
encrypted bit Indicates whether the procedure is encrypted.
0 = Not encrypted
1 = Encrypted
compressed bit Indicates whether or not the procedure is compressed.
0 = Not compressed
1 = Compressed
text nvarchar(4000) Actual text of the SQL definition statement.
===============================================================

Hope this helps

Ed Harper
Microsoft SQL Support.



Sun, 04 May 2003 03:00:00 GMT
 How can I script db from tsql script?
Thanks for your reply


Quote:
> Shmuel:

> No, there is not a single T-SQL command available to script a database.
> However,  the script for all objects is contained in the system table
> 'syscomments'

> In fact, this is where SQL Enterprise Manager obtains the text for an
> object script.

> Ref: Books On Line,  Syscomments
> ==============================

> syscomments (T-SQL)
> Contains entries for each view, rule, default, trigger, CHECK constraint,
> DEFAULT constraint, and stored procedure. The text column contains the
> original SQL definition statements, which are limited to a maximum size of
> 4 MB. This table is stored in each database.

> --------------------------------------------------------------------------
--
> ----

> Important None of the entries in syscomments should be deleted. If an
entry
> in syscomments is manually removed or modified, the corresponding stored
> procedure will not function properly. To hide or encrypt stored procedure
> definitions, use CREATE PROCEDURE with the ENCRYPTION keyword.

> --------------------------------------------------------------------------
--
> ----
> Column name Data type Description
> id int Object ID to which this text applies.
> number smallint Number within procedure grouping, if grouped.  0 for
> entries that are not procedures.
> colid smallint Row sequence number for object definitions longer than
4,000
> characters.
> status smallint For internal use only.
> ctext varbinary(8000) Actual text of the SQL definition statement.
> texttype smallint 0 = User-supplied comment
> 1 = System-supplied comment
> 4 = Encrypted comment
> language smallint For internal use only.
> encrypted bit Indicates whether the procedure is encrypted.
> 0 = Not encrypted
> 1 = Encrypted
> compressed bit Indicates whether or not the procedure is compressed.
> 0 = Not compressed
> 1 = Compressed
> text nvarchar(4000) Actual text of the SQL definition statement.
> ===============================================================

> Hope this helps

> Ed Harper
> Microsoft SQL Support.



Sun, 04 May 2003 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Scripting DB Objects using TSQL

2. Canned PARADOX scripts?

3. any body has a script to script users permissions on db objects

4. Running TSQL scripts in MSDE

5. TSQL Scripting of Create Table

6. scripting object through TSQL

7. Batch TSQL Script with SQLDMO

8. tsql scripts

9. running SQL script (TSQL) file in VB

10. Long TSQL Script

11. Job with TSQL script does not work

12. Scripting transfers in TSQL (6.5)


 
Powered by phpBB® Forum Software