none
Data type differences

    Question

  • I need the following in queries:
    • 1: precision and/or scale differences only (data type and data type length match)
    • 2: data type length differences only (precision, scale and data types match)
    • 3: data type differences only (only check data type differences)

    These can be different datasets


    • Edited by soldierfc Tuesday, May 06, 2014 5:52 PM
    Tuesday, May 06, 2014 5:51 PM

Answers

  • I show you the last query (from the top of my head) and you should be able to write the rest using the same idea:

    Select C.TABLE_SCHEMA, C.TABLE_NAME, C.Column_Name, C.Data_Type,

    DIf.Table_Schema as Different_Table_Schema, Dif.Table_Name as Different_Table_Name,

    Dif.Data_Type as Different_Data_Type

    FROM INFORMATION_SCHEMA.Columns C

    CROSS APPLY (SELECT * FROM INFORMATION_SCHEMA.Columns C1

    WHERE C1.Column_Name = C.Column_Name AND C.Data_TYPE < C1.Data_TYPE) Dif


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by soldierfc Tuesday, May 06, 2014 6:31 PM
    Tuesday, May 06, 2014 6:11 PM

All replies

  • Thanks in advance
    Tuesday, May 06, 2014 5:52 PM
  • You can use INFORMATION_SCHEMA.COLUMNS view for that.

    So is it between tables in two databases that you want this to be compared?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, May 06, 2014 5:54 PM
  • No just on one Database
    Tuesday, May 06, 2014 6:00 PM
  • I show you the last query (from the top of my head) and you should be able to write the rest using the same idea:

    Select C.TABLE_SCHEMA, C.TABLE_NAME, C.Column_Name, C.Data_Type,

    DIf.Table_Schema as Different_Table_Schema, Dif.Table_Name as Different_Table_Name,

    Dif.Data_Type as Different_Data_Type

    FROM INFORMATION_SCHEMA.Columns C

    CROSS APPLY (SELECT * FROM INFORMATION_SCHEMA.Columns C1

    WHERE C1.Column_Name = C.Column_Name AND C.Data_TYPE < C1.Data_TYPE) Dif


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by soldierfc Tuesday, May 06, 2014 6:31 PM
    Tuesday, May 06, 2014 6:11 PM