SQL Snippets: Select All Fields for All Tables In Database

The following SQL snippet will select all fields for all tables in the selected database returning formatted column types.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Tables'].name AS 'Table Name'
	,['Columns'].column_id AS 'Column ID'
	,['Columns'].name AS 'Column Name'
	,CASE ['Types'].name
		WHEN 'int' THEN ['Types'].name
		WHEN 'tinyint' THEN ['Types'].name
		WHEN 'smallint' THEN ['Types'].name
		WHEN 'bigint' THEN ['Types'].name
		WHEN 'uniqueidentifier' THEN ['Types'].name
		WHEN 'timestamp' THEN ['Types'].name
		WHEN 'datetime' THEN ['Types'].name
		WHEN 'image' THEN ['Types'].name
		WHEN 'text' THEN ['Types'].name
		WHEN 'varbinary' THEN ['Types'].name + '(' + CAST(['Columns'].max_length AS VARCHAR(10)) + ')'
		WHEN 'binary' THEN ['Types'].name + '(' + CAST(['Columns'].max_length AS VARCHAR(10)) + ')'
		WHEN 'char' THEN ['Types'].name + '(' + CAST(['Columns'].max_length AS VARCHAR(10)) + ')'
		WHEN 'varchar' THEN ['Types'].name + '(' + CAST(['Columns'].max_length AS VARCHAR(10)) + ')'
		WHEN 'nvarchar' THEN ['Types'].name + '(' + CAST(['Columns'].max_length AS VARCHAR(10)) + ')'
		WHEN 'decimal' THEN ['Types'].name + '(' + CAST(['Columns'].precision AS VARCHAR(10)) + ',' + CAST(['Types'].scale AS VARCHAR(10)) + ')'
		WHEN 'numeric' THEN ['Types'].name + '(' + CAST(['Columns'].precision AS VARCHAR(10)) + ',' + CAST(['Types'].scale AS VARCHAR(10)) + ')'
	END AS'Data Type'
FROM
	sys.tables AS ['Tables']
INNER JOIN
	sys.columns AS ['Columns']
		ON
			['Columns'].object_id = ['Tables'].object_id
INNER JOIN
	sys.types AS ['Types']
		ON
			['Types'].system_type_id = ['Columns'].system_type_id

WHERE
	['Tables'].type = 'U'

Read original post SQL Snippets: Select All Fields for All Tables In Database at azurecurve|Ramblings of an IT Professional