What are all the keys used in a database

Summary

How to get the list of all keys in a database with MS SQL server 2005? >>tip<

• You cannot view all the keys in a database when using Visual Studio, you must do it one table at a time.

All the keys

use AdventureWorksLT;
go

select schm.name as 'Schema', tbl.name as 'Table'
     , keys.name as 'Constraint', keys.type_desc as 'Type'
     , cols.name as 'Column'
  from sys.key_constraints as keys
  join sys.tables as tbl
    on tbl.object_id = keys.parent_object_id
  join sys.schemas as schm
    on schm.schema_id = tbl.schema_id
  join sys.index_columns as idxcols
    on idxcols.object_id = tbl.object_id
   and idxcols.index_id = keys.unique_index_id
  join sys.columns as cols
    on cols.object_id = tbl.object_id
   and cols.column_id = idxcols.column_id
 order by 1,2,3,4;
go

Schema     Table                             Constraint                                                                     Type                     Column
---------- --------------------------------- ------------------------------------------------------------------------------ ------------------------ --------------------
dbo        ErrorLog                          PK_ErrorLog_ErrorLogID                                                         PRIMARY_KEY_CONSTRAINT   ErrorLogID
SalesLT    Address                           AK_Address_rowguid                                                             UNIQUE_CONSTRAINT        rowguid
SalesLT    Address                           PK_Address_AddressID                                                           PRIMARY_KEY_CONSTRAINT   AddressID
SalesLT    Customer                          AK_Customer_rowguid                                                            UNIQUE_CONSTRAINT        rowguid
SalesLT    Customer                          PK_Customer_CustomerID                                                         PRIMARY_KEY_CONSTRAINT   CustomerID
SalesLT    CustomerAddress                   AK_CustomerAddress_rowguid                                                     UNIQUE_CONSTRAINT        rowguid
SalesLT    CustomerAddress                   PK_CustomerAddress_CustomerID_AddressID                                        PRIMARY_KEY_CONSTRAINT   CustomerID
SalesLT    CustomerAddress                   PK_CustomerAddress_CustomerID_AddressID                                        PRIMARY_KEY_CONSTRAINT   AddressID
SalesLT    Product                           AK_Product_Name                                                                UNIQUE_CONSTRAINT        Name
SalesLT    Product                           AK_Product_ProductNumber                                                       UNIQUE_CONSTRAINT        ProductNumber
SalesLT    Product                           AK_Product_rowguid                                                             UNIQUE_CONSTRAINT        rowguid
SalesLT    Product                           PK_Product_ProductID                                                           PRIMARY_KEY_CONSTRAINT   ProductID
SalesLT    ProductCategory                   AK_ProductCategory_Name                                                        UNIQUE_CONSTRAINT        Name
SalesLT    ProductCategory                   AK_ProductCategory_rowguid                                                     UNIQUE_CONSTRAINT        rowguid
SalesLT    ProductCategory                   PK_ProductCategory_ProductCategoryID                                           PRIMARY_KEY_CONSTRAINT   ProductCategoryID
SalesLT    ProductDescription                AK_ProductDescription_rowguid                                                  UNIQUE_CONSTRAINT        rowguid
SalesLT    ProductDescription                PK_ProductDescription_ProductDescriptionID                                     PRIMARY_KEY_CONSTRAINT   ProductDescriptionID
SalesLT    ProductModel                      AK_ProductModel_Name                                                           UNIQUE_CONSTRAINT        Name
SalesLT    ProductModel                      AK_ProductModel_rowguid                                                        UNIQUE_CONSTRAINT        rowguid
SalesLT    ProductModel                      PK_ProductModel_ProductModelID                                                 PRIMARY_KEY_CONSTRAINT   ProductModelID
SalesLT    ProductModelProductDescription    AK_ProductModelProductDescription_rowguid                                      UNIQUE_CONSTRAINT        rowguid
SalesLT    ProductModelProductDescription    PK_ProductModelProductDescription_ProductModelID_ProductDescriptionID_Culture  PRIMARY_KEY_CONSTRAINT   ProductModelID
SalesLT    ProductModelProductDescription    PK_ProductModelProductDescription_ProductModelID_ProductDescriptionID_Culture  PRIMARY_KEY_CONSTRAINT   ProductDescriptionID
SalesLT    ProductModelProductDescription    PK_ProductModelProductDescription_ProductModelID_ProductDescriptionID_Culture  PRIMARY_KEY_CONSTRAINT   Culture
SalesLT    SalesOrderDetail                  AK_SalesOrderDetail_rowguid                                                    UNIQUE_CONSTRAINT        rowguid
SalesLT    SalesOrderDetail                  PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID                            PRIMARY_KEY_CONSTRAINT   SalesOrderID
SalesLT    SalesOrderDetail                  PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID                            PRIMARY_KEY_CONSTRAINT   SalesOrderDetailID
SalesLT    SalesOrderHeader                  AK_SalesOrderHeader_rowguid                                                    UNIQUE_CONSTRAINT        rowguid
SalesLT    SalesOrderHeader                  AK_SalesOrderHeader_SalesOrderNumber                                           UNIQUE_CONSTRAINT        SalesOrderNumber
SalesLT    SalesOrderHeader                  PK_SalesOrderHeader_SalesOrderID                                               PRIMARY_KEY_CONSTRAINT   SalesOrderID

(30 row(s) affected)