导出列属性

Create proc sp_HelpTable
@TableName sysname=NULL,
@FieldName sysname=NULL
As
Begin
 /*主键、唯一键、索引*/
 select  TableName=o.name,
  FieldName=c.name,
  ObjectName=idx.name,
  Typeoid=cast(case when oidx.ID is null then N'Index' else N'Constraint' end as sysname),
  Define=stuff(case when oidx.xtype=N'PK' then N',Primary key'
      when oidx.xtype=N'UQ' then N',Unique key'
      when indexproperty(idxk.id,idx.name,N'ISUnique')=1 then N',Unique'
      else N'' end
   +case when indexproperty(idxk.id,idx.name,N'IsClustered')=1 then  N',Clustered' else N'' end
   +case when indexproperty(idxk.id,idx.name,N'ISFulltextkey')=1 then N',Fulltextkey' else N'' end,1,1,N'')
 from  sysobjects o join syscolumns c on o.id=c.id and
  objectproperty(o.id,N'ISUserTable')=1 and
  (o.name like @TableName or @TableName is null) and
  (c.name like @FieldName or @FieldName is null)
  join sysindexkeys idxk on idxk.id=o.id and idxk.colid=c.colid
  join sysindexes idx on idx.id=idxk.id and idx.indid=idxk.indid and idx.indid not in(0,255) and indexproperty(idxk.id,idx.name,N'IsAutoStatistics')=0
  left join sysobjects oidx on oidx.parent_obj=o.id and oidx.name=idx.name
 union all
 /*默认值*/
 select  o.name,
  c.name,
  ObjectName=od.name,
  Type=Cast(N'Default' as sysname),
  Define=cm.text
 from  sysobjects o join syscolumns c on c.id=o.id and objectproperty(o.id,N'ISUserTable')=1 and
  (o.name=@TableName or @TableName is null) and
  (c.name=@FieldName or @FieldName is null)
  join sysobjects od on od.parent_obj=o.id and objectproperty(od.id,N'ISDefaultCnst')=1
  join syscomments cm on cm.id=od.id and cm.id=c.cdefault
 /*Check约束*/
 union all
 select  o.name,
  c.name,
  ObjectName=oc.name,
  type=Cast(N'Check' as sysname),
  Define=cm.text
 from  sysobjects o join syscolumns c on c.id=o.id and objectproperty(o.id,N'ISUserTable')=1 and
  (o.name like @TableName or @TableName is null) and
  (c.name like @FieldName or @FieldName is null)
  join sysobjects oc on oc.parent_obj=o.id and objectproperty(oc.id,N'ISCheckCnst')=1
  join syscomments cm on cm.id=oc.id
  join sysdepends d on d.id=oc.id and d.depnumber=c.colid
 /*外键*/
 union all
 select  o.name,
  c.name,
  ObjectName=ofk.name,
  Type=cast(N'ForeignKey' as sysname),
  Define=Quotename(user_name(opk.uid))+N'.'+quotename(opk.name)+N'.'+quotename(cpk.name)
 from  sysobjects o join syscolumns c on c.id=o.id and
  objectproperty(o.id,N'ISUserTable')=1 and
  (o.name like @TableName or @TableName is null) and
  (c.name like @FieldName or @FieldName is null)
  join sysobjects ofk on ofk.parent_obj=o.id and objectproperty(ofk.id,N'IsForeignKey')=1
  join sysforeignkeys fk on fk.constid=ofk.id and fk.fkey=c.colid
  join sysobjects opk on opk.id=fk.rkeyid
  join syscolumns cpk on cpk.id=opk.id and cpk.colid=fk.rkey
 order by TableName,FieldName,ObjectName
End