Script to list data types of all columns in a Database in SQL Server

SQL Server tables displays the data type of
each column inside the table. You can do this dozens of ways, but a popular method shown in
the following example joins the sys.objects table with the sys.columns table. There are two
functions that you may not be familiar with in the following code. The TYPE_NAME() function
translates the data type id into its proper name. To go the opposite direction, you could use the
TYPE_ID() function. The other function of note is SCHEMA_ID(), which is used to return the
identity value for the schema. This is useful primarily when you want to write reports against the
SQL Server metadata.

USE AdventureWorks
GO

SELECT o.NAME AS ObjectName
 ,c.NAME AS ColumnName
 ,TYPE_NAME(c.user_type_id) AS DataType
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
WHERE o.NAME = 'Department'
 AND o.Schema_ID = SCHEMA_ID('HumanResources')