Find Multi-Column Primary key

Question

I have about 30 tables from an old ERP which have multi-column primary keys. Unfortunately I don't know what those keys are. I've used the SSIS profiling task to determine primary key candidates for up to 5 columns, but it runs so slow as to be impractical. Is there any tool that would do this faster? My alternative is about 2 weeks of investigation using Excel and lots of select distinct queries.

Answer 1

As it turns out I was able to get primary keys for many tables by reverse-engineering with ER/Studio using an ODBC driver.

I have no idea how they manage to get the primary keys (the reports produced by the database don't show them), but for most table it seems like they're there.

Answer 2

The following should get you what you're looking for.

SELECT 
    *
FROM (
        SELECT 
            i.object_id,
            index_name = i.name,
            key_column_name = c.name,
            ic.key_ordinal,
            key_col_cnt = MAX(ic.key_ordinal) OVER (PARTITION BY ic.object_id)
        FROM
            sys.indexes i
            JOIN sys.index_columns ic
                ON i.object_id = ic.object_id
                AND i.index_id = ic.index_id
            JOIN sys.columns c
                ON ic.object_id = c.object_id
                AND ic.column_id = c.column_id
        WHERE 
            i.is_primary_key = 1
        ) ix
WHERE 
    ix.key_col_cnt > 1;