查表的字段名，主键，字段类型的语句(2)

来源：未知责任编辑：责任编辑发表时间:2013-12-01 14:21　点击:次

　　select name from sysobjects where xtype='u'

2、字段

SELECT c.name,t.name,c.xprec,c.xscale,c.isnullable

FROM systypes t,syscolumns c

WHERE t.xtype=c.xtype

AND c.id = (SELECT id FROM sysobjects WHERE name='YourTableName')

ORDER BY c.colid

3、主键（参考SqlServer系统存储过程sp_pkeys）

select COLUMN_NAME = convert(sysname,c.name)

from

sysindexes i, syscolumns c, sysobjects o

where o.id = object_id('[YourTableName]')

and o.id = c.id

and o.id = i.id

and (i.status & 0x800) = 0x800

and (c.name = index_col ('[YourTableName]', i.indid, 1) or

c.name = index_col ('[YourTableName]', i.indid, 2) or

c.name = index_col ('[YourTableName]', i.indid, 3) or

c.name = index_col ('[YourTableName]', i.indid, 4) or

c.name = index_col ('[YourTableName]', i.indid, 5) or

c.name = index_col ('[YourTableName]', i.indid, 6) or

c.name = index_col ('[YourTableName]', i.indid, 7) or

c.name = index_col ('[YourTableName]', i.indid, or

c.name = index_col ('[YourTableName]', i.indid, 9) or

c.name = index_col ('[YourTableName]', i.indid, 10) or

c.name = index_col ('[YourTableName]', i.indid, 11) or