473,889 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using SQL to find primary keys

What query do I use to list the primary key for each user table, i.e.

TABLE | PRIMARY_KEY |

Regards,
Alan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
4 42701
One method:

SELECT
TABLE_SCHEMA,
TABLE_NAME,
CONSTRAINT_NAME
FROM INFORMATION_SCH EMA.TABLE_CONST RAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
TABLE_SCHEMA,
TABLE_NAME

--
Hope this helps.

Dan Guzman
SQL Server MVP

"alan" <an*******@devd ex.com> wrote in message
news:40******** *************** @news.frii.net. ..
What query do I use to list the primary key for each user table, i.e.

TABLE | PRIMARY_KEY |

Regards,
Alan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #2
"alan" <an*******@devd ex.com> wrote in message news:40******** *************** @news.frii.net. ..
What query do I use to list the primary key for each user table, i.e.

TABLE | PRIMARY_KEY |

Regards,
Alan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


USE pubs
go

SELECT KU.*
FROM INFORMATION_SCH EMA.TABLE_CONST RAINTS AS TC
INNER JOIN
INFORMATION_SCH EMA.KEY_COLUMN_ USAGE AS KU
ON TC.CONSTRAINT_T YPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_N AME = KU.CONSTRAINT_N AME
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSI TION

CONSTRAINT_CATA LOG CONSTRAINT_SCHE MA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
COLUMN_NAME ORDINAL_POSITIO N
pubs dbo UPKCL_auidind pubs dbo authors au_id 1
pubs dbo PK_emp_id pubs dbo employee emp_id 1
pubs dbo PK__jobs__117F9 D94 pubs dbo jobs job_id 1
pubs dbo UPKCL_pubinfo pubs dbo pub_info pub_id 1
pubs dbo UPKCL_pubind pubs dbo publishers pub_id 1
pubs dbo UPKCL_sales pubs dbo sales stor_id 1
pubs dbo UPKCL_sales pubs dbo sales ord_num 2
pubs dbo UPKCL_sales pubs dbo sales title_id 3
pubs dbo UPK_storeid pubs dbo stores stor_id 1
pubs dbo UPKCL_taind pubs dbo titleauthor au_id 1
pubs dbo UPKCL_taind pubs dbo titleauthor title_id 2
pubs dbo UPKCL_titleidin d pubs dbo titles title_id 1

Regards,
jag
Jul 20 '05 #3
jag,

That's exactly what I was looking for.

Thanks.

Regards,
Alan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #4
Dan,

Your query produced results such as (Northwind):

Table | Key
Customers | PK_Customers
Employees | PK_Employees

What I wanted was:

Table | Key
Customers | CustomerID
Employees | EmployeeID

However, the info about INFORMATION_SCH EMA was gold dust. I didn't know
all these tables existed.

John Gilson's reply gave me the answer that I was looking for.

Thanks.

Regards,
Alan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
2962
by: Ilan Sebba | last post by:
I am trying to add a record using SQL. My problem is that the primary keys are foreign keys, and these foreign keys are autonumbers. I therefore do not know the primary keys of the record I am trying to insert. I therefore do not think that I can use the sql "Insert Into" command. Here is a simplified illustration of my tables: tblFather NaturalKey1 NatuarlKey2
7
5359
by: Philip | last post by:
Hey all, (Access 2000) I've been having a horror story with this design problem. My Database is Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each Item in each table needs a unique ID# based on its context. Primary Keys AUTHORS = AuthorID - NO Duplicates
7
2476
by: Dave | last post by:
Hi, Maybe I'm missing something with the DataKeyField attribute of a datagrid but it seems that it's somewhat limiting since this only allows you to specify one field as the key. I have a table that has two keys and it seems you need this value to find the corresponding record in the DataSet in order to update the data from the Datagrid.
3
2559
by: rnettle | last post by:
Can anyone tell me how to use the DataRowCollection.Find Method on 3 primary keys, 2 of the keys are of the String type and the other is of the DateTime type. I have tried the code below but get the following error message . An unhandled exception of type 'System.InvalidCastException' occurred in system.data.dll Additional information: Specified cast is not valid.
115
6303
by: LurfysMa | last post by:
Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? I have several tables that have unique fields. Can I use them as primary keys or should I define an autonum primary key? One table has information about the 50 states in the US. The table looks like this:
8
16614
by: Imicola | last post by:
Hi, I want to use primary key IDs in my tables which are autonumbers, with a text prefix. I.e. for table tblVillage, the ID's will by V1, V2, V3... and for table tblHumanCases, the ID's will be H1, H2, H3... I have a few questions: 1. I have heard its not good to do this. Is this true and if so why? 2. I am also having problems creating the relationships to related tables due to the field types. In the parent table the field type...
2
9170
by: Danny | last post by:
Hello, We imported a bunch of tables from a database and realized that the primary keys weren't copied to the destination db. In order to re- create the keys, we need to know which tables have them. Is there a command that I can use (on the source db) to find out which tables contain primary keys? The db has hundreds of tables and I'd rather not go through each one to see which has a primary key. Also, for future reference, is there a...
4
3839
by: Peter | last post by:
I am interested in informed feedback on the use of Constraints, Primary Keys and Unique. The following SQL statement creates a Bands tables for a database of bookings Bands into Venues, where the rule of the business is that only band plays on the one night. The SQL statement prevents a Band name being repeated (as it is Unique). Similar statement for the Venues. CREATE TABLE Bands (BandID varchar(5) CONSTRAINT BandID PRIMARY KEY, Band...
1
12159
by: =?Utf-8?B?Rm9lZg==?= | last post by:
I have a BindingSource with an underlying table with two primary keys. To set the position with one primary key works fine as shown in the code snippet below: Dim index As Integer = myBindingSource.Find("ColumnName", myColumnName) If (index <-1) Then myBindingSource.Position = index End If How should I set the BindingSource.Position with two or more primary keys?
0
9810
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11203
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10794
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10443
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7999
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7151
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4650
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3257
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.