SQL Server – Runtime of Checking for Duplicate Primary Keys

You should become familiar with the new catalog views. The old sysobjects, syscolumns etc. have been deprecated and are only provided for backward compatibility reasons - they shouldn't be used for new development and old code should eventually transition to the new objects.

Here is a query that will get both sides of the columns involved in foreign keys:

SELECT 
        constraint_name   = OBJECT_NAME(fkc.constraint_object_id), 
        foreign_schema    = SCHEMA_NAME(child.[schema_id]), 
        foreign_table     = child.name, 
        foreign_column    = child_cols.name, 
        referenced_schema = SCHEMA_NAME(referenced.[schema_id]),
        referenced_table  = referenced.name, 
        referenced_column = referenced_cols.name
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.tables AS child
    ON fkc.parent_object_id = child.[object_id]
    INNER JOIN sys.tables AS referenced
    ON fkc.referenced_object_id = referenced.[object_id]
    INNER JOIN sys.columns AS referenced_cols
    ON fkc.parent_column_id = referenced_cols.column_id
    AND referenced_cols.[object_id] = referenced.[object_id]
    INNER JOIN sys.columns AS child_cols
    ON fkc.referenced_column_id = child_cols.column_id
    AND child_cols.[object_id] = child.[object_id]
    WHERE referenced.name = 'table name'
    -- if you want the tables that a child table references, use this instead:
    -- WHERE child.name = 'table name'
    ORDER BY fkc.parent_column_id;

And this one is much simpler, just for the tables involved:

SELECT 
    constraint_name = name, 
    foreign_schema = OBJECT_SCHEMA_NAME(parent_object_id),
    foreign_table = OBJECT_NAME(parent_object_id), 
    referenced_schema = OBJECT_SCHEMA_NAME(referenced_object_id),
    referenced_table = OBJECT_NAME(referenced_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'table name'
-- if you want the tables that a child table references, use this instead:
-- WHERE OBJECT_NAME(parent_object_id) = 'table name';

EDIT adding some links to documentation (these mostly fall within the SQL Server 2008 R2 hierarchy but they're roughly equivalent for SQL Server 2008 as well):

• Catalog Views
• Querying the Catalog Views
• Querying the Catalog Views FAQ
• Metadata Functions

Your points are unrelated to database design: choice of natural or surrogate key is an implementation decisions after conceptual and logical models are complete

In addition to comments and other answers:

• some natural keys work well such as currency or language codes (CHF, GBP, DE, EN etc)
• avoiding composite keys forces you to always join intermediate tables (rather than simple) parent-grandchild
• adding a surrogate key in unnecessary for link tables

Edit: example of "composite keys"

Assume: t1 has child t2 has child t3

• If you had the key of t1 in t3 (composite key) you can join t1 and t3 directly.
  t1 key is also the left hand column of t3 key so you don't need an extra index

• With a surrogate key/FK, you have to join via t2
  You need extra indexes on the FK columns in t2 and t3 which

This latter option with the "always use surrogate key" dogma

• adds complexity
• decreased or reverses disk space "savings"