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 dont 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.
I believe there is a way to get the statement through excel VB.
Please release my inquiry
Thanks a lot.

hara999 wrote:
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.
I can script DDL statement (Create or Drop, etc...) from DB through
SQL
Sever Enterprise Manager Tool and make a text file from it.
I believe there is a way to get the statement through excel VB.

You can use ADO's OpenSchema method to get objective schema information
e.g. table names and columns names. DDL is another matter: it is
subjective, better written by a human than a machine.

Take for example your DDL: you've used the synonym int for the standard
term INTEGER. Any tools for generating DDL will have similar
preferences and assumptions built in and should be assessed on it
merits (I'm currently working on my own as a pet project). If the one
you are currently using via EM is acceptable to you then use it. The
one I've seen produces a script that looks quite dissimilar from
standard SQL.

ADO does not have a GenerateDDL method. Doing so would place too much
of a burden on the authors of the OLE DB providers. Even Microsoft's
own OLE DB provider for SQL Server doesn't expose many of the
OpenSchema rowsets e.g. the one for VIEWs' definitions is a
disappointing omission. Expecting a third party vendor to decided on
and expose a DDL syntax may be a little hopeful.

Jamie.

--