为数据库中所有表生成创建存储数据的存储过程代码

为数据库中所有表生成创建存储数据的存储过程代码
Author：CoolRzs
QQ    ：61437524
E-Mail：[e-mail]
Date  ：13:30 2006-12-18

        没有系统学过 SQL Server 知识，Transact-SQL 更是只在书本中东拼西凑的看了点，昨晚突然想这样写段代码来为数据库中所有表存储数据生成存储过程；也不知道这样写对不对，合不合规范，主要是想借此学习一下游标的使用；不过想要的结果倒是得到了，贴在这里，希望道上的高手给指点指点。

/*
过 程 名 : sp_CreateStoreProcedure
功能描述 : 为数据库中所有表生成创建存储数据的存储过程代码
作    者 : CoolRzs
*/
DECLARE @s VARCHAR(8000), @v VARCHAR(8000), @outfield varchar(20), @tabname VARCHAR(20), @tabid INT, @fieldname VARCHAR(20), @Type VARCHAR(20), @Length VARCHAR(4)
SET @tabname = ''
SET @tabid = 0
DECLARE cur_TabName SCROLL CURSOR FOR
SELECT id, OBJECT_NAME(id) AS tabname FROM sysobjects WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
OPEN cur_TabName
FETCH FIRST FROM cur_TabName INTO @tabid, @tabname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @s= ''
PRINT '/* ==sp_Store' + @tabname + ' Begin== */'
PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[sp_Store' + @tabname + ']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure [dbo].[sp_Store' + @tabname + ']'
PRINT 'GO'
PRINT 'SET QUOTED_IDENTIFIER OFF'
PRINT 'GO'
PRINT 'SET ANSI_NULLS OFF '
PRINT 'GO'
PRINT 'CREATE PROCEDURE sp_Store' + @tabname
DECLARE cur_FieldName SCROLL CURSOR FOR
Select c.name, t.name, c.length from syscolumns c inner join systypes t on c.xtype = t.xtype where id = @tabid order by c.colorder
OPEN cur_FieldName
FETCH FIRST FROM cur_FieldName INTO @fieldname, @Type, @Length
WHILE @@FETCH_STATUS = 0
BEGIN
IF (LOWER(@fieldname) = 'id') AND (LOWER(@Type) = 'int')
SET @outfield =  '@' + @fieldname + ' ' + @Type + ' output'
ELSE
BEGIN
IF (LOWER(@Type) = 'varchar')
--这里仅判断了 varchar 类型，实际应用中可能会有更多需要这样判断的类型；
BEGIN
PRINT '@' + @fieldname + ' ' + @Type + '(' + @Length + '),'
END
ELSE
BEGIN
PRINT '@' + @fieldname + ' ' + @Type + ','
END
END
IF (LOWER(@fieldname) <> 'id')
IF (@s = '')
BEGIN
SET @s = @fieldname
SET @v = '@' + @fieldname
END
ELSE
BEGIN
SET @s = @s + ', ' + @fieldname
SET @v = @v + ', @' + @fieldname
END
FETCH NEXT FROM cur_FieldName INTO @fieldname, @Type, @Length
END
CLOSE cur_FieldName
DEALLOCATE cur_FieldName
PRINT @outfield
PRINT 'AS'
PRINT 'INSERT INTO ' + @tabname + ' (' + @s + ') '
PRINT 'VALUES (' + @v + ')'
PRINT 'SET @id =  @@IDENTITY'
PRINT 'GO'
PRINT 'SET QUOTED_IDENTIFIER OFF '
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON '
PRINT 'GO'
PRINT '/* ==sp_Store' + @tabname + ' End== */'
PRINT ''
FETCH NEXT FROM cur_TabName INTO @tabid, @tabname
END
CLOSE cur_TabName
DEALLOCATE cur_TabName

--＝转载请保持以上信息的完整，谢谢！＝--

--[完]