Tags

, ,


This query will return all of the primary keys, including those with multiple columns (and will return those in the order they are listed in the key definition): 

SELECT  
    T.TABLE_NAME,  
    T.CONSTRAINT_NAME,  
    K.COLUMN_NAME,  
    K.ORDINAL_POSITION  
FROM  
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE K 
    ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME  
WHERE 
    T.CONSTRAINT_TYPE = ‘PRIMARY KEY’  
    — AND T.TABLE_NAME = ‘table_name’ 
ORDER BY 
    T.TABLE_NAME, 
    K.ORDINAL_POSITION

 
You can also use the following methods: 
 

EXEC sp_pkeys ‘<tablename>’ 
EXEC sp_helpconstraint ‘<tablename>’ 

 
sp_pkeys will return a row for each column that participates in the primary key for <tablename>. The columns you are likely most interested in are COLUMN_NAME and PK_NAME. 
 
sp_helpconstraint will list all constraints for <tablename>, including foreign keys that reference the table. In the first recordset, there will only be a column called Object Name (kind of useless, since that’s what you passed in). In the second resultset, there will be the following columns: constraint_type, constraint_name, and constraint_keys. 
 
Microsoft Access 
 
This code uses ADOX to iterate through all the primary keys: 
 

<%  
    Set conn = CreateObject(“ADODB.Connection”)  
    Set cat = CreateObject(“ADOX.Catalog”)  
    conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _  
        “Data Source=<path to db>” 
    Set cat.ActiveConnection = conn  
 
    Response.Write “<table border=1><tr>” & _ 
        “<th>Table/Column</th>” & _ 
        “<th>Key Name</th></tr>” 
 
    For Each tbl in cat.Tables  
        if left(tbl.Name, 4) <> “MSys” then 
            For Each key in tbl.Keys  
                If key.Type = 1 Then 
                    For Each col in key.Columns  
                        Response.Write “<tr><td>” & tbl.Name & “.” & _  
                            col.Name & “</td><td>” & _  
                            key.Name & “</td></tr>”  
                    Next  
                End If  
            Next  
        End If 
    Next  
 
    Response.Write “</table>” 
 
    Set cat = Nothing  
    conn.Close : Set conn = Nothing  
%>
Advertisements