Finding Duplicate Columns in set of Tables

• apat

  SSCertifiable

  Points: 5929

  More actions

  June 6, 2009 at 4:17 pm

  #103754

  Hi All,

  I need to find Same Column Names (duplicate columns) in a set of tables. ex. I have a database which contains 20 tables and some of those tables have same column names. I need to find those column names which exist in more than one table.

  Please help me with the query...

  Thanks much...

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 6, 2009 at 7:44 pm

  #1005763

  there's gotta be a better way, but this is all i could come up with...it works, but seems overly complicated..i need a nap or something:

  select object_name(syscolumns.id) as TableName,syscolumns.name As ColumnName ,MyAlias.TheCount

  from syscolumns

  inner join (

  select syscolumns.name ,count(*) AS TheCount from syscolumns

  inner join sysobjects on syscolumns.id=sysobjects.id

  where sysobjects.xtype='U'

  group by syscolumns.name

  having count(*) >1) MyAlias

  On syscolumns.name = MyAlias.name

  Order By MyAlias.TheCount DESC

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  June 7, 2009 at 9:31 am

  #1005830

  You can also use the INFORMATION_SCHEMA views:

  WITH cols AS

  (

  SELECT

  COLUMN_NAME,

  COUNT(*) COLUMN_COUNT

  FROM INFORMATION_SCHEMA.COLUMNS

  GROUP BY COLUMN_NAME

  HAVING COUNT(*) > 1

  )

  SELECT

  c.TABLE_SCHEMA,

  c.TABLE_NAME,

  cols.COLUMN_NAME,

  cols.COLUMN_COUNT

  FROM cols

  JOIN INFORMATION_SCHEMA.COLUMNS c ON cols.COLUMN_NAME = c.COLUMN_NAME

  ORDER BY cols.COLUMN_NAME

  Flo