columns of the primary key of all tables of all schemas

Question

columns of the primary key of all tables of all schemas

aslhanhanc

SSC-Addicted

Points: 441
More actions
March 16, 2012 at 8:44 am

#270121

I want to list all columns of the primary key of all tables of all schema
Could you please help me?
Regards

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Start with Books Online, look at either the INFORMATION_SCHEMA views or the system views: sys.tables, sys.indexes, sys.index_columns.

After that, if you have questions come back and ask.

Lowell SSC Guru Points: 323518 More actions · Answer 2

start with sys.indexes, and you have to join sys.index_columns and sys.columns

select

object_name(IDX.object_id),

IDX.name,

cols.name,

* from sys.indexes IDX

INNER JOIN sys.index_columns IXCOLS

ON IDX.[object_id] = IXCOLS.[object_id]

AND IDX.[index_id] = IXCOLS.[index_id]

INNER JOIN sys.columns COLS

ON IXCOLS.[column_id] = COLS.[column_id]

AND IXCOLS.[object_id] = COLS.[object_id]

where IDX.is_primary_key = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

You mean something like this:

SELECT

OBJECT_NAME(I.object_id) AS table_name,

I.name AS index_name,

I.index_id,

I.type_desc AS index_type,

IC.key_ordinal,

IC.partition_ordinal,

IC.is_descending_key,

C.name AS column_name,

C.column_id,

C.max_length,

C.precision,

C.scale,

C.collation_name,

C.is_nullable,

C.is_ansi_padded,

C.is_identity,

C.is_computed

FROM

sys.indexes AS I

JOIN sys.index_columns AS IC

ON I.index_id = IC.index_id AND

I.object_id = IC.object_id

JOIN sys.columns AS C

ON IC.column_id = C.column_id AND

IC.object_id = C.object_id

WHERE

I.is_primary_key = 1

Edit: figured I'd be late.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

aslhanhanc SSC-Addicted Points: 441 More actions · Answer 4

Thank you very much

All the replies were so useful