March 24, 2009 at 8:32 am
Hi
I am using sql 2000 and the query fetches system column consraint types.
One of the table column has both primary and foreign key constraint. so it returns two records. I need to get only one record and also get the count of the records
SQL
select distinctc.table_name, c.COLUMN_NAME, pk.constraint_type
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where (constraint_type = 'FOREIGN KEY' or CONSTRAINT_TYPE = 'PRIMARY KEY')
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME order by c.table_name
1. Filter out to one record displaying tablename, columnname and constraint type = Primary Key only (Though the table column has both constraints)
2. I need another column that has the count of the duplicate records on tablename and columnname with different contraints.
Thanks
Gan
March 26, 2009 at 2:31 pm
Hi
I have tried using a subquery to get the constraint type and used top 1 to get only one record for a column. (query below)
But if i also need to get the constraint_name from the KEY_COLUMN_USAGE or TABLE_CONSTRAINTS table, should i need to write another subquery similar to the one i did for getting constraint_type or any other way using left or right outer joins.
Any help?
=========================================
Select distinct
o.Name as TableName,
c.Name as ColumnName,
c.length as Length,
c.xprec as [Precision],
c.xscale as Scale,
c.isnullable,
(select name from sysTypes where xusertype = c.xusertype) as DataType,
CASE (select top 1 pk.constraint_type from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu where (constraint_type = 'FOREIGN KEY' or CONSTRAINT_TYPE = 'PRIMARY KEY') and cu.TABLE_NAME = pk.TABLE_NAME and cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME and pk.Table_Name = o.Name and cu.column_name=c.Name)
When 'PRIMARY KEY' then 'PK'
When 'FOREIGN KEY' then 'FK'
When 'Both' then 'Both'
ELSE 'None'
End as KeyType
FROM
sysColumns C
INNER JOIN sysObjects o on c.id = o.id
WHERE
o.type = 'U' and o.name <> 'dtproperties'
ORDER BY o.name
======================================
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply