none
Getting primary key info from SQL Server, c# RRS feed

  • Question

  • Hi,

    I am trying to identify primary keys on tables stored on SQL Server, from an app written in C#. I have tried several strategies, but I still cannot determine which fields are PKs on their respective tables.

    Initially:

    sqlCommand = SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = myTable;

    Then:

    SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.KeyInfo);
    DataTable schemaTable = myReader.GetSchemaTable();

    I can get useful info about  each of the columns, except for primary key info; schemaTable.PrimerayKey is always empty.

    Any ideas?

    Many thanks in advance,

    -- Edwin

    Monday, June 7, 2010 7:22 PM

Answers

  •  

    SELECT u.COLUMN_NAME, c.CONSTRAINT_NAME

     

    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN

     

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS u ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME

     

    where u.TABLE_NAME = 'MyTable' AND c.TABLE_NAME = 'MyTable' and c.CONSTRAINT_TYPE = 'PRIMARY KEY'


    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    Tuesday, June 8, 2010 6:46 PM

All replies

  • Try this query

    SELECT SysCol.is_identity, SchemCol.* 
    FROM INFORMATION_SCHEMA.COLUMNS SchemCol 
    	inner join sys.all_columns SysCol
    		on SchemCol.COLUMN_NAME = SysCol.name
    where SchemCol.TABLE_NAME = 'tablename'
    The is_identity column is a flag
    Tuesday, June 8, 2010 12:23 AM
  •  

    SELECT u.COLUMN_NAME, c.CONSTRAINT_NAME

     

    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN

     

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS u ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME

     

    where u.TABLE_NAME = 'MyTable' AND c.TABLE_NAME = 'MyTable' and c.CONSTRAINT_TYPE = 'PRIMARY KEY'


    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    Tuesday, June 8, 2010 6:46 PM
  • Edwin,

    Erik obviously knows his way around the SQL Server system tables and his query is probably the best one to use.  However, I wanted to chime in and let you know why your approach doesn't produce the results you'd expect.

    Calling DataReader.GetSchemaTable() is designed to return metadata about the query you're executing.  Including CommandBehavior.KeyInfo tells the query to include additional metadata (key information, base columns and tables) that's not typically included with the resultset.  Following your approach should return the key information if the Command's CommandText is a standard query like: "SELECT Field1, Field2, ... FROM MyTable".  If you're going to query the system tables directly for information about a particular table, there's no need to call GetSchemaTable.  You should see the information you're looking for in the results of the query via Command.ExecuteReader or DataAdapter.Fill.

    I hope this information proves helpful.


    David Sceppa
    Tuesday, June 8, 2010 10:49 PM
    Moderator