How to get the Script for an object using query Analyser?

Hi,

I wanted to know how to get the script for an object using query analyser is there a system procedure to get the script. how does the enterprise manager generate the script?

Check out sp_helpText in BOL.

Cheers,
Loonysan

|||

sp_helptext is used for procedure,view,trigger etc

what if i want to get the scrip of a table? or job like what we get in the enterprise manage using the GENERATE sCRIPT option

|||

sp_help describes database objects. It might not be in the exact format you need it, but you should be able to manipulate the stored procedure in the way you need it. I also did a query like the following and this gave me information like column name, datatype and length of a table. There are other columns in the syscolumns table that specifiy collation and if it can contain a null (isnullable field in syscolumns).

select c.[name],t.[name],c.length
from sysobjects o
join syscolumns c
on o.id = c.id
join systypes t
on c.xtype = t.xtype
where o.[name] = 'nissannow_orders'

Results:

CreateDate datetime 8
OrderDate datetime 8
ExportSubmissionDate datetime 8
ModifiedDate datetime 8
OrderID int 4
CustomerID int 4
DealerIDDealerVisited int 4
CreatedBy varchar 50
ShipPrimaryPhone varchar 20
ShipEmailAddress varchar 50
ModifiedBy varchar 50
ShipCity varchar 50
ShipStateProvince varchar 50
ShipZip varchar 20
ShipFirstName varchar 100
ShipLastName varchar 100
ShipAddress1 varchar 100

Let me know if you need any more assistance with this.

|||There is no easy way to do this in TSQL. You need to either write a SP that does the work using the metadata in the system tables or use the built-in client API like DMO/SMO. You can write VB scripts for example that uses DMO to generate scripts or DTS/SSIS tasks.