After testing out the different versions again on some of my databases, I noticed the same discrepancy in the number of records returned between Abdullah Khan's query and Jorge Serres' (and everybody else's) queries. The queries are missing any tables that do not have a defined primary key. If you comment out the next to last line, it shows all the tables.

Here's the query with the commented line:

SELECT DISTINCT

t.name AS Table_Name, sc.name as [schema],

ept.value AS Table_Desc,

c.name AS Column_Name,

st.name + '(' + CASE WHEN c.max_length = - 1 THEN 'max' ELSE CAST(c.max_length AS varchar(100)) END + ')' AS Column_Data_Type,

c.column_id,

CASE WHEN c.is_nullable = 0 THEN 'False' ELSE 'True' END AS Null_Allowed_Ind,

epc.value AS Column_Desc,

CASE WHEN dc.definition LIKE '(getdate())' THEN 'Current Date' ELSE dc.definition END AS Column_Default_Value,

CASE WHEN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.TABLE_NAME = b.TABLE_NAME AND b.constraint_type = 'PRIMARY KEY' AND a.TABLE_NAME = t.name AND a.COLUMN_NAME = c.name) = 1 THEN 'Yes' ELSE 'No' END AS Primary_Key_Ind,

CASE WHEN t .object_id = fk.parent_object_id AND

c.column_id = fk.parent_column_id THEN 'Yes' ELSE '' END AS Foriegn_Key_Ind,

CASE WHEN c.is_identity = 1 THEN 'Yes' ELSE '' END AS Identity_Column_Ind,

ft.name AS Foreign_Table,

(SELECT TOP 1 a.constraint_name

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.TABLE_NAME = b.TABLE_NAME

AND b.constraint_type = 'PRIMARY KEY'

AND a.COLUMN_NAME = c.NAME

AND a.TABLE_NAME = object_name(c.object_id) AND a.ordinal_position = c.column_id) AS Primary_Key_Name,

object_name(fk.constraint_object_id) AS Foreign_Key_Name

FROM sys.columns AS c INNER JOIN

sys.systypes AS st ON st.xtype = c.user_type_id LEFT OUTER JOIN

sys.extended_properties AS epc ON epc.major_id = c.object_id AND epc.minor_id = c.column_id LEFT OUTER JOIN

sys.default_constraints AS dc ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id INNER JOIN

sys.tables AS t ON c.object_id = t.object_id LEFT OUTER JOIN

sys.extended_properties AS ept ON ept.major_id = t.object_id AND ept.minor_id = t.parent_object_id LEFT OUTER JOIN

sys.key_constraints AS pk ON t.object_id = pk.parent_object_id LEFT OUTER JOIN

sys.foreign_key_columns AS fk ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id LEFT OUTER JOIN

sys.tables AS ft ON fk.referenced_object_id = ft.object_id

INNER join sys.schemas sc on t.schema_id = sc.schema_id --

WHERE (t.name NOT IN ('sysdiagrams', 'DataDictionary')) AND (st.name NOT LIKE '%sysname%')

--AND (select count(*) from sys.key_constraints where type_desc = 'PRIMARY_KEY_CONSTRAINT' and parent_object_id = t.object_id) = 1

ORDER BY Table_Name, c.column_id;