Thanks, Sergey. You have answered so many of my SQL questions over the last couple years. Maybe I'll get to provide you with some useful information someday.

Between the three answers I've gotten from Keith, you, and Bonnie, I will have to think about a convenient way to integrate "primary key discovery" into my stored procedure standards and my data accessor class in .NET.

I'd like my code design to parallel my own work habits, a kind of lazy instantiation, only incurring the resource cost when I will benefit from it. I wouldn't want to bother with getting PK information and assigning them to my ADO.NET DataTables when I don't need them, but I would like to automate their creation.

It seems to me like the time to consider this is when I write the PK, because they tend to be written with a specific application in mind. I could also add a bool parameter that opted whether to pull PK information or not, so that when the PK was called from code, I could select to pull PK information, returned in a table that always had the same name, then I could consume the PK data table in the result set to assign PKs when the stored procedure returned, then remove it.

That's my current idea, anyway. This scheme would probably only be worth coding for a system that pulled 1000s of records and wanted to randomly select PKs from them. For now, it is kind of academic, because my current system never pulls more than 500 records.

What is more fun than a framework in search of an application?

Thanks again.

>You can use INFORMATION_SCHEMA

SELECT * FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
>	JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
>		ON tc.CONSTRAINT_name = ccu.CONSTRAINT_name
>	WHERE CONSTRAINT_type = 'PRIMARY KEY'
>		AND table_name = 'MyTable'
>

>
>>I know how to set a primary key when creating a table in SQL or by using the graphic interface of Enterprise Manager, but once a table has been created and I know its name, is there a way to determine if any of its columns are primary keys? I don't understand why ADO.NET, when you do some kind of SQL pass-through along the lines of
>>
>>SELECT * FROM TableName
>>
>>never captures the primary key information. Selecting all columns obviously includes any primary keys that are set.
>>
>>Is it just that it is possible to do, but Microsoft is leaving it up to developers because some may not need primary keys in their result sets and not want to incur the additional overhead of indexing the tables in their result sets?
>>
>>Even the results of a query that joins multiple tables could set primary keys, when applicable, if my sense of set theory is correct. If table1 has pk1 and table2 has pk2, a join of the two tables should have a pk3 that is defined by a unique pairing of pk1 + pk2. I am assuming that the uniqueness of primary keys is transitive, to use a mathematical term.
>>
>>I suppose that it is possible to write stored procedures that break primary key uniqueness and it would be a lot of overhead, if possible at all, for ADO.NET to evaluate a stored procedure or SQL query and decide if someone had broken PK uniqueness.
>>
>>For the database design that I am working with, we always have single column primary keys on every table, don't rename columns with AS, and all tables in my result sets that I get have primary keys that I can determine.
>>
>>Can someone tell me the SQL code to ask if a given table has primary key or identifier columns set?