将数据库表中的数据生成Insert脚本的存储过程!!!

CREATE Procedure DBGenData
?@tableName? Varchar(100)?--表名
AS
DECLARE @columnName VARCHAR(100)??--列名
DECLARE @TypeName VARCHAR(100)??--数据类型
DECLARE @columns VARCHAR(3000)???--
DECLARE @columnsAndhead VARCHAR(3000)?--

SET @columnsAndhead = @#INSERT INTO @#+@tableName+@#(@#
SET @columns = @#@#

?--获取表的所有字段名称
??? DECLARE a? CURSOR FOR
?SELECT a.[NAME] AS ColumnName ,b.[NAME] AS TypeName
?FROM syscolumns a INNER JOIN systypes b ON a.xtype=b.xtype
?WHERE a.[id]=(SELECT [id] FROM sysobjects WHERE [NAME]=@tableName)

??? OPEN a
??? FETCH NEXT FROM a INTO @columnName ,@TypeName
??? WHILE @@FETCH_STATUS = 0
??? BEGIN
??IF @TypeName IN (@#bigint@#,@#bit@#,@#decimal@#,@#float@#,@#int@#,@#money@#,@#numeric@#,@#real@#,@#smallint@#,@#smallmoney@#,@#tinyint@#)
??BEGIN
???SET @columns = @columns + @#ISNULL(CAST(@#+@columnName +@# AS VARCHAR),@#@#@#@#)+@#@#,@#@#+@#
??END
??ELSE
??BEGIN
???SET @columns = @columns+? @#@#@#@#@#@#@#@#@#+? ISNULL(CAST(@#+@columnName +@# AS VARCHAR),@#@#@#@#)+ @#@#@#@#@#@#@#@#+@#@#,@#@#+@#
??END
??SET @columnsAndhead = @columnsAndhead + @columnName +@#,@#?????
??????? FETCH NEXT FROM a INTO @columnName ,@TypeName
??? END
?SELECT @columnsAndhead = left(@columnsAndhead,len(@columnsAndhead)-1) +@#) VALUES(@#@#@#
?SELECT @columns = left(@columns,len(@columns)-5)
??? CLOSE a?
??? DEALLOCATE a??
?
?exec(@#select @#@#@#+@columnsAndhead+@#+@#+@columns +@#+@#@#)@#@# as a from @#+@tableName)

GO

原文转自：http://www.ltesting.net