mssql sqlserver 使用sql脚本获取指定表上主键的4种方法分享


摘要:
下文分享使用sql脚本获取指定数据表上的主键的方法分享
实验环境:sqlserver 2008 R2


--1.通过sysindexkeys获取表主键
   SELECT o.name as [表名称],
                 c.name as  [列名称],
                  k.colid as [列顺序编号],
                  k.keyno as [索引顺序号]
                    FROM sysindexes i
                INNER JOIN sysindexkeys k ON i.id = k.id AND i.indid = k.indid 
                INNER JOIN sysobjects o ON i.id = o.id 
                INNER JOIN syscolumns c ON i.id=c.id AND k.colid = c.colid 
                WHERE o.xtype = 'U'
                              AND EXISTS(SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name = i.name) 
                ORDER BY o.name,k.colid
 
--2.使用系统自带存储过程sp_pkeys 获取指定表主键
    exec sp_pkeys @table_name ='表名称'

--3. 使用系统表INFORMATION_SCHEMA.KEY_COLUMN_USAGE获取指定表主键
    SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
                 WHERE TABLE_NAME='表名称'

--4.使用查询表结构的模式获取指定表主键信息
 -- 使用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'列说明' 
     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