System tables or also known as the system catalog, contain information about all the objects, data types, constraints, configuration options, and resources available to SQL Server. Each database has a system catalog and the structure of the database is defined by this catalog. Knowing the structure is crucial to a database administrator and the database too. The database server uses the catalog to keep order. I know I sometimes overlook this great resource that's available to us. The table we are going to look at in this article is the sys.columns table. This table does return the column names of a particular table but also returns a lot of other information about a column in a table.
This example using sys.columns is to simply return all information about our table.
SELECT * FROM sys.columns
WHERE object_id = object_id('YourTableName')
To get some more examples in using sys.columns, go to my article found at Crush The Soul