查询业务表以及字段属性
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'