SQL Syntax For Reading sqlite_master Object

[RichardN]

One for the SQL gurus....
I am working on a generic database browsing program that has a couple of different tables.

I want to read the sqlite_master object to insert the column names from one table into a new column of a new table where I will later associate those column names with plain language titles.

I cannot for the life of me arrive at the correct SQL syntax to copy the column names of Table1 into a 'FieldNames' column in new Table2.

Help!

 

[citywest]

Hi Richard,

Fairly straightforward. To create a new empty table from an existing table:

CREATE TABLE new_table_name AS SELECT * FROM original_table_name

But I think you know this so am including for completeness.

To get the column names from the original table:

Dim SQL as SQL
Dim ResultsSet As Cursor
Dim ResultsString As String

ResultsString = ""

ResultsSet = SQL.ExecQuery("PRAGMA table_info(`original_table_name`)")

For i = 0 To ResultsSet.RowCount -1

ResultsSet.Position = i

ResultsString = ResultsString & ResultsSet.getString("name") & "," 'Build comma delimited list

Next

To add these in a single field as a comma delimited list in you New Table (you should remove the last comma from the list first):

SQLString = "INSERT OR REPLACE INTO new_table_name (id, fieldnames) VALUES (" & whateverID & ", '" & ResultsString & "')"

SQL.ExecuteNonQuery(SQLString)

Common Field Values are:

name = Column Name
cid = Position
type = Column Type (Text, Int, Date etc)

Hope this helps,

Mark S.

 

[RichardN]

Thanks Mark.

On reflection I don't think the best way to do this is programaticly as it will only be done once. The lookup table should be put into place when the principle data is finalised so I will probably do it with an SQL manager during development.

Any offers as to a regular SQL script to complete the task ???