List of Tables with Columns, Data Types and Primary Key and Identity information

SELECT
	SCHEMA_NAME([t].[schema_id]) [SchemaName], 
	[t].[Name] [TableName], 
	[c].[column_id] [ColumnId], 
	[c].[name] [ColumnName], 
	[tp].[name] [TypeName], 
	[c].[max_length] [MaxLength], 
	[c].[Precision], 
	[c].[Scale], 
	(SELECT COUNT(*) 
	 FROM [sys].[indexes] AS [i] 
		INNER JOIN [sys].[index_columns] AS [ic] ON [i].[OBJECT_ID] = [ic].[OBJECT_ID] AND [i].[index_id] = [ic].[index_id]
	 WHERE [i].[is_primary_key] = 1 AND [i].[object_id] = [t].[object_id] AND [ic].[column_id] = [c].[column_id]) [IsPK],
	[c].[is_identity] [IsIdentity]
FROM [sys].[tables] [t]
	INNER JOIN [sys].[columns] [c] ON [t].[object_id] = [c].[object_id]
	INNER JOIN [sys]. [tp] ON [tp].[system_type_id] = [c].[system_type_id] AND [c].[user_type_id] = [tp].[user_type_id]
ORDER BY [t].[name], [c].[column_id]