CREATEPROC #AutoGeneration_Load_P @TABLENAMEVARCHAR(50) AS BEGIN DECLARE@HOST_NAMEVARCHAR(200) DECLARE@GET_DATEDATETIME DECLARE@SQLROCVARCHAR(4000) DECLARE@REMARKVARCHAR(2000) DECLARE@SQLVARCHAR(2000) DECLARE@PARAMETERVARCHAR(2000) DECLARE@DESCRIPTIONVARCHAR(8000) DECLARE@WHERE_SQLVARCHAR(2000) DECLARE@ROWCOUNTINT SELECT@SQLROC='',@DESCRIPTION='',@PARAMETER='',@REMARK='',@WHERE_SQL='',@SQL='', @HOST_NAME=HOST_NAME(),@GET_DATE=GETDATE() SET@SQLROC=@SQLROC+'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID(''SP_'+@TABLENAME+'_Load'') AND XTYPE IN (N''P''))'+CHAR(10) SET@SQLROC=@SQLROC+SPACE(5)+'DROP PROC SP_'+@TABLENAME+'_Load'+CHAR(10) SET@SQLROC=@SQLROC+'GO ' SET NOCOUNT ON CREATETABLE #(TABLE_QUALIFIER VARCHAR(100), TABLE_OWNER VARCHAR(100), TABLE_NAME VARCHAR(100), COLUMN_NAME VARCHAR(100), KEY_SEQ VARCHAR(50), PK_NAME VARCHAR(100)) INSERTINTO # EXEC SP_PKEYS @TABLENAME SELECT@REMARK=@REMARK+',@'+COLUMN_NAME, @WHERE_SQL=@WHERE_SQL+' AND '+COLUMN_NAME+'=@'+COLUMN_NAME+'' FROM # SELECT@PARAMETER=@PARAMETER+SPACE(4)+'@'+LTRIM(NAME)+SPACE(20-LEN(NAME))+ CASEWHEN xtype=34THEN'image' WHEN xtype=35THEN'text' WHEN xtype=36THEN'uniqueidentifier' WHEN xtype=48THEN'tinyint' WHEN xtype=52THEN'smallint' WHEN xtype=56THEN'int' WHEN xtype=58THEN'smalldatetime' WHEN xtype=59THEN'real' WHEN xtype=60THEN'money' WHEN xtype=61THEN'datetime' WHEN xtype=62THEN'float' WHEN xtype=98THEN'sql_variant' WHEN xtype=99THEN'ntext' WHEN xtype=104THEN'bit' WHEN xtype=106THEN'decimal' WHEN xtype=108THEN'numeric' WHEN xtype=122THEN'smallmoney' WHEN xtype=127THEN'bigint' WHEN xtype=165THEN'varbinary' WHEN xtype=167THEN'varchar'+'('+LTRIM(length)+')' WHEN xtype=173THEN'binary' WHEN xtype=175THEN'char'+'('+LTRIM(length)+')' WHEN xtype=189THEN'timestamp' WHEN xtype=231THEN'nvarchar'+'('+LTRIM(length)+')' WHEN xtype=239THEN'nchar'+'('+LTRIM(length)+')' WHEN xtype=241THEN'xml' WHEN xtype=231THEN'sysname'END+','+CHAR(10) FROM SYSCOLUMNS A WHERE ID=OBJECT_ID(''+@TABLENAME+'') AND NAME IN (SELECT COLUMN_NAME FROM #) DROPTABLE # SET NOCOUNT OFF SET@DESCRIPTION=@DESCRIPTION+'/*+--------------------------------------+'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 过程名称:SP_'+@TABLENAME+'_Load'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 功能说明:查询表'+@TABLENAME+'记录集合的存储过程'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 入口参数:'+STUFF(@REMARK,1,1,'')+''+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 过程返回:返回所有记录集'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 维护记录:Y/A'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 使用案例:SP_'+@TABLENAME+'_Load'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 工作站名:'+@HOST_NAME+''+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 联系方式:zlp321001@hotmail.com'+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'| 创建日期:'+CONVERT(VARCHAR(20),@GET_DATE,120)+''+CHAR(10) SET@DESCRIPTION=@DESCRIPTION+'+--------------------------------------+*/'+CHAR(10) SELECT@SQLROC=@SQLROC+CHAR(10)+@DESCRIPTION+'CREATE PROC SP_'+@TABLENAME+'_Load' SET@SQLROC=@SQLROC+CHAR(13)+CHAR(10)+LEFT(@PARAMETER,LEN(@PARAMETER)-2)+CHAR(10) SET@SQLROC=@SQLROC+'AS'+CHAR(10)+'BEGIN' SET@SQLROC=@SQLROC+CHAR(10)+SPACE(4)+'SET NOCOUNT ON' SET@SQLROC=@SQLROC+CHAR(32)+@SQL SET@SQLROC=@SQLROC+CHAR(10)+SPACE(8)+'SELECT * FROM '+@TABLENAME+' WHERE '+STUFF(@WHERE_SQL,1,4,'')+CHAR(10) SET@SQLROC=@SQLROC+SPACE(4)+'SET NOCOUNT OFF' SET@SQLROC=@SQLROC+CHAR(10)+'END' PRINT@SQLROC+CHAR(10)+'GO ' END GO CREATEPROC #SP_Generation_Load @TABLENAMESVARCHAR(8000) AS BEGIN DECLARE@IINT DECLARE@TABLENAMEVARCHAR(100) SET@I=CHARINDEX(',',@TABLENAMES) WHILE@I>0 BEGIN SET@TABLENAME=LEFT(@TABLENAMES,@I-1) EXEC #AutoGeneration_Load_P @TABLENAME SET@TABLENAMES=RIGHT(@TABLENAMES,LEN(@TABLENAMES)-@I) SET@I=CHARINDEX(',',@TABLENAMES) END IFLEN(@TABLENAMES)>0 BEGIN EXEC #AutoGeneration_Load_P @TABLENAMES END END GO --测试 #SP_Generation_Load 't' dropproc #AutoGeneration_Load_P dropproc #SP_Generation_Load --测试结果: /**//* IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('SP_t_Load') AND XTYPE IN (N'P')) DROP PROC SP_t_Load GO /*+--------------------------------------+ | 过程名称:SP_t_Load | 功能说明:查询表t记录集合的存储过程 | 入口参数:@type | 过程返回:返回所有记录集 | 维护记录:Y/A | 使用案例:SP_t_Load | 工作站名:RICHWAY-ZJ | 联系方式:zlp321001@hotmail.com | 创建日期:2006-08-31 12:26:37 +--------------------------------------+*/ CREATEPROC SP_t_Load @typevarchar(10) AS BEGIN SET NOCOUNT ON SELECT*FROM t WHERE type=@type SET NOCOUNT OFF END GO */