MSSQL Server编写存储过程小工具(二)

本篇文章由 泉州SEO www.234yp.com 整理发布,mssql是什么 www.234yp.com/Article/168182.html 谢谢合作!
mssql是什么

  SQL Server编写存储过程小工具
  以下是两个存储过程的源程序
  /*===========================================================

  语法: sp_GenInsert <Table Name>,<Stored Procedure Name>
  以northwind 数据库为例
  sp_GenInsert 'Employees', 'INS_Employees'

  注释:如果您在Master系统数据库中创建该过程,那您就可以在您服务器上所有的数据库中使用该过程。

  =============================================================*/

  CREATE procedure sp_GenInsert
  @TableName varchar(130),
  @ProcedureName varchar(130)
  as
  set nocount on

  declare @maxcol int,
  @TableID int

  set @TableID = object_id(@TableName)

  select @MaxCol = max(colorder)
  from syscolumns
  where id = @TableID

  select 'Create Procedure ' + rtrim(@ProcedureName) as type,0 as colorder into #TempProc
  union
  select convert(char(35),'@' + syscolumns.name)
  + rtrim(systypes.name)
  + case when rtrim(systypes.name) in ('binary','char','nchar','nvarchar','varbinary','varchar') then '(' + rtrim(convert(char(4),syscolumns.length)) + ')'
  when rtrim(systypes.name) not in ('binary','char','nchar','nvarchar','varbinary','varchar') then ' '
  end
  + case when colorder < @maxcol then ','
  when colorder = @maxcol then ' '
  end
  as type,
  colorder
  from syscolumns
  join systypes on syscolumns.xtype = systypes.xtype
  where id = @TableID and systypes.name <> 'sysname'
  union
  select 'AS',@maxcol + 1 as colorder
  union
  select 'INSERT INTO ' + @TableName,@maxcol + 2 as colorder
  union
  select '(',@maxcol + 3 as colorder
  union
  select syscolumns.name
  + case when colorder < @maxcol then ','
  when colorder = @maxcol then ' '
  end
  as type,
  colorder + @maxcol + 3 as colorder
  from syscolumns
  join systypes on syscolumns.xtype = systypes.xtype
  where id = @TableID and systypes.name <> 'sysname'
  union
  select ')',(2 * @maxcol) + 4 as colorder
  union
  select 'VALUES',(2 * @maxcol) + 5 as colorder
  union
  select '(',(2 * @maxcol) + 6 as colorder
  union
  select '@' + syscolumns.name
  + case when colorder < @maxcol then ','
  when colorder = @maxcol then ' '
  end
  as type,
  colorder + (2 * @maxcol + 6) as colorder
  from syscolumns
  join systypes on syscolumns.xtype = systypes.xtype
  where id = @TableID and systypes.name <> 'sysname'
  union
  select ')',(3 * @maxcol) + 7 as colorder
  order by colorder

  select type from #tempproc order by colorder

  drop table #tempproc


关于更多mssql是什么内容,可以收藏本网页。
CCTV5直播简介: 天天直播将为您提供CCTV5、央视五台24小时高清直播

上一篇:MSSQL之联合注入


下一篇:MSSQL·查询数据库中所有索引的相关信息