none
Get list of primary keys of certain data type? RRS feed

  • Question

  • Hi all I was wondering if it was possible to obtain a list of primary keys of a certain data type? I am basically developing an application that allows one to create a table but I want to simplify the process so when they declare a field and assign it a data type I have a drop down that populates possible candidates.

    I am currently doing a search for columns of the same data type but found that in order for one to create a foreign key the field must link to a primary key.

    select table_name,column_name,data_type  
    from information_schema.columns  
    where data_type = 'tinyint' 
    order by table_name 

    If I am wrong about the following then please correct me:
    - Foreign key must link to a primary key.
    - Must be of same data type.


    I am trying to do search:

    where data_type = 'tinyint' and constraint_type = 'primary key' 

    This possible to do in one query?

    Thanks everyone!

    Monday, March 9, 2009 4:28 PM

Answers

  • This should get you started
    DECLARE @datatype sysname  
        SET @datatype = 'int' 
     
    SELECT c.name As column_name  
         , i.*  
    FROM   sys.columns As c  
     INNER 
      JOIN sys.indexes As i  
        ON c.object_id = i.object_id  
     INNER 
      JOIN sys.types As t  
        ON t.system_type_id = c.system_type_id  
    WHERE  t.name = @datatype 

    By the by, you are very brave to let your users create tables 8-)
    George
    • Marked as answer by OnamC Tuesday, March 10, 2009 10:36 AM
    Monday, March 9, 2009 5:01 PM
    Answerer

All replies

  • This should get you started
    DECLARE @datatype sysname  
        SET @datatype = 'int' 
     
    SELECT c.name As column_name  
         , i.*  
    FROM   sys.columns As c  
     INNER 
      JOIN sys.indexes As i  
        ON c.object_id = i.object_id  
     INNER 
      JOIN sys.types As t  
        ON t.system_type_id = c.system_type_id  
    WHERE  t.name = @datatype 

    By the by, you are very brave to let your users create tables 8-)
    George
    • Marked as answer by OnamC Tuesday, March 10, 2009 10:36 AM
    Monday, March 9, 2009 5:01 PM
    Answerer
  • Only the development team is going to be using this table :) so it should "in theory" be safe. I am looking at possible issues that may occur and rectifying them like this little problem right now :) access to this application is quite strict so should be ok hopefully :)

    Thanks for your command will need to try it when back @ work :)
    Monday, March 9, 2009 5:08 PM
  • OnamC said:

    Only the development team is going to be using this table :) so it should "in theory" be safe. I am looking at possible issues that may occur and rectifying them like this little problem right now :) access to this application is quite strict so should be ok hopefully :)

    Thanks for your command will need to try it when back @ work :)


    A primary key can consist of more than one column, so which data type would be correct?
    Monday, March 9, 2009 7:45 PM
    Moderator
  • The data types that are going to be primary keys would be a BIGINT as I'm using an incremented ID (clustered index) approach :)
    Tuesday, March 10, 2009 8:51 AM
  • Did a slight modification to the SQL gvee but it was on the right track. I came up with this:

    select max(column_name) as column_name,table_name,data_type,is_primary_key,is_identity 
    from information_schema.columns as info_schema_cols 
    inner join sys.columns as sys_cols on info_schema_cols.column_name = sys_cols.name 
    inner join sys.indexes as sys_indx on sys_cols.object_id = sys_indx.object_id   
    where is_primary_key = 1 and is_identity = 1 and data_type = 'int' 
    group by table_name,data_type,is_primary_key,is_identity 
    order by column_name 

    Might prove useful to someone else perhaps...

    Thanks for the suggestions!
    Tuesday, March 10, 2009 10:35 AM
  • OnamC said:

    The data types that are going to be primary keys would be a BIGINT as I'm using an incremented ID (clustered index) approach :)


    Every table has a surrogate key, regardless of whether a natural key exists..?
    If you're going to implement this, please tell me you're at least putting a unique constraint on the natural keys!
    George
    Wednesday, March 11, 2009 12:17 PM
    Answerer