August 19, 2003 at 8:35 am
Would anyone have sql that would produce a report on all user tables listing primary keys, etc.
Thanks for the help,
Charles L. Nichols
Charles L. Nichols
August 19, 2003 at 9:08 am
How about something like this?
select t.[name] as tablename, isnull(p.[name],'') as ConstraintName, isnull(p.xtype,'') as ConstraintType
from sysobjects t
left join sysobjects p on t.[id] = p.parent_obj
where t.xtype='U'
order by tablename asc
Dan B
August 19, 2003 at 9:15 am
Or this might be a little prettier...
select t.[name] as tablename, isnull(p.[name],'') as ConstraintName,
case p.xtype
When 'PK' Then 'Primary Key'
When 'C' Then 'Check'
When 'UQ' Then 'Unique'
When 'F' Then 'Foreign Key'
When 'TR' Then 'Trigger'
When 'D' Then 'Default'
Else ''
End As ConstraintType
from sysobjects t
left join sysobjects p on t.[id] = p.parent_obj
where t.xtype='U'
order by tablename asc
Dan B
August 20, 2003 at 2:44 pm
Thank You Dan for the help. Could you also help me in modifying the report to include all columns for each table.
Thanks again, I really appreciate the help!
Or this might be a little prettier...
select t.[name] as tablename, isnull(p.[name],'') as ConstraintName,
case p.xtype
When 'PK' Then 'Primary Key'
When 'C' Then 'Check'
When 'UQ' Then 'Unique'
When 'F' Then 'Foreign Key'
When 'TR' Then 'Trigger'
When 'D' Then 'Default'
Else ''
End As ConstraintType
from sysobjects t
left join sysobjects p on t.[id] = p.parent_obj
where t.xtype='U'
order by tablename asc
Dan B
[/quote]
Charles L. Nichols
Charles L. Nichols
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply