Very Computer

Hello there,

Does any one know how I can retrieve FK and PK relationships through a
sql statement and not through the a diagram. I have got a sql statment
to retrieve the relationships already but it does not work where the
relationship is made up of multiple columns - it just works where they
are made up of single columns.

Any help would be great.

Thanks
Nicky

Nicky, you can modify this script for you
SELECT USER_NAME( OBJECTPROPERTY( i.id, 'OwnerID' ) )
                AS OwnerName,
     OBJECT_NAME( i.id ) AS TableName,
  i.name AS IndexName,
      CASE INDEXPROPERTY( i.id , i.name , 'IsClustered')
             WHEN 1 THEN 'YES'
             ELSE 'NO'
      END AS IsClustered,
      CASE INDEXPROPERTY( i.id , i.name , 'IsUnique'    )
         WHEN 1 THEN 'YES'
            ELSE 'NO'
      END AS IsUnique,
      CASE INDEXPROPERTY( i.id , i.name , 'IsForeign'    )
         WHEN 1 THEN 'YES'
            ELSE 'NO'
      END AS IsForeign,
      STATS_DATE( i.id , i.indid ) AS LastUpdatedDate
  FROM sysindexes AS i
WHERE OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0 And
      1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics'   ) ,
          INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,
          INDEXPROPERTY( i.id , i.name , 'IsHypothetical'   ) ) And
      i.indid BETWEEN 1 And 250
ORDER BY OwnerName, TableName, IndexName

Quote:> Hello there,

> Does any one know how I can retrieve FK and PK relationships through a
> sql statement and not through the a diagram. I have got a sql statment
> to retrieve the relationships already but it does not work where the
> relationship is made up of multiple columns - it just works where they
> are made up of single columns.

> Any help would be great.

> Thanks
> Nicky

Thanks for that Uri - but I already have a procedure that does a similar
sort of thing - what I am actualy after is a procedure that will return
all of the columns related to that key, as I stated in my mail I already
have a procedure that does what I have asked, but it only does it for
single column keys - if the key has multiple columns then it will not
work.

Any more ideas ??

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nicky,

Quote:> Thanks for that Uri - but I already have a procedure that does a
> similar sort of thing - what I am actualy after is a procedure that
> will return all of the columns related to that key, as I stated in my
> mail I already have a procedure that does what I have asked, but it
> only does it for single column keys - if the key has multiple columns
> then it will not work.

Look at sp_fkeys, information_schema.table_constraints,
information_schema.constraint_column_usage.

Linda

Thanks very much Linda

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!