November 24, 2005 at 4:14 am
Hi!
I wondered how to do a query that lists all the primary keys (and its data type) from the tables that have more than one field as primary key
tkxx!!!
November 24, 2005 at 5:01 am
so far, I'v done this. Shows all tables PKs, but doesn't display column data type
SELECT TOP 100 PERCENT
T.FIELDS_PER_PK,
T.TABLE_NAME,
T.PK_NAME,
K.COLUMN_NAME,
K.ORDINAL_POSITION
FROM
(
select top 100 percent t.table_name, K.CONSTRAINT_NAME as PK_NAME, count(k.constraint_name) FIELDS_PER_PK
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME
where t.constraint_type='PRIMARY KEY'
group by t.table_name, k.constraint_name
) as T
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
ON T.PK_NAME = K.CONSTRAINT_NAME
ORDER BY
FIELDS_PER_PK desc,
T.TABLE_NAME,
K.ORDINAL_POSITION
November 24, 2005 at 5:24 am
Here's the full answer, I think - again using the INFORMATION_SCHEMA. I've built it up a bit at a time (using table variables) so it's easier to understand.
--Get all Primary Key Constraints
declare @PrimaryKeyConstraints table (TABLE_NAME sysname, CONSTRAINT_NAME sysname)
insert into @PrimaryKeyConstraints
select TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'PRIMARY KEY'
--select * from @PrimaryKeyConstraints
--Get all Composite Primary Key Constraints
declare @CompositePrimaryKeyConstraints table (TABLE_NAME sysname, CONSTRAINT_NAME sysname)
insert into @CompositePrimaryKeyConstraints
select TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where CONSTRAINT_NAME in (select CONSTRAINT_NAME from @PrimaryKeyConstraints) and ORDINAL_POSITION = 2
--select * from @CompositePrimaryKeyConstraints
--Get Composite Primary Key Columns
declare @CompositePrimaryKeyColumns table (TABLE_NAME sysname, CONSTRAINT_NAME sysname, COLUMN_NAME sysname)
insert into @CompositePrimaryKeyColumns
select TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where CONSTRAINT_NAME in (select CONSTRAINT_NAME from @CompositePrimaryKeyConstraints)
--select * from @CompositePrimaryKeyColumns
--Get details about the Composite Primary Key Columns
select
pkc.TABLE_NAME, pkc.CONSTRAINT_NAME, pkc.COLUMN_NAME, c.DATA_TYPE
from
INFORMATION_SCHEMA.COLUMNS c
inner join @CompositePrimaryKeyColumns pkc on pkc.TABLE_NAME = c.TABLE_NAME and pkc.COLUMN_NAME = c.COLUMN_NAME
order by
pkc.TABLE_NAME, c.ORDINAL_POSITION
Running this against Northwind gives:
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME DATA_TYPE
--------------------- ------------------------ --------------- -----------
CustomerCustomerDemo PK_CustomerCustomerDemo CustomerID nchar
CustomerCustomerDemo PK_CustomerCustomerDemo CustomerTypeID nchar
dtproperties pk_dtproperties id int
dtproperties pk_dtproperties property varchar
EmployeeTerritories PK_EmployeeTerritories EmployeeID int
EmployeeTerritories PK_EmployeeTerritories TerritoryID nvarchar
Order Details PK_Order_Details OrderID int
Order Details PK_Order_Details ProductID int
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply