I wrote a procedure that basically does the same thing. It was not as robust as yours. We do a lot of stored procedures and triggers on big tables.
And before everyone complains about the use of cursors, it was a quick and dirty work around for a problem.
Use master
if exists (select * from dbo.sysobjects where id = object_id(N'sp_ScriptHelper') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure sp_ScriptHelper
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO
CREATE PROCEDURE sp_ScriptHelper
@tblName nvarchar(50)='',
@which int = '0',
@Prefix nvarchar(5)=''
AS
Declare @FldName as nvarchar(200)
Declare @fldtype as nvarchar(30)
Declare @fldlgth as int
if @which = 1 or @which = 0
Begin
print '/* Standard Declares for table ' + upper(@tblname) + '*/'
Declare tblfields cursor for
SELECT COLUMN_NAME, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns where TABLE_NAME = @tblName order by ORDINAL_POSITION
OPEN tblfields
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
WHILE (@@fetch_status <> -1)
BEGIN
if @fldtype<> 'timestamp'
BEGIN
if not @fldlgth is null
if not @Prefix is null
Print 'Declare @' + @Prefix + @fldname + ' as ' + @fldtype + ' (' + cast(@fldlgth as nvarchar(10)) + ')'
else
Print 'Declare @' + @fldname + ' as ' + @fldtype + ' (' + cast(@fldlgth as nvarchar(10)) + ')'
else
if not @Prefix is null
Print 'Declare @' + @Prefix + @fldname + ' as ' + @fldtype
else
Print 'Declare @' + @fldname + ' as ' + @fldtype
END
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
END --end loop
CLOSE tblfields
DEALLOCATE tblfields
Print ''
Print ''
Print ''
END
If @which = '0' or @which ='2'
Begin
print '/* Standard Variable Load from table ' + upper(@tblname) + '*/'
Print ''
Print ''
Print ''
Print 'Select '
Declare tblfields cursor for
SELECT COLUMN_NAME, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns where TABLE_NAME = @tblName order by ORDINAL_POSITION
OPEN tblfields
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
WHILE (@@fetch_status <> -1)
BEGIN
if @fldtype<> 'timestamp'
if not @Prefix is null
Print '@' + @Prefix + @fldname + ' = ' + @fldname + ','
else
Print '@' + @fldname + ' = ' + @fldname + ','
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
END --end loop
CLOSE tblfields
DEALLOCATE tblfields
Print 'FROM ' + upper(@tblname)
Print '/* WHERE clause goes here */'
Print ''
Print ''
Print ''
End
IF @which ='0' or @which = '3'
Begin
print '/* Standard Insert for table ' + upper(@tblname) + '*/'
Print ''
Print ''
Print ''
Print 'INSERT INTO ' + Upper(@tblname) + '('
Declare tblfields cursor for
SELECT COLUMN_NAME, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns where TABLE_NAME = @tblName order by ORDINAL_POSITION
OPEN tblfields
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
WHILE (@@fetch_status <> -1)
BEGIN
if @fldtype<> 'timestamp'
if not @Prefix is null
Print '@' + @Prefix + @fldname + ','
else
Print '@' + @fldname + ','
else
Print 'DEFAULT'
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
END --end loop
CLOSE tblfields
DEALLOCATE tblfields
Print ')'
Print ''
Print ''
Print ''
End
if @which = '0' or @which= '4'
Begin
print '/* Standard Update for table ' + upper(@tblname) + '*/'
Print ''
Print ''
Print ''
Print 'Update ' + upper(@tblname)
Print 'SET'
Declare tblfields cursor for
SELECT COLUMN_NAME, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns where TABLE_NAME = @tblName order by ORDINAL_POSITION
OPEN tblfields
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
WHILE (@@fetch_status <> -1)
BEGIN
if @fldtype<> 'timestamp'
if not @Prefix is null
Print @fldname + ' = @' + @Prefix + @fldname + ','
else
Print @fldname + ' = @' + @fldname + ','
else
Print @fldname + ' = DEFAULT'
FETCH NEXT FROM tblfields INTO @fldName, @fldtype, @fldlgth
END --end loop
CLOSE tblfields
DEALLOCATE tblfields
Print '/* Where statement goes here */'
End
SET NOCOUNT Off
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
exec sp_MS_marksystemobject sp_ScriptHelper
GO