剛好需要這個(gè)過(guò)程, 在網(wǎng)上找了幾個(gè), 感覺(jué)都差一些, 其SQL都特別長(cháng). 而且我特別需要用到聚類(lèi)索引, 自己寫(xiě)了個(gè), 這是程序開(kāi)發(fā)使用到的版本.
select o.name tablename, c.name fieldname, t.name fieldtype, columnproperty(c.id,c.name,'PRECISION') fieldlen, c.Scale,c.length,
c.colid fieldorder, c.isnullable,
case when c.colid in(select ik.colid
from sysindexes i, Sysindexkeys ik, sysobjects oo
where i.id=ik.id and i.indid=ik.indid
and i.name=oo.name and oo.xtype='PK' --主鍵
and o.id=i.id
) then 1 else 0 end isPrimaryKey,
case when c.colid in(select ik.colid
from sysindexes i, Sysindexkeys ik
where i.id=ik.id and i.indid=ik.indid
and o.id=i.id and i.indid=1 --聚類(lèi)索引
) then 1 else 0 end isClusterKey,
columnproperty( c.id, c.name,'IsIdentity') IsIdentity,
isnull(m.text,'') defaultvalue
from sysobjects o, syscolumns c, systypes t, syscomments m
where o.xtype='U'
and o.id=c.id
and c.xtype=t.xtype
and c.cdefault*=m.id
order by o.name, c.colid
程序使用到的版本是否用0/1表示, 如果用在文檔整理, 難看得要命, 優(yōu)化了個(gè)文檔版本的,在查詢(xún)分析器找出來(lái)還是蠻好看的.
select case when c.colid=1 then o.name else '' end 表名,
c.colid 順序,
c.name 字段名,
t.name 字段類(lèi)型,
columnproperty(c.id,c.name,'PRECISION') 字段長(cháng)度,
isnull(c.Scale,'') 小數位,
c.length 占用字節,
case when c.isnullable=1 then '√' else '' end 可為空,
case when c.colid in(select ik.colid
from sysindexes i, Sysindexkeys ik, sysobjects oo
where i.id=ik.id and i.indid=ik.indid
and i.name=oo.name and oo.xtype='PK' --主鍵
and o.id=i.id
) then '√' else '' end 主鍵,
case when c.colid in(select ik.colid
from sysindexes i, Sysindexkeys ik
where i.id=ik.id and i.indid=ik.indid
and o.id=i.id and i.indid=1 --聚類(lèi)索引
) then '√' else '' end 聚類(lèi)索引,
case when columnproperty( c.id, c.name,'IsIdentity')=1 then '√' else '' end 自增長(cháng),
isnull(m.text,'') 默認值
from sysobjects o, syscolumns c, systypes t, syscomments m
where o.xtype='U'
and o.id=c.id
and c.xtype=t.xtype
and c.cdefault*=m.id
order by o.name, c.colid
