Generating database tables' CREATE script

Question

I am working on an application which backs up database tables and stores their data as CSV. When I want to restore a table back to the database I use a 'model' table which creates an existing table based on the model. However there are multiple structures of these tables and I would like to generate the tables' 'Create' script and save it somewhere for restoring purposes. Is there anyway for this? I am only interested to do this via coding and not sql script because we are using both SQL Server and Oracle and this process will be automated for more than 3000 tables.

In the code below I create the table based on a table model:

 If ProductData.Tables(0).Rows(0).Item(0) = 0 Then 'Does not exist
        'Create table based on a model
        Dim sqlCopyFromModel As New SqlCommand("SELECT TOP 0 * INTO " & sFileName & " FROM s_20130702;", con)
        con.Open()
        Dim i As Integer = sqlCopyFromModel.ExecuteNonQuery()
        con.Close()
        'Enter data from CSV file
        Dim sqlInsertDataFromCSV As New SqlCommand("BULK INSERT " & sFileName & " FROM '" & txtFilePath.Text.Trim & "' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW=2 )", con)

        con.Open()
        sqlInsertDataFromCSV.ExecuteNonQuery()
        con.Close()
        MsgBox("success")

Little alarming that you are creating a custom solution, surely there are in built tools in SQL (simply backing it up) that would be far more efficient? — Dr Schizo
– Dr Schizo, Commented Aug 12, 2014 at 14:20
What is it you're trying to accomplish when a traditional database backup/restore is available? — Fuck StackOverflow
– Fuck StackOverflow, Commented Aug 12, 2014 at 14:34
@DMason It's a custom backup tool with special requirements. — alwaysVBNET
– alwaysVBNET, Commented Aug 12, 2014 at 14:45
Have you looked at the Entity Framework (Code First) model? It seems like that is what you are looking for. — Pradeep Kumar
– Pradeep Kumar, Commented Aug 12, 2014 at 14:46

Kim Berg Hansen · Accepted Answer · 2014-08-12 14:35:39Z

1

In Oracle you can use builtin package DBMS_METADATA where the function GET_DDL can give you the DDL ("create script") needed to create the table.

http://docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS66885

SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
FROM DUAL;

(If you are trying to do something that is database independent and works both on SQL Server and Oracle, then you'd better not use "TOP 0" ;-)

answered Aug 12, 2014 at 14:35

Kim Berg Hansen

2,03913 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generating database tables' CREATE script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related