Hello, I want to read only the column name from the table. I have tried this query << SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'LogData1'>> and I got result like <<CREATE TABLE [LogData1] ([Id] INTEGER PRIMARY KEY, [Time] datetime, [W1] float, [W2] smallint, [W3] smallint, [W4] smallint, [W5] smallint)>>.

But I want to display the only column name like ID,Time,W1,W2,W3,W4,W5.

Please help me. I am trying this also << Select * from LogData1 limit 0 >>, but still no result getting.

I have acheived same requirement in SQL server using this query << Select Column_Name from information_Schema.Columns where Table_Name = 'LogData1' >>.

Please provide me the solution I want to read only column names from the table, not all information.

is it ok, but I don't want all information about the table.

I want to read only column names....like Bus_OBS, Bus_Area, Bus_Org.

Thnx Buddy It Worked :- L E O

Try:

SELECT name FROM pragma_table_info('Uploads') ORDER BY cid;

The available columns are: cid,name,type,notnull,dflt_value,pk

Sorry boss, it didnt work. As per your suggestion I have tried

SELECT name FROM pragma_table_info('Datalogger1') ORDER BY cid and SELECT name FROM pragma_table_info(Datalogger1) ORDER BY cid

I got error like << near "(": syntax error: >>

Please provide me another solution.

The pragma functions were added in SQLite 3.16.0. Are you using an older version?

Hello, Pragma function works but if I use select name .......then it not works. I have testing the same thing on Sqlite version 3.7.0.

didn't recognize name field, for this query.<< SELECT name FROM pragma_table_info('Datalogger1') ORDER BY cid >>

is there any other option?

Upgrade?

If you really can't upgrade to version 3.16.0 or newer, you will have to use

PRAGMA table_info('Datalogger1');

This statement will return the same information in a result set, that you can retrieve in the same way as the result set from any SELECT statement.

If you use the value of cid as an index in the table where you store the column names in your program, the order in the result set will not matter.

Hello, I have tried this query for fetching table name from database << SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'NEO%' ORDER BY name >>

above mentioned query works, I am sharing this only for ref.

cid is only by happenstance the column identification when the table/view contains no hidden or computed columns, or it the table contains these things, then only the xinfo pragma cid is an actual column ordinal.

That is, table_info does not return full information so the cid is merely the row_number() and does not identify the column.

This is because table_info omits some columns -- particularly hidden or computed columns.

Although the cid is the row_number of the output, when using the xinfo pragma's (eg, table_xinfo) the number of rows output is equal to the number of columns (none are excluded) and since the columns are generated in-order, the cid represents, by happenstance, the column ordinal.

Since there is no a-priori way to know that a table does or does not contain hidden or computed columns, then it is foolish to use the cid returned by the regular info pragma's when there is a "properly accurate" method (xinfo) available.

WORKS! Thnk's! (I have used 2nd field from SELECT * FROM SQLITE_SCHEMA instead table name ('Uploads'), and without ORDER BY...) /Drago/

If you are referring to SQL Server, you might be using SSMS.

If so, expand the database to show all table names and then expand any given table, you will see a Columns option. Click on Columns and drag it to the query window. This will enumerate the columns names in that given table separated by comma at the cursor position. (easier/faster than writing queries for an equivalent result!).

For SQLite, using the shell, try the following (which is a copy of my session - note the version I am using):

SQLite version 3.35.4 2021-04-02 15:20:15
Enter ".help" for usage hints.
sqlite>
sqlite> /* Open your database ... chinook.db in my example */
sqlite> .open ./db/chinook.db
sqlite> /* Initialize parameters */
sqlite> .param init
sqlite> /* set the value of parameter my#Table to table name artists */
sqlite> .param set :myTable artists
sqlite> /* Set the columns names as a SELECT statement */
sqlite> select 'select ' || group_concat(replace('[#]','#', name),',') || ' from ' || :myTable || ';' as SelStmt from pragma_table_info(:myTable);
SelStmt
--------------------------------------
select [ArtistId],[Name] from artists;
sqlite> /* try another table, say, employees Remember parameter names are case-sensitive */
sqlite> .param set :myTable employees
sqlite> select 'select ' || group_concat(replace('[#]','#', name),',') || ' from ' || :myTable || ';' as SelStmt from pragma_table_info(:myTable);
SelStmt                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select [EmployeeId],[LastName],[FirstName],[Title],[ReportsTo],[BirthDate],[HireDate],[Address],[City],[State],[Country],[PostalCode],[Phone],[Fax],[Email] from employees;
sqlite>

The relevant results were:

select [ArtistId],[Name] from artists;

and

select [EmployeeId],[LastName],[FirstName],[Title],[ReportsTo],[BirthDate],[HireDate],[Address],[City],[State],[Country],[PostalCode],[Phone],[Fax],[Email] from employees;

Change the value of parameter :myTable (as desired) for scripting other tables

sqlite> .param set :myTable invoices
sqlite> select 'select ' || group_concat(replace('[#]','#', name),',') || ' from ' || :myTable || ';' as SelStmt from pragma_table_info(:myTable);
SelStmt                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------
select [InvoiceId],[CustomerId],[InvoiceDate],[BillingAddress],[BillingCity],[BillingState],[BillingCountry],[BillingPostalCode],[Total] from invoices;

The relevant result:

select [EmployeeId],[LastName],[FirstName],[Title],[ReportsTo],[BirthDate],[HireDate],[Address],[City],[State],[Country],[PostalCode],[Phone],[Fax],[Email] from employees;

This forum frowns upon the wrapping of column names within square brackets. It works with the SQLite shell and I always use them; I think square brackets add clarity. Optionally, you might consider editing the script to remove them.

PS: Using a parameter for the name of the table means that you specify the table name once only.

Hello, I don't understand what you say?

my requirement is I want to read only column names from the table.

I have tried this but it not works << SELECT name FROM pragma_table_info('Datalogger1') ORDER BY cid >>.

column names are not fixed it's getting vary table to table.

I want to read only column names from the table.

I have tried this but it not works << SELECT name FROM pragma_table_info('Datalogger1') ORDER BY cid >>.

It will NOT WORK unless you have opened a database that has a table named Datalogger1 in it.

Suggestion:

1. Start a new SQLite CLI session.

2. Open your database.

3. Run the .tables command

4. Run the SQL statement, taking care to replace Datalogger1 by your table name.

If it still does not work, copy your session and paste into a new message in this thread so others can see where you might be going wrong.

Hello, I have tried this query for fetching table name from database << SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'NEO%' ORDER BY name >>

above mentioned query works, I am sharing this only for ref.