compare one Master table one column rows with other table column names

• Sangeeth Raj

  Hall of Fame

  Points: 3541

  More actions

  June 8, 2016 at 5:01 am

  #314370

  Hi,

  I have come across the requirement to check whether the column name of 20 tables are same or not with one master table rows. This master table got all column names and tables names. I need to compare these rows names are matching or not with actual table column names.

  Eg

  Master Table

  TableName | Column Names

  EmployeDetails Name

  EmployeDetails ID

  EmployeDetails salary

  Sales Invoice

  Sales Tax

  Sales Price

  Table1 : EmployeeDetails

  Columns: Name,ID,salary

  Table2: Sales

  Columns: Invoice,Tax,Price.

  Now I need to compare rows of mastertable with columnnames of tabtle1 & table2, whether they spell right or wrong, how many columns are missing from master table.

  Many Thanks in Advance

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  June 8, 2016 at 5:42 am

  #1883838

  You should start with code like this

  select SCHEMA_NAME(t.schema_id) [schema_name], t.name [table_name], c.name [column_name]

  from sys.tables t

  inner join sys.columns c on t.object_id = c.object_id

  -- where t.is_ms_shipped = 0 -- user created tables

  to find all tables and their columns of a database and then make a comparison with the master table data.

  Igor Micev,
  My blog: www.igormicev.com