Day: December 6, 2022

This post is part of the series on SQL Snippets.

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'