Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - odbc_columns() - Listing All Columns in a Table

By: FYIcenter.com

(Continued from previous topic...)

How To List All Columns in a Table using odbc_columns()?

If you want to get a list of all columns in a table, you can use the odbc_columns() function, which can actually be used to list all columns in all tables and views in the database. The syntax of odbc_columns() is:

$result_set = odbc_columns($connection_object,
   $qualifier, # database name for SQL Server
   $schema, # schema name for SQL Server
   $table_name, # table or view name for SQL Server
   $column_name # column name
   )
#- The returning result set contains 5 fields:
#- TABLE_QUALIFIER, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME
#- DATA_TYPE, TYPE_NAME, PRECISION, LENGTH, SCALE, RADIX
#- NULLABLE, REMARKS

The schema, table_name and column_name arguments accept search patterns ( '%' to match zero or more characters and '_' to match a single character).

The tutorial example below shows you how to get a list of columns in a table called fyi_rates in the current database, FyiCenterData, which is hard coded in the DSN definition:

<?php
  $con = odbc_connect('FYI_SQL_SERVER','sa','FYIcenter');

# odbc_tables($con, $database, $schema, $table, $column);
  $res = odbc_columns($con, 'FyiCenterData','%','fyi_rates','%');
  print("Column list of fyi_rates:\n");
  while (odbc_fetch_row($res)) {
     print("   ".odbc_result($res,2));
     print(", ".odbc_result($res,3));
     print(", ".odbc_result($res,4));
     print(", ".odbc_result($res,5));
     print(", ".odbc_result($res,6));
     print(", ".odbc_result($res,7));
     print(", ".odbc_result($res,8));
     print(", ".odbc_result($res,9));
     print(", ".odbc_result($res,10));
     print(", ".odbc_result($res,11));
     print(", ".odbc_result($res,12)."\n");
  }
  odbc_free_result($res);

  odbc_close($con); 
?>

If you run this script, you will get something like:

Column list of fyi_rates:
   dbo, fyi_rates, id, 4, int, 10, 4, 0, 10, 1,
   dbo, fyi_rates, comment, 12, varchar, 16, 16, , , 1,

Now you know that table, fyi_rates, has two columns: "id INT" and "comment VARCHAR(16)".

(Continued on next topic...)

1. What Are the Requirements to Use ODBC Connections in PHP Scripts?
2. What Are Commonly Used ODBC Functions in PHP?
3. How To Test ODBC DSN Connection Settings?
4. How To Connect to a SQL Server using odbc_connect()?
5. How To List All DSN Entries on Your Local Machine using odbc_data_source()?
6. How To Execute a SQL Statement using odbc_exec()?
7. How To Retrieve Error Messages using odbc_errormsg()?
8. How To Turn Off Warning Messages during PHP Execution?
9. How To Receive Returning Result from a Query?
10. How To Loop through Result Set Objects using odbc_fetch_row()?
11. How To Retrieve Field Values using odbc_result()?
12. How To List All Tables in the Database using odbc_tables()?
13. How To List All Columns in a Table using odbc_columns()?
14. How To Create Prepared Statements using odbc_prepare()?