How to get the Create DDL statement from SQL server through excel.

[hara999]

As you know, we can connect SQL server through excel VB and we can get the
data from a DB using DML statement. And also we can create a Table on the SQL
server DB, through excel VB as command execution. The following is a simple
example I used.

Sub ExecuteDDL()
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim adoRs As New ADODB.Recordset

Cn.Provider = "sqloledb"
Cn.ConnectionTimeout = 7
Cn.CommandTimeout = 0
Cn.Provider = "sqloledb"
Cn.ConnectionString = "Data Source=TEST;DSN=TEST;UID=TEST;PWD=TESTâ€
Cn.Open

Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "CREATE TABLE TEST_TABLE (C1 int, C2 char(100))"
Cmd.CommandType = adCmdText
Cmd.Execute

Cn.Close
End Sub

Before the execution above, I want to get the DDL statement of a Table from
DB.
The procedure that I want is like this.
I have a Table name that I want to recreate from DB.
1. Get the create DDL statement from DB
2. Edit the DDL statement
3. Execution DDL statement
I want to process above 3 step on excel through VB, but I don’t know how to
get the create DDL statement through excel macro( Number 1 step).

I can script DDL statement (Create or Drop, etc...) from DB through SQL
Sever Enterprise Manager Tool and make a text file from it.
But that way is very inconvenient to handle many tables and is required too
many manual steps.
It will be really helpful to all user for SQL server and Excel
Thanks a lot.