关于自定义代码生成器(续)---SQL2008查询数据库字段主键，外键，唯一键等相关属性

2. 查询字段名，表名，架构名，序号，是否主键，是否外键，是否唯一键，是否为空，是否自动增长，默认值，字段类型，大小，长度，小数位数，相关属性

select a.*,isnull(b.rtable,'0') as RefTable,isnull(b.rname,'0') as RefName from (SELECT    d.name as TableName,--如果表名相同就返回空       a.colorder as  ID, --字段序号      a.name as  Name,   --字段名    (case  when COLUMNPROPERTY( a.id,a.name, 'IsIdentity' )=1 then  1 else   0  end)  as DbIdentity,--自动增长       (case  when (SELECT count(*) FROM sysobjects--查询主键                       WHERE (name in                                (SELECT name FROM sysindexes                                WHERE (id = a.id)  AND (indid in                                        (SELECT indid FROM sysindexkeys                                         WHERE (id = a.id) AND (colid in                                            (SELECT colid FROM syscolumns                                           WHERE (id = a.id) AND (name = a.name))                           )))))            AND (xtype = 'PK' ))>0 then  1   else   0  end)  as  PK,--查询主键END      (case when(select count(*) from (select OBJECT_NAME(f.fkeyid) as fname, col.name, f.constid as tempfrom syscolumns col,sysforeignkeys f where f.fkeyid=col.idand f.fkey=col.colid) ft where ft.fname = d.name and ft.name=a.name)>0 then 1 else 0 end) as FK, --查询外键(case when(select count(COLUMN_NAME) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME in (SELECT name FROM sys.key_constraints where object_name(parent_object_id)=d.name AND type='UQ') and COLUMN_NAME=a.name)>0 then 1 else 0 end) as UQ,--查询唯一键b.name as  DbType,--字段类型   a.length as  DbLength,--占用字节数(select TOP 1 TABLE_SCHEMA from information_schema.COLUMNS WHERE TABLE_NAME=d.name) as TableSchema,--架构COLUMNPROPERTY(a.id,a.name,'PRECISION' )  as   DbPrecision, -- 长度isnull(COLUMNPROPERTY(a.id,a.name,'Scale' ),0)  as  DbScale, -- 小数位数(case  when a.isnullable=1 then  1 else   0  end)  as  DbNull, --允许空isnull(e.text,'' )  as  DefaultValue  --默认值FROM syscolumns a left join systypes bon a.xtype=b.xusertypeinner join sysobjects d    on a.id=d.id and d.name<> 'dtproperties'  left join syscomments e   on a.cdefault=e.id   left join sys.extended_properties gon a.id=g.major_id AND a.colid = g.minor_id      where d.name like'TestData' and SCHEMA_NAME(d.uid)='TestSchema' --所要查询的表和所在的架构)aleft join ( select a.*,b.fname from  (select OBJECT_NAME(rkeyid) rtable,col.name rname,OBJECT_NAME(fkeyid) ftable from sysforeignkeys f inner join  syscolumns col on f.rkeyid=col.id and f.rkey=col.colid and OBJECT_NAME(fkeyid)='表名') a inner join  (select OBJECT_NAME(rkeyid) rtable,col.name fname,OBJECT_NAME(fkeyid) ftable from sysforeignkeys f inner join  syscolumns col on f.fkeyid=col.id and f.fkey=col.colid and OBJECT_NAME(fkeyid)='表名') b  on a.rtable=b.rtable)bon a.tablename = b.ftable and a.name = b.fnameorder by replace(a.Name,'_','')

2. 查询表相关主外键属性：主表，主表字段，外表，外表字段

select * from (select a.*,b.fname from (select OBJECT_NAME(rkeyid) rtable,col.name rname,OBJECT_NAME(fkeyid) ftable from sysforeignkeys f inner join syscolumns col on f.rkeyid=col.id and f.rkey=col.colid) a right join (select OBJECT_NAME(rkeyid) rtable,col.name fname,OBJECT_NAME(fkeyid) ftable from sysforeignkeys f inner join syscolumns col on f.fkeyid=col.id and f.fkey=col.colid) b  on a.rtable=b.rtable and a.ftable=b.ftable) a order by ftable