Menu
I am currently using...
select Table_Name, Column_name, data_type, is_Nullable from information_Schema.Columns ...to determine information about columns in a given database for the purposes of generating a DataAccess Layer.
From where can I retrieve information about if these columns are participants in the primary key of their table?
670
To identify a primary key for a table or file, you locate a column that's values uniquely identify the rows in the selected table. Additionally, you can verify that a primary key that is defined in the data source is the true primary key.
Columns that have a high percentage of uniqueness, as well as at least one foreign key candidate, make the best primary key candidates. Identify the columns that have the highest uniqueness percentage and assign one of the columns as the primary key. You might only have one primary key per table.
If we want to know the table's primary keys and foreign keys. We can simply use an “information_schema. key_column_usage” view, this view will return all of the table's foreign keys and primary keys.
Here is one way (replace 'keycol' with the column name you are searching for):
SELECT K.TABLE_NAME , K.COLUMN_NAME , K.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY' AND K.COLUMN_NAME = 'keycol'; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
