Only Column Names read from table
(1) By Yuvraj (yuvraj.jambhle) on 2021-04-12 13:23:01 [link]
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.
(2) By jose isaias cabrera (jicman) on 2021-04-12 14:07:34 in reply to 1 [link]
Have you tried, >PRAGMA table_info(tablename); that is how I grab all of my columns names from a table. ``` sqlite> PRAGMA table_info(Business_OBS_List); 0|Bus_OBS||0||1 1|Bus_Area||0||0 2|Bus_Org||0||0 ``` Maybe I am misunderstanding your question.
(4) By Yuvraj (yuvraj.jambhle) on 2021-04-13 10:18:13 in reply to 2 [link]
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.
(15) By anonymous on 2023-05-19 07:10:57 in reply to 2 [link]
Thnx Buddy It Worked :- L E O
(3) By Kees Nuyt (knu) on 2021-04-12 14:18:01 in reply to 1 [link]
Try:
```sql
SELECT name FROM pragma_table_info('Uploads') ORDER BY cid;
```
The available columns are:
`cid,name,type,notnull,dflt_value,pk`
(5) By Yuvraj (yuvraj.jambhle) on 2021-04-13 10:24:04 in reply to 3 [link]
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.
(6) By Warren Young (wyoung) on 2021-04-13 10:42:24 in reply to 5 [link]
The [pragma functions](https://www.sqlite.org/pragma.html#pragfunc) were added in SQLite 3.16.0. Are you using an older version?
(8) By Yuvraj (yuvraj.jambhle) on 2021-04-14 04:27:05 in reply to 6 [link]
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?
(9) By Warren Young (wyoung) on 2021-04-14 04:48:43 in reply to 8 [link]
Upgrade?
(12) By Kees Nuyt (knu) on 2021-04-14 11:03:49 in reply to 8 [link]
If you really can't upgrade to version 3.16.0 or newer, you will have to use
```sql
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.
(14) By Yuvraj (yuvraj.jambhle) on 2021-04-14 14:35:23 in reply to 12 [link]
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.
(16) By Keith Medcalf (kmedcalf) on 2023-05-19 15:33:47 in reply to 12 [link]
`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.
(17) By anonymous on 2023-07-24 15:20:30 in reply to 3 [link]
WORKS! Thnk's! (I have used 2nd field from SELECT * FROM SQLITE_SCHEMA instead table name ('Uploads'), and without ORDER BY...) /Drago/
(7) By anonymous on 2021-04-13 12:44:33 in reply to 1
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 <b>Columns</b> option. Click on <b>Columns</b> and drag it to the query window. <i>This will enumerate the columns names in that given table separated by comma at the cursor position.</i> (easier/faster than writing queries for an equivalent result!).
For SQLite, using the shell, try the following (<i>which is a copy of my session - note the version I am using</i>):
```
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.
<b>PS:</b> Using a parameter for the name of the table means that you specify the table name <i>once</i> only.
(10) By Yuvraj (yuvraj.jambhle) on 2021-04-14 05:14:15 in reply to 7 [link]
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.
(11) By anonymous on 2021-04-14 09:35:23 in reply to 10 [link]
>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 <i>Datalogger1</i> 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 <i>Datalogger1</i> by <i>your table name</i>.
If it still <i>does not work</i>, copy your session and paste into a new message in this thread so others can see where you might be going wrong.
(13) By Yuvraj (yuvraj.jambhle) on 2021-04-14 14:18:56 in reply to 11 [link]
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.