擷取MSSQL表中的主鍵（預存程序）

 

建立一個預存程序，通過傳入的表名參數，返回該表的主鍵。其預存程序代碼如下：

 

CREATE  Proc pGetPkColumn

(@tableName varchar(50))

as

    /*

      Create By Peopleyl

      獲得所要匯入資料表的自動遞增列

    */

declare @columnName varchar(500)

set @columnName = ''

SELECT @columnName = @columnName +','+ c.Name

FROM

  sysindexes i,

  sysindexkeys k,

  sysobjects o,

  syscolumns c,

  systypes d

WHERE

  i.Name like 'pk_%'

  AND o.id=i.id

  AND o.xType='U'

  AND i.id = k.id

  AND i.indid = k.indid

  AND i.id = o.id

  AND i.id = c.id

  AND k.colid = c.colid

  and i.indid > 0

  and i.indid < 255

  and (i.status & 64)=0

  and c.xtype = d.xtype

  and o.name = @tableName

if charindex(',',@columnName) = 1

select Stuff(@columnName,1,1,'')

else

select @columnName