Ian,

I only wanted to see the column names that appear more than once, so that I exclude all the columns that are ok.

So I re-wrote your query to:

-- From http://www.sqlservercentral.com/articles/Admin/65138/

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

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.

2009-01-28 hsp@stovi.com; only show the column names that appear more than once

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

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRY

DROP TABLE #Prevalence

END TRY

BEGIN CATCH

END CATCH

BEGIN TRY

DROP TABLE #FieldList

END TRY

BEGIN CATCH

END CATCH

-- 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 AS TableName

, C1.DATA_TYPE

, C1.CHARACTER_MAXIMUM_LENGTH

, C1.NUMERIC_PRECISION

, C1.NUMERIC_SCALE

, [%]

INTO #FieldList

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

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

--SELECT * FROM #fieldList ORDER BY column_name

;

WITH cte (column_name, Data_type, Character_maximum_length, Numeric_precision, Numeric_Scale, UsedInTables, rownum) as

(

SELECT column_name

, MAX(data_type) AS Data_type, MAX(Character_maximum_length) AS Character_maximum_length, MAX(Numeric_precision) AS Numeric_precision

, MAX(Numeric_Scale) AS Numeric_Scale

, (SELECT TableName + ', ' FROM #fieldList F1

WHERE f1.Column_name= F.Column_name

AND (f1.Data_type = F.Data_Type OR F.Data_Type IS NULL)

AND (f1.Character_maximum_length = f.Character_maximum_length OR f.Character_maximum_length IS NULL)

AND (f1.Numeric_precision = f.Numeric_precision OR f.Numeric_precision IS NULL)

AND (f1.Numeric_Scale = f.Numeric_Scale OR f.Numeric_Scale IS NULL)

for xml path('')) AS UsedInTables

, ROW_NUMBER() OVER ( PARTITION BY column_name ORDER BY data_type, Character_maximum_length) AS rownum

FROM #fieldList F

WHERE

f.tablename NOT LIKE 'tmp%'

GROUP BY column_name, Data_type, Character_maximum_length, Numeric_precision, Numeric_Scale

)

SELECT c1.* FROM cte c1

INNER JOIN cte c2 ON c1.column_name = c2.Column_name

WHERE c2.rownum=2

Thank you very much for your initial script.

Best regards,

Henrik Staun Poulsen

Stovi Software