How to list field or column names in SQL

My brother was writing a code of a search module as part of his inventory system project. The search module should provide options to users to select the field to search from. For example, I should be able to search Product database using ProductCode, ProductName or Part Number. What he decided to do is to list all available fields in the table and put it on a combobox. So his challenge was, How to select all column names in SQL? (He is using SQL Server).


SQL server database has a syscolumns table which contains columns of each table in the database. You can get the column name by using the following query.

SELECT [Name] FROM syscolumns
WHERE id=OBJECT_ID(N'dbo.tblProducts')
ORDER BY colid

To call it from a VB.NET application, you can use SqlDataReader the same way you do it on a ordinary table.

cmd.CommandText = "SELECT [Name] FROM syscolumns WHERE id=OBJECT_ID(N'dbo.tblProducts') ORDER BY colid"

Dim lrd As SqlDataReader = cmd.ExecuteReader()
cboProducts.Items.Clear()
While lrd.Read()
cboProducts.Items.Add(lrd("[Name]").ToString())
End While

To stay up-to-date on Technology news, subscribe now.