detecting similarity among tables attributes(columns)

Question

Post reply

detecting similarity among tables attributes(columns)

jamalziena

Grasshopper

Points: 15
More actions
March 29, 2011 at 12:45 pm

#235958

i have table(t1) with 20 attributes i want to compare it with other 10 tables to detect similar attributes between the table(t1) and other tables one by one and for each comparison process i need to know the name of similar attributes and the number of matches
thanks

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could start with the following query returning all tables and related columns in a database:

SELECT

t.name AS table_name,

c.name AS column_name

FROM sys.tables t

INNER JOIN sys.columns c

ON t.object_id = c.object_id

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

Using Lutz's query, I would probably through an order by c.name,t.name in there. You might also want to include the datatype and size as well..

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004666 More actions · Answer 3

jamalziena (3/29/2011)
i have table(t1) with 20 attributes i want to compare it with other 10 tables to detect [font="Arial Black"]similar attributes [/font]between the table(t1) and other tables one by one and for each comparison process i need to know[font="Arial Black"] the name of similar attributes [/font]and the number of matches
thanks

A quick question on the items highlighted in the quote above, please. Are you really looking for "similar" name which may be spelled differently or are you looking for "exact matches" for the column names?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jamalziena Grasshopper Points: 15 More actions · Answer 4

hi Lutz

thank you very much , about the the code :

SELECT

t.name AS table_name,

c.name AS column_name

FROM sys.tables t

INNER JOIN sys.columns c

ON t.object_id = c.object_id

this compare the t1 with first table so i have to repeat for the other 9 table how can i use a procedure to repeat the operation for the 10 table at the end the output display the all similar attribute

thank u alot

jamalziena Grasshopper Points: 15 More actions · Answer 5

jamalziena

Grasshopper

Points: 15

March 30, 2011 at 2:00 pm

#1305670

hi Jeff

yes i am looking for exact matches

SQLRNNR SSC Guru Points: 281344 More actions · Answer 6

jamalziena (3/30/2011)
hi Lutz
thank you very much , about the the code :
SELECT
t.name AS table_name,
c.name AS column_name
FROM sys.tables t
INNER JOIN sys.columns c
ON t.object_id = c.object_id
this compare the t1 with first table so i have to repeat for the other 9 table how can i use a procedure to repeat the operation for the 10 table at the end the output display the all similar attribute
thank u alot

The code as is will return all of the tables in the database. If you change it as I suggested, the results will sort by column name and then table name. This will make it much easier to spot the like tables.