Listing all columns from a SQL Server database

In this short post, we are going to learn how to list all columns of every table in a SQL Server database.
We want to get the schema, the name of the table, the name of the column and the data type.

Query

We will be using a simple SQL query, like this one:

SELECT
SCHEMA_NAME(TAB.schema_id) 'Schema'
,TAB.name 'Table_Name'
,COL.NAME 'Column_Name'
,TYP.name 'Type'
FROM
SYS.COLUMNS COL
INNER JOIN
SYS.TABLES TAB ON TAB.[object_id] = COL.[object_id]
INNER JOIN
SYS.TYPES TYP ON COL.system_type_id = TYP.user_type_id
WHERE
TAB.[is_ms_shipped] = 0
ORDER BY
COL.name,TYP.name

Don’t forget to execute the query in the database you want.
This query is tested in SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016 and SQL Server 2017.