SQL Snippets: Select All Fields for All Tables In Database

Microsoft SQL ServerThis 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'