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