CODEWITHSUNDEEP
sql
Gabri T
Gabri T

Reputation: 454

Find all tables in database By primaryKey

I'm searching for a query that can retrieve all the tables by the specified key/keys name on a specific database. Something like this:

SELECT table_name 
FROM   information_Schema.columns 
WHERE  column_name='ID_UNICO' 
AND    Constraint_Type = 'PRIMARY KEY'

I've been searching and the best I could find was:

SELECT Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY'
    AND Col.Table_Name = '<your table name>'

this retrieves the PK's of a specific table, I want to find table_name BY PK name.

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

also found this but this gives me the first column of the table, which doesn't ensure me that it it the PK.

Couldn't find any duplicate either.

Upvotes: 1

Views: 1360

Answers (3)

Jacob H
Jacob H

Reputation: 2505

Here you go, join TABLE_CONSTRAINTS to CONSTRAINT_COLUMN_USAGE to tie the constraint name to the column then return your TABLE_NAME:

SELECT CCU.COLUMN_NAME, 
       CONS.TABLE_NAME 
FROM   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU 
       JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS 
         ON CONS.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME 
WHERE  CONS.CONSTRAINT_TYPE = 'PRIMARY KEY' 
       AND CCU.COLUMN_NAME = 'ID_UNICO'

Upvotes: 1

Mehdi akbari
Mehdi akbari

Reputation: 124

You can use this query. Replace 'YOUR_COLUMN_NAME' with your primary key column name.

SELECT Table_Name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
and COLUMN_NAME ='YOUR_COLUMN_NAME'

Upvotes: 1

Karen Payne
Karen Payne

Reputation: 5102

Would this work for you?

DECLARE @ColName AS VARCHAR(MAX)
SET @ColName = 'CourseId'
SELECT
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
    AND COLUMN_NAME = @ColName

Upvotes: 2

Related Questions