Find primary key columns

I'm trying to find out some information about the schema of a database so that I can write a smarter table browser for my application's admin interface. I need to get the primary keys of a table, but I can't seem to get them in a simple manner. I've tried using ASP.NET functionality (like GetSchemaTable() for instance) but it doesn't get all the information I want. I tried INFORMATION_SCHEMA.xxx but those views don't give me everything I need either. Here's what I need:

Table Name, Column Name, Column Position, Data Type, Domain Name (user defined data type name), Length (in characters for nvarchar/nchar please), Is it nullable, Is it computed, Referenced Table Name, Referenced Column Name, Is it part of the primary key

I enforce that the schema allows a column to reference at most one column in one other table, so the "referenced" columns will either be null or contain at most one value. I've constructed this so far:

select o.name TableName
, c.name ColumnName
, fko.name ReferenceTableName
, fkc.name ReferenceColumnName
, COALESCE(t.name, t.name) ColumnType
, c.prec ColumnLength
, c.isnullable IsNullable
, c.iscomputed IsComputed
, c.colid ColumnOrder
from syscolumns c
inner join sysobjects o on c.id=o.id
inner join systypes t on c.xusertype=t.xusertype
left outer join sysforeignkeys fk on c.id=fk.fkeyid and c.colid=fk.fkey
left outer join sysobjects fko on fk.rkeyid=fko.id
left outer join syscolumns fkc on fk.rkeyid=fkc.id and fk.rkey=fkc.colid
where o.xtype='U'

Where can I find out if a column is a primary key?I don't see a way for you to get all of the information you are looking for out of the Information_Schema views, either.

I normally get the "is this a primary key?" information out of the Information_Schema.Key_Column_Usage view joined on the Information_Schema.Table_Constraints view. I am not certain of the underlying sys tables (althought perusal of those views will obviously tell you). Below is some incomplete code showing how I'd join the KCU and TC views:

SELECT
 Tables.Table_Name AS [Table Name],
 Columns.Column_Name AS [Column Name],
 Columns.Ordinal_Position AS [Column Position],
 CASE WHEN TC.Constraint_Name IS NULL Then 'No' Else 'Yes' END AS [Part of the Primary Key]
FROM
 information_schema.tables AS Tables
LEFT OUTER JOIN
 information_schema.columns AS Columns ON Tables.Table_Catalog = Columns.Table_Catalog AND Tables.Table_Schema = Columns.Table_Schema AND Tables.Table_Name = Columns.Table_name
LEFT OUTER JOIN
 information_schema.key_column_usage AS KCU ON Tables.Table_Catalog = KCU.Table_Catalog AND Tables.Table_Schema = KCU.Table_Schema AND Columns.Column_Name = KCU.Column_Name
LEFT OUTER JOIN
 information_schema.table_constraints AS TC ON Tables.Table_Catalog = TC.Table_Catalog AND Tables.Table_Schema = TC.Table_Schema AND KCU.Constraint_Name = TC.Constraint_Name AND TC.constraint_type = 'PRIMARY KEY'

Terri|||Thanks for the idea to look at the INFORMATION_SCHEMA.KEY_COLUMN_USAGE directly! You can execute "sp_helptext [INFORMATION_SCHEMA.KEY_COLUMN_USAGE]" if you want to see how this view is defined. It uses an undocumented table master.dbo.spt_values, but it did lead me to the sysindexkeys and sysindexes table as I was looking for documentation of the spt_values table.

Here is my solution, for anyone who is interested. I use only explicitly documented columns of sysXXX tables, so that hopefully it might work in SQL Server versions other than SQL Server 2000.

select o.name TableName
, c.name ColumnName
, t.name ColumnType
, c.prec ColumnLength
, CONVERT(bit, COALESCE(pko.id, 0)) IsKey
, CONVERT(bit, c.isnullable) IsNullable
, CONVERT(bit, c.iscomputed) IsComputed
, c.colid ColumnOrder
, fko.name ReferenceTableName
, fkc.name ReferenceColumnName
from syscolumns c
inner join sysobjects o on c.id=o.id
inner join systypes t on c.xusertype=t.xusertype
left outer join sysforeignkeys fk on c.id=fk.fkeyid and c.colid=fk.fkey
left outer join sysobjects fko on fk.rkeyid=fko.id
left outer join syscolumns fkc on fk.rkeyid=fkc.id and fk.rkey=fkc.colid
left outer join sysindexkeys ik on c.id=ik.id and c.colid=ik.colid
left outer join sysindexes i on ik.id=i.id and ik.indid=i.indid
left outer join sysobjects pko on c.id=pko.parent_obj and i.name=pko.name
where o.xtype='U'

This assumes that each column references at most one other column. If you have a column that references more than one column, you'll get a row for each referenced column due to the left outer joins. It is ok for more than one column to be part of a primary key. ColumnType is either the user defined data type or the system defined data type if the column was not specified with a user defined data type. ColumnLength is the length in characters for nvarchar and nchar, NULL for text, ntext and image, and the length in bytes for all other types. The IsKey, IsNullable and IsComputed columns are converted to bit type so that ASP.NET can automatically treat them as Boolean values.

find all triggers

How do I query the schema views (preferably) or sys tables to find all
triggers on my db?
tia
chrisDisplays the name of the table and the triggers:
Select c.name,a.name
From sysobjects a, sysdepends b, sysobjects c
Where a.id = b.id
And b.depid = c.id and a.type = 'TR' and c.type = 'U'
'U' defines tables, views are not incuded here, for that, leave out the last
part
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BD6E22CA-A815-4CD1-AD15-590E6C168146@.microsoft.com...
> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
> tia
> chris|||Select name TriggerName,
object_name(parent_obj) TableName
from sysobjects
Where type = 'TR'
"Chris" wrote:

> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
> tia
> chris|||Hi Chris,
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:BD6E22CA-A815-4CD1-AD15-590E6C168146@.microsoft.com...
> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
Try this:
select
trig.name as [Trigger Name],
tab.name as [Table Name],
case
when ObjectProperty( trig.id, 'ExecIsInsteadOfTrigger') = 1 then 'INSTEAD OF
' else 'FOR ' end
+ substring (
case when ObjectProperty( trig.id, 'ExecIsInsertTrigger') = 1 then ',
INSERT' else '' end +
case when ObjectProperty( trig.id, 'ExecIsUpdateTrigger') = 1 then ',
UPDATE' else '' end +
case when ObjectProperty( trig.id, 'ExecIsDeleteTrigger') = 1 then ',
DELETE' else '' end
, 3, 100) as [Trigger for]
from sysobjects as trig
inner join sysobjects as tab on tab.id = trig.parent_obj
where trig.type = 'TR'
order by tab.name
Or this:
select
object_name(parent_obj) as TableName,
name as TriggerName,
case(OBJECTPROPERTY(id, 'ExecIsTriggerDisabled'))
when 0 then 'YES'
when 1 then 'NO' end as Enable,
case(OBJECTPROPERTY(id, 'ExecIsInsertTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Insert],
case(OBJECTPROPERTY(id, 'ExecIsUpdateTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Update],
case(OBJECTPROPERTY(id, 'ExecIsDeleteTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Delete],
case(OBJECTPROPERTY(id, 'ExecIsAfterTrigger'))
when 0 then 'Instead of'
when 1 then 'After' end as [Type],
crdate as CreationDate
from
sysobjects
where
type = 'TR'
order by
object_name(parent_obj), name

> tia
> chris
HTH,
Andrea - www.absistemi.it