A nice addition for your Cookbook (Code Snippets):
sys.tables provides one row for each table in a database. This does include user tables and system tables that exist in each database. There is a column within sys.tables: [is_ms_shipped] that identifies system tables. This allows you to filter your results to just user tables if you so desire without having to join to the sys.sysobject system compatibility view’s type column (where sys.sysobjects.type = ‘U’) in order to do so.
sys.all_columns offers a row for each column for every object in a database. Many of the columns are shared with sys.types, and we pull most of the metadata from this view, but there is still one column lacking from our result set that we must pull from sys.types.
sys.types is the catalog view that stores rows relating to system and user-defined data types and their properties. The only field from this view we will need is the data type’s name, as it is the only field in our desired result set we can’t return from sys.all_columns as it pertains to column metadata.
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], AC.[name] AS [column_name],
TY.[name] AS system_data_type, AC.[max_length],
AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded]
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]
WHERE T.[is_ms_shipped] = 0
ORDER BY T.[name], AC.[column_id]