sp_helptext for Tables

Hi Jay,

Thank you.
Its very helpful for me if you can do it.
Thanks once again.

Hemant

"Jay Konigsberg" wrote in message

> Are you saying you want to create the DDL for a table, from a T-SQL
> statement? If so, you can't do it from anything less than a procedure. I
> wrote such a procedure for 2000 and got it about 98% working, it wasn't
> that hard, but it took a while.
>
> Shall I dig it out for you?
>
> --
> Jay Konigsberg
> SQL Server DBA in Sacramento, CA
> http://www.linkedin.com/in/jaykonigsberg
>
> Live in Sacramento, CA?
> Join the Sacramento SQL Server User Group on LinkedIn
> http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>
>
>
> "Hemant" wrote in message
>
>> Hi,
>>
>> I am working on sql server 2000. I want to generate script of a table in
>> a query.
>> Is there any way like sp_helptext for views to create Script for tables?
>>
>> Thanks,
>> Hemant
>>
>
>
 >> Stay informed about: sp_helptext for Tables 

Hemant
VB project, you need to use SQL DMO object library


Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)

'==========================================================================
' Parameters
' -------------------------------------------------------------------------
' strLogin: The Login Name of the account you use to connect to the server
' strPwd: The Password for the account you use to connect to the server
' strDataBase: The name of the database you want to create script for
' StrFilePath: The path and filename to store the SQL file
'==========================================================================

Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj

Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")

Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
Const SQLDMOScript2_NoCollation As Long = 8388608

' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation

' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")


' Script Tables and Triggers, ignoring system
' tables and system generated triggers
For Each genObj In db.Tables
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath,,SQLDMOScript2_NoCollation
For Each objTrigger In genObj.Triggers
If objTrigger.SystemObject = False Then
objTrigger.Script intOptions, StrFilePath
End If
Next
End If
Next


MsgBox "Finished generating SQL scripts."

End Sub

Save the module as MyModule.

To call the procedure, open the Immediate window, type the following line,
and then press ENTER:Call
ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")





"Hemant" wrote in message

> Hi Jay,
>
> Thank you.
> Its very helpful for me if you can do it.
> Thanks once again.
>
> Hemant
>
> "Jay Konigsberg" wrote in message
>
>> Are you saying you want to create the DDL for a table, from a T-SQL
>> statement? If so, you can't do it from anything less than a procedure. I
>> wrote such a procedure for 2000 and got it about 98% working, it wasn't
>> that hard, but it took a while.
>>
>> Shall I dig it out for you?
>>
>> --
>> Jay Konigsberg
>> SQL Server DBA in Sacramento, CA
>> http://www.linkedin.com/in/jaykonigsberg
>>
>> Live in Sacramento, CA?
>> Join the Sacramento SQL Server User Group on LinkedIn
>> http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>>
>>
>>
>> "Hemant" wrote in message
>>
>>> Hi,
>>>
>>> I am working on sql server 2000. I want to generate script of a table in
>>> a query.
>>> Is there any way like sp_helptext for views to create Script for tables?
>>>
>>> Thanks,
>>> Hemant
>>>
>>
>>
>
>
 >> Stay informed about: sp_helptext for Tables