How to pull out the name of the primary key column(s) of a MS SQL Server table?
I have a MS SQL Server Database with about 75 tables on it, and I am trying to insert records into tables if a record with the same primary key doesn't exist, or update if they do. I could hard code the primary keys for every table into my vb.net code, but I'd rather not as more tables are to be added at a later date and my code needs to handle this without being changed. Is there a way to pull o开发者_运维技巧ut the primary key column name(s) from a table using a query so I can loop through each table executing the same code?
Thanks, Tom
P.S. I am a bit of a newbie when it comes to SQL so if you have the time please be simple and clear with any responses.
Have a look at Find Tables With Primary Key Constraint in Database
SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i INNER JOIN
sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
There is a simpler way of doing this. It is called ADO.NET :)
Basically, you need a DataAdapter
to fetch the data from your DB to a DataSet
. You then add, modify or remove rows to a DataTable
in the DataSet
and when you're ready to commit to the DB you tell DataAdapter
to do so. The DataSet
can keep track of the changes made to it and the DataAdapter
knows what statement to use depending on the RowState
property of a row/record.
See here how to retrieve and modify data using ADO.NET.
精彩评论