A bit late but I made the following updates which were useful to me -- flag table versus view and identify if a field is an identity field. Maybe someone else will as well...

/*----------------------------------------------------------------------

Purpose: Identify columns having different datatypes, for the same column name.

Sorted by the prevalence of the mismatched column.

------------------------------------------------------------------------

Revision History:

06/01/2008 Ian_Stirk@yahoo.com Initial version.

03/11/2014Martin McDonald -- Modified to add is_table and is_identity information

-----------------------------------------------------------------------*/

-- Do not lock anything, and do not get held up by any locks.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Calculate prevalence of column name

SELECT

COLUMN_NAME

,[%] = CONVERT(DECIMAL(12,2),COUNT(COLUMN_NAME)* 100.0 / COUNT(*)OVER())

INTO #Prevalence

FROM INFORMATION_SCHEMA.COLUMNS

GROUP BY COLUMN_NAME

-- Do the columns differ on datatype across the schemas and tables?

SELECT DISTINCT

C1.COLUMN_NAME

, C1.TABLE_SCHEMA

, C1.TABLE_NAME

, case

when t.name is null then 'View'

else 'Table'

end as is_table

, c.is_identity

, C1.DATA_TYPE

, C1.CHARACTER_MAXIMUM_LENGTH

, C1.NUMERIC_PRECISION

, C1.NUMERIC_SCALE

, [%]

FROM INFORMATION_SCHEMA.COLUMNS C1

INNER JOIN INFORMATION_SCHEMA.COLUMNS C2 ON C1.COLUMN_NAME = C2.COLUMN_NAME

INNER JOIN #Prevalence p ON p.COLUMN_NAME = C1.COLUMN_NAME

left outer join sys.tables t on c1.TABLE_NAME = t.name

left outer join sys.columns c on t.object_id = c.object_id

and c1.COLUMN_NAME = c.name

WHERE ((C1.DATA_TYPE != C2.DATA_TYPE)

OR (C1.CHARACTER_MAXIMUM_LENGTH != C2.CHARACTER_MAXIMUM_LENGTH)

OR (C1.NUMERIC_PRECISION != C2.NUMERIC_PRECISION)

OR (C1.NUMERIC_SCALE != C2.NUMERIC_SCALE))

ORDER BY [%] DESC, C1.COLUMN_NAME, C1.TABLE_SCHEMA, C1.TABLE_NAME

-- Tidy up.

DROP TABLE #Prevalence