SqlServer2000 系統表查詢

查詢業務表以及字段屬性

select a.name     as KEY_TABLE_NAME, 
		     b.name     as KEY_COLUMN_NAME,     
		     c.name     as KEY_DATA_TYPE,     
		     b.length   as KEY_DATE_LENGTH,
		     case  
				 when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=b.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = b.id AND colid=b.colid)) ) then 'y'  
				 else 'n' end         as KEY_IS_PK,

		     case  
				 when exists(select 1 from syscolumns where id = object_id(a.name) and (autoval is not null or status = 128) and name=b.name) then 'y' 
				 else 'n' end         as KEY_IS_IDENTITY,
		     d.value    as remark,
		     case when b.isnullable=1 then 'y'else 'n' end as KEY_IS_NULL
		from sysobjects a     
  inner join syscolumns b on a.id = b.id 
  inner join systypes c on b.xtype = c.xtype          
   left join sysproperties d         
		  on (b.id = d.id and b.colid = d.smallid)
	   where ( a.xtype ='u' ) 
		 and c.name <> 'sysname'
and ---a.name ='supplier_aggregator'
	---order by a.name,d.name
) result where result.KEY_IS_INDEX = 'y'
           

查詢業務表主鍵與索引鍵

   group_id表示index組号, 同表中有相同group_id表示聯合主鍵 或者聯合索引

select obj.name AS TABLE_NAME, 
col.name AS COLUMN_NAME, 
indexes.KEY_IS_PK AS IS_PK, 
indexes.indid AS GROUPD_ID
from syscolumns col
inner join sysobjects obj on col.id = obj.id
inner join systypes type on col.xtype = type.xtype
inner join 
(
	select keys.colid,keys.id,idx.indid,
	case  
		 when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=keys.id and name=idx.name ) then 'y'  
		 else 'n' end         as KEY_IS_PK

	from sysindexkeys keys 
	inner join (
		select *
		  from sysindexes
		 where (status & 64)=0
		   and indid > 0
		   and indid < 255
	) idx on keys.id = idx.id and keys.indid = idx.indid 

)indexes on col.id = indexes.id and col.colid=indexes.colid
where obj.xtype='u' 
---and obj.name = 'geonames_airport'
and type.name <> 'sysname'