根据表名生成insert和update字段列表

--1.生成插入字段列表
if exists(select 1 from sysobjects where id=object_id('usp_sql_insert_get') and type='P')
   drop proc usp_sql_insert_get
go
create proc usp_sql_insert_get
   @tableName varchar(20) --表名
as
/**********
[版本号]1.2.0.1
[创建时间]2010.06.02
[作者]ZHOEN
[版权]Copyright(c) 2010
[描述]
[功能说明]
   根据表名生成插入字段列表
[参数说明]
   @tableName varchar(20) --表名
[返回值]
[结果集、排序] 
[调用的sp]
[调用实例]
   exec usp_sql_insert_get @tableName
[修改历史]
**********/
  set nocount on

  declare @rowCount int,
          @rowIndex int,
          @fields varchar(1000)

  select identity(int,1,1) as lsh,[name] into #tempData
  from syscolumns
  where id=object_id(@tableName)

  select @rowCount=count(1) from #tempData

  select @rowIndex=1 , @fields = ''
  while @rowIndex<=@rowCount
  begin
    select @fields = @fields + ',' + [name]
    from #tempData
    where lsh = @rowIndex

    set @rowIndex =  @rowIndex + 1
  end

  select @fields as fields
go
--2.生成更新字段列表
if exists(select 1 from sysobjects where id=object_id('usp_sql_update_get') and type='P')
   drop proc usp_sql_update_get
go
create proc usp_sql_update_get
   @tableName varchar(20) --表名
as
/**********
[版本号]1.2.0.1
[创建时间]2010.06.02
[作者]ZHOEN
[版权]Copyright(c) 2010
[描述]
[功能说明]
   根据表名生成更新字段列表
[参数说明]
   @tableName varchar(20) --表名
[返回值]
[结果集、排序] 
[调用的sp]
[调用实例]
  exec usp_sql_update_get @tableName
[修改历史]
**********/
  set nocount on

  declare @rowCount int,
          @rowIndex int,
          @fields varchar(1000)

  select identity(int,1,1) as lsh,[name] into #tempData
  from syscolumns
  where id=object_id(@tableName)

  select @rowCount=count(1) from #tempData

  select @rowIndex=1 , @fields = ''
  while @rowIndex<=@rowCount
  begin
    select @fields = @fields + ',a.' + [name] + ' = b.' + [name]
    from #tempData
    where lsh = @rowIndex

    set @rowIndex =  @rowIndex + 1
  end

  select @fields as fields
go