yeshupandit_2002 (10/25/2012)

---

Hi,

I havnt got your question fully but i assume that you want to find tables that "can have relation

base on the column name"

Like Parent table having USERID as primary key and some other table having USERID normal column

than it may have relation between them and you want those tables

if above which i assume is same then

this query might help you

i havent check it for 3000 table but for 10 it's working

;WITH CTE AS

(

select TAB.object_id,TAB.name,COL.name AS COLNAME,COL.column_id,COL.is_identity

from

sys.tables TAB

INNER JOIN sys.columns COL ON TAB.object_id=COL.object_id

)

SELECT

Child.object_id as 'Child Objectid'

,Child.name as 'Child TableName'

,Child.COLNAME as 'Child ColumnName'

,Parent.object_id as 'Parent Objectid'

,Parent.name as 'Parent TableName'

,Parent.COLNAME as 'Parent ColumnName'

FROM

cte Child

INNER JOIN CTE Parent

ON Child.COLNAME=Parent.COLNAME AND Child.name<>Parent.name AND Child.is_identity+1=Parent.is_identity