How to find table name using primary key name in sql?

how to check which column is primary key in sql
how to find primary key of a table in database
how to find primary key of a table in sql server
sql check primary key constraint
sql command get primary key
sql select based on primary key
get primary key value sql
sql server query for primary keys

I have names of primary keys in a variable and I need to find the table to which they belong. The db has many table so linear search is not an option.


you can try this out ::

SELECT table_name 
FROM information_Schema.columns 
WHERE column_name='dept_id' 
  and ordinal_position = 1;

How to find table name using primary key name in sql?, See also: tables without primary keys. Query. select schema_name(tab.​schema_id) as [schema_name], tab.[name] as  Query below lists tables and their primary key (PK) constraint names.By browsing list you can spot which tables have and which don't have primary keys. See also: tables without primary keys.


You can use the information_schema tables. If the primary key name is the first column in the table, you can just do:

select table_name
from information_schema.columns
where column_name in (<your list here>) and
      ordinal_position = 1;

Otherwise, you have to go through the constraints to get what you want. Something like:

select kcu.table_name, kcu.column_name
from information_schema.table_constraints tc join
     information_schema.key_column_usage kcu
     on tc.contraint_name = kcu.contraint_name and
        tc.table_name = kcu.table_name
where tc.contraint_type = 'PRIMARY KEY' and
      column_name in (<your list here>);

You can also do this using the system tables and views.

List tables with their primary keys (PKs) in SQL Server database , Query below lists all primary keys constraints (PK) in the database with their columns (one row per column). See also: list of  Here we have declared Primary key on PersonID and Name columns on the Persons_Data Table. NOTE that a “Constraint allows us to define the limit data type when we create or alter the table queries in SQL.” How to use a primary key in SQL with Row, column, table, etc.? We use a primary key in SQL database queries as follows:


This should work, please try it:

SELECT table_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
AND column_name in (select column_name from <your list here>)

Below solution wouldn't work if the primary key constraint is set to any column other than that of the first column in `CREATE TABLE' query.

select table_name
from information_schema.columns
where column_name in (select column_name from <your list here>) and
ordinal_position = 1

For example, it wouldn't work if we create table like this:

create table sample1
(
 field1 int,
 field2 int primary key
)

Please correct me if I am wrong.

List all primary keys (PKs) and their columns in SQL Server , SELECT SCHEMA_NAME(schema_id) AS [Schema], name AS [Table] TABLE_CONSTRAINTS view to get a list of tables with primary keys. So I need to put the primary key column name in a variable. The best way I found so far is this : declare @keyname nvarchar(1024) create table #keys (table_qualifier nvarchar(255),table_owner nvarchar(255),table_name nvarchar(255),column_name nvarchar(255),key_seq integer,pk_name nvarchar(255)) insert #keys exec sp_pkeys @table_name = @tablename


The below query gives tablename based on constraint name

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_NAME ='<ConstraintName>'

7 Ways to Return All Tables with a Primary Key in SQL Server , Try using the code given below: SELECT Col. AND Constraint_Type = '​PRIMARY KEY'. AND Col.Table_Name = '<your table name>'  First, you can define a table without a primary key using the CREATE TABLE statement though it is not a good practice. Then, you add the primary key to the table using the ALTER TABLE statement. For example, the following statement creates the project_milestones table without the primary key. The project_milesones stores milestones of projects.


To find table name using primary key name follow this below query

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
CONSTRAINT_NAME =<ConstraintName>

---Find the below views to get constraint details in a db select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS select * from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS select * from
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

How do you list the primary key of a SQL Server table?, How do I determine if a table has a primary key and if it has one, how do I determine You can learn more about how we use advertisements here. If you knew the table name that you were looking for, you could modify the SQL as follows: Here is a query that will list all Primary Key columns from a SQL Server table (enter the schema and table name in the WHERE statement - in this case we want to find Primary Key columns from Person.BusinessEntityAddress table): SELECT DB_NAME() AS Database_Name,sc.name AS 'Schema_Name',o.Name AS 'Table_Name',i.Name AS 'Index_Name'


Oracle / PLSQL: Retrieve primary key information, Table-level constraints specify the names of the columns to which they apply. Note: If you attempt to add a primary key using ALTER TABLE and any of the See "Using SQL standard authorization" and "Privileges on views, triggers, and  Another easiest method to find the tables by the table’s name in SQL Server database is to use the filter settings option in the object explorer in SQL Server Management Studio. In the Object Explorer in SQL Server Management Studio, go to the database and expand it. Right Click the Tables folder and select Filter in the right-click menu.


CONSTRAINT clause, C (check constraint on a table). P (primary key). U (unique key) TABLE_NAME, VARCHAR2(30), NOT NULL, Name associated with the table (or view) with constraint definition See Also: the constraints in Oracle Database SQL Reference. Frequently I have come across situations to list down all the tables referencing a specific table using foreign key. There are several methods to find the foreign keys referencing a table. Here I will go over the different methods to find the foreign keys references to a specific table. Using sp_fkey


ALL_CONSTRAINTS, This tutorial shows you how to use SQLite PRIMARY KEY constraint to define the In SQL standard, the primary key column must not contain NULL values. CREATE TABLE countries ( country_id INTEGER PRIMARY KEY, name TEXT NOT  SQL PRIMARY KEY Constraint. The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).