SQL Server編寫存儲過程小工具(二)

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