Hot to get the create DDL statement from SQL Server through excel.

H

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top