关于自定义代码生成器(续)---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 temp
		from syscolumns col,sysforeignkeys f where f.fkeyid=col.id
		and 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 b
on a.xtype=b.xusertype
inner 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 g
on a.id=g.major_id AND a.colid = g.minor_id  
    where d.name like'TestData' and SCHEMA_NAME(d.uid)='TestSchema' --所要查询的表和所在的架构
)a
left 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
)b
on a.tablename = b.ftable and a.name = b.fname
order 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