Finding All Columns

A table in SQL Server is made up of columns. Each column has a data type, precision, default value and many other attributes. But sometimes you might want to find all the columns in a table, maybe for a data dictionary report. What are the API's available to determine the number of columns in a table and their names?? SQL Server offers two methods to determine this information.

Using the syscolumns table.
Using the information schema views.

The syscolumns table contains one row for every column in every table and view, and a row for each parameter in a stored procedure. If you want to use the information in this table to get the list of columns, here is query that you can use.


SELECT name FROM syscolumns
	WHERE OBJECT_NAME (id) = 'authors'
	ORDER BY colid

The columns of interest to us in this table are id, which indicates the object identifier. We use the OBJECT_NAME function to get the name of the identifier. The colid column indicates the order of the columns.

The other method that you can use is the information schema views. These views provide an internal, system table-independent view of the SQL Server meta data. Information schema views allow applications to work properly even though significant changes have been made to the system tables. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA. Using these views, here is query that you can use to get the list of columns in a table.


SELECT column_name FROM information_schema.columns
	WHERE table_name = 'authors'
	ORDER BY ordinal_position

Ok, so we have seen two methods, but which one should you use?? The safest method is the information schema views, since they protect your application from any changes that Microsoft might make to the system tables.

Home