Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "sys.columns" - Getting a List of Columns in a Table

By: FYIcenter.com

(Continued from previous topic...)

How To Get a List of Columns using the "sys.columns" View?

If you have an existing table, but you don't remember what are the columns defined in the table, you can use the "sys.columns" system view to get a list of all columns of all tables in the current database.

In order to a list of columns of a single table, you need to join sys.columns and sys.tables as shown in the tutorial example below:

SELECT * FROM sys.columns c, sys.tables t
   WHERE c.object_id = t.object_id
   AND t.name = 'tip'
GO
object_id   name         column_id user_type_id max_length
2073058421  id           1         56           4
2073058421  subject      2         167          80
2073058421  description  3         167          256
2073058421  create_date  4         61           8

You can see the column names easily from the sys.columns view. But you can only see the column type IDs. This requires another join to get the column type names. You may try the "sp_columns" stored procedure to get a better list of columns shown in the next tutorial.

(Continued on next topic...)

  1. What is a table?
  2. What are DDL (Data Definition Language) statements for tables?
  3. How to create new tables with "CREATE TABLE" statements?
  4. How To Get a List of All Tables with "sys.tables" View?
  5. How To Get a List of Columns using the "sys.columns" View?
  6. How To Get a List of Columns using the "sp_columns" Stored Procedure?
  7. How To Get a List of Columns using the "sp_help" Stored Procedure?
  8. How To Generate CREATE TABLE Script on an Existing Table?
  9. How to create new tables with "SELECT ... INTO" statements?
  10. How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD"?
  11. How To Delete an Existing Column in a Table with "ALTER TABLE ... DROP COLUMN"?
  12. How to rename an existing column with the "sp_rename" stored procedure?
  13. How to rename an existing column with SQL Server Management Studio?
  14. How to change the data type of an existing column with "ALTER TABLE" statements?
  15. How to rename an existing table with the "sp_rename" stored procedure?
  16. How To Drop an Existing Table with "DROP TABLE" Statements?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...