How get the structure (definition) of a table or view in SQL Server?

Sometimes, you might need to programmatically retrieve information about a table's or view's columns. In SQL Server, there are two nice and straightforward ways to access this information. All you need to know is the name of the table or view you are interested in.

This first method uses the so-called Information Schema Views available in SQL Server. These views allow you to get column information (among many more things) of a given table or view. Here's an example:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTableName'

The second method is similar, but instead uses the "sp_columns" stored procedure. This returns a similar, but not equal list of columns. Here's an example:

sp_columns 'MyTableName'

Both methods have their benefits, so be sure to test both and pick the one that suits your situation best.

Happy querying!

Keywords: get columns of a table using SQL, query columns, get schema of a table or view