如何查询出一个表的主键字段名称,类型和长度的清单

Question

如何查询出一个表的主键字段名称，类型，长度的清单.
请注意,我需要的最终效果格式是如下:
表名     主键名       序号   类型
Mytable MyID      1   int
Mytable DBGrid_Name   2   varchar(500)
Mytable MyMoney       3   decimal(18,4)
Mytable MyDate        4     datetime附：
我通过语句
select * from information_schema.columns where table_name = 'Mytable'来查询，却只能查询出下面这样的格式
表名     主键名       序号   类型
Mytable MyID      1   int
Mytable DBGrid_Name   2   varchar
Mytable MyMoney       3   decimal
Mytable MyDate        4     datetime虽然还有其他字段已标注了长度，小数位等信息，但却很难将它们组合成我想要的那种效果
特来求助高手们。

Answer 1

information_schema.columns 目录视图
有两个列Numeric_Precision和Numeric_Scale自己研究.

Answer 2

2000的话可以试试这个
SELECT     
    (case   when   a.colorder=1   then   d.name   else   ''   end)   N'表名',   
    a.colorder   N'字段序号',   
    a.name   N'字段名',   
    (case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   '√'else   ''   end)   N'标识',   
    (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   '√'   else   ''   end)   N'主键',   
    b.name   N'类型',   
    a.length   N'占用字节数',   
    COLUMNPROPERTY(a.id,a.name,'PRECISION')   as   N'长度',   
    isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)   as   N'小数位数',   
    (case   when   a.isnullable=1   then   '√'else   ''   end)   N'允许空',   
    isnull(e.text,'')   N'默认值',   
    isnull(g.[value],'')   AS   N'字段说明'   
  --into   ##tx   
    
  FROM     syscolumns     a   left   join   systypes   b     
  on     a.xtype=b.xusertype   
  inner   join   sysobjects   d     
  on   a.id=d.id     and     d.xtype='U'   and     d.name<>'dtproperties'   
  left   join   syscomments   e   
  on   a.cdefault=e.id   
  left   join   sysproperties   g   
  on   a.id=g.id   AND   a.colid   =   g.smallid       
  order   by   object_name(a.id),a.colorder 

Answer 3

CHARACTER_MAXIMUM_LENGTH 
 以字符为单位的最大长度，适于二进制数据、字符数据，或者文本和图像数据。
 
NUMERIC_PRECISION 
 近似数字数据、精确数字数据、整数数据或货币数据的精度。否则，返回 NULL。 
 
NUMERIC_SCALE 
 近似数字数据、精确数字数据、整数数据或货币数据的小数位数。否则，返回 NULL。
 
---------------------得到值..连接字符串还用说吗?

Answer 4

我想说的正是字符串连接。
我的代码是这样写的。。
--下面是先把关键字段查出来插到 #AA里去
SELECT A.TABLE_NAME,A.COLUMN_NAME,A.ORDINAL_POSITION,B.DATA_TYPE,B.CHARACTER_MAXIMUM_LENGTH
      ,B.CHARACTER_OCTET_LENGTH,B.NUMERIC_PRECISION,B.NUMERIC_SCALE
into #AA
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A,information_schema.columns B
WHERE A.COLUMN_NAME=B.COLUMN_NAME
and (A.TABLE_NAME='User_ADD_Dbgrid_Column_For_Self' and B.TABLE_NAME='User_ADD_Dbgrid_Column_For_Self')--下面的语句是通过系统表各个字段的值，组合起来组成 MyType.  
--我这样写，会让int类型也会生成int(10,0),这样的格式。。
select *,DATA_TYPE+'('+isnull(cast(CHARACTER_MAXIMUM_LENGTH as varchar(50)),'')+
         case right(isnull(cast(NUMERIC_PRECISION as varchar(50)),'')+','+isnull(cast(NUMERIC_SCALE as varchar(50)),''),1)
         when ',' then left(isnull(cast(NUMERIC_PRECISION as varchar(50)),'')+','+isnull(cast(NUMERIC_SCALE as varchar(50)),''),len(isnull(cast(NUMERIC_PRECISION as varchar(50)),'')+','+isnull(cast(NUMERIC_SCALE as varchar(50)),''))-1)
         else isnull(cast(NUMERIC_PRECISION as varchar(50)),'')+','+isnull(cast(NUMERIC_SCALE as varchar(50)),'')
         end+')' MyType
from #AA

Answer 5

我按照上面的这种写法，都会让int，datetime类型生成类似int(10,0),这样的格式。。显然是不对的。。

Answer 6

找SQL表的主键1:利用SQL自带的存储过程EXEC sp_pkeys @table_name='表名'2:利用系统表INFORMATION_SCHEMA.KEY_COLUMN_USAGE SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME='表名'3: 以下这个查询表结构的语句可能大家都见过：利用syscolumns,sysindexes,sysindexkeys三个表关联可以查出主键SELECT 
 (case when a.colorder=1 then d.name else '' end) N'表名',
 a.colorder N'字段序号',
 a.name N'字段名',
 (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
 (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 '√' else '' end) N'主键',
 b.name N'类型',
 a.length N'占用字节数',
 COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
 (case when a.isnullable=1 then '√'else '' end) N'允许空',
 isnull(e.text,'') N'默认值',
 isnull(g.[value],'') AS N'字段说明'
--into ##txFROM  syscolumns  a left join systypes b 
on  a.xtype=b.xusertype
inner join sysobjects d 
on a.id=d.id  and  d.xtype='U' and  d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid  
order by object_name(a.id),a.colorder

Answer 7

select m.表名 , m.主键名 , 序号 = (select count(1) from 
(
select o.name as 表名,c.name as 主键名,t.name as 类型
from sysindexes i
join sysindexkeys k on i.id = k.id and i.indid = k.indid
join sysobjects o on i.id = o.id
join syscolumns c on i.id=c.id and k.colid = c.colid
join systypes t on c.xusertype=t.xusertype
where o.xtype = 'U' 
and exists(select 1 from sysobjects where xtype = 'PK' and parent_obj=i.id and name = i.name)
) n where n.表名 < m.表名) + 1, 
m.类型 
from 
(
select o.name as 表名,c.name as 主键名,t.name as 类型
from sysindexes i
join sysindexkeys k on i.id = k.id and i.indid = k.indid
join sysobjects o on i.id = o.id
join syscolumns c on i.id=c.id and k.colid = c.colid
join systypes t on c.xusertype=t.xusertype
where o.xtype = 'U' 
and exists(select 1 from sysobjects where xtype = 'PK' and parent_obj=i.id and name = i.name)
) m
order by 序号

Answer 8

dawugui，麻烦你看清楚我的问题，我要的结果是如下这种，你的查出来之后，“类型”这一列只是单纯的类型，长度，小数位，是没有的。。表名    主键名      序号  类型 
Mytable MyID     1   int 
Mytable DBGrid_Name  2   varchar(500) 
Mytable MyMoney      3   decimal(18,4) 
Mytable MyDate        4    datetime 

Answer 9

dawugui，麻烦你看清楚我的问题，我要的结果是如下这种，你的查出来之后，“类型”这一列只是单纯的类型，长度，小数位，是没有的。。表名    主键名      序号  类型 
Mytable MyID     1   int 
Mytable DBGrid_Name  2   varchar(500) 
Mytable MyMoney      3   decimal(18,4) 
Mytable MyDate        4    datetime