find primary and foreign keys

Hot Topic

Transactions
Dynamically Create Columns
Problem with Incrementing variable
is this possible in SSIS?
view history on maintence plan
JDBC Driver 2005 exception stack trace
Deployed files reduced size
error on server
Is IIS always required for replication?
Conditional MDX ?

SQL Server

Problem of merged cells in excel format after exporting from SQL Reporting Services 2000
Error in creating a simple asp.net app similar to sample.net (as2000 samples)
I need to compare two string values and give confidence score on how much they are similar.How do we use Fuzzy look up to do it?
again a problem with xml datasource extension
question on using xp_cmdshell
A Problems after the install of the Hotfix
Still a problem with Report Refresh Icon
SQL server 2005 Beta 2 Uninstall Hell
Changing MOLAP dimensions to ROLAP
Creating an update trigger for a multi-table view

find primary and foreign keys

 

I have a DB with 100 tables. I was wondering if anybody knows a quick way to list primary and foreign key with the column name for all the tables.

Your help would make my life a lot easier

 

thanks

Answer this question

find primary and foreign keys

  • prash_ac

    Hi,

    The below will list all PK's:

    SELECT o.name AS 'TableName',
     c.name AS 'PKColumnName'
     FROM sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk
     WHERE o.type in ('U')
     AND x.id = o.id
     AND o.id = c.id
     AND o.id = xk.id
     AND x.indid = xk.indid
     AND c.colid = xk.colid
     AND xk.keyno <= x.keycnt
     AND     (x.status&32) = 0
     AND convert(bit,(x.status & 0x800)/0x800) = 1
    ORDER BY o.name, c.name

    Cheers

    Rob

  • pv

    Please use the INFORMATION_SCHEMA views instead of accesing system tables or using undocumented columns. The view INFORMATION_SCHEMA.KEY_COLUMN_USAGE will give the information you are looking for.

  • find primary and foreign keys
Answer this question