CREATE TABLE dbo.tbl
(
	bi BIGINT,
	d DECIMAL (18, 2),
	f FLOAT,
	i INT,
	m MONEY,
	n NUMERIC (15, 3),
	r REAL,
	si SMALLINT,
	sm SMALLMONEY,
	ti TINYINT,
	sChar CHAR(10),
	sVarchar VARCHAR(100),
	sMax VARCHAR (MAX),
	id INT IDENTITY(1, 1)
)
GO

SET NOCOUNT ON
GO
IF OBJECT_ID('dbo.fGetColumnList') IS NOT NULL
	DROP FUNCTION dbo.fGetColumnList
GO
CREATE FUNCTION dbo.fGetColumnList( @TableId INT, @VariableList BIT )
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @ColumnList VARCHAR(MAX)
	SET @ColumnList = ''

	SELECT @ColumnList = @ColumnList + CASE @VariableList WHEN 1 THEN '@' ELSE '' END + LOWER(c.NAME) + ','
	FROM sys.columns c
	WHERE c.[object_id] = @TableId
	AND c.is_identity = 0
	ORDER BY c.column_id

	SET @ColumnList = SUBSTRING(@ColumnList,1,LEN(@ColumnList)-1)

	RETURN @ColumnList

END
GO
IF OBJECT_ID('dbo.GenerateInsUpdateScript') IS NOT NULL
	DROP PROCEDURE dbo.GenerateInsUpdateScript
GO
CREATE PROCEDURE dbo.GenerateInsUpdateScript(
	@TableId INT
)AS
SET NOCOUNT ON
BEGIN
	SELECT t.NAME TableName, 1 OrderBy, 0 column_id,
	'IF OBJECT_ID(''pInsUpd_' + t.NAME + ''') IS NOT NULL' + CHAR(13) + CHAR(10) +
	'	DROP PROCEDURE pInsUpd_' + LOWER(t.NAME) + CHAR(13) + CHAR(10) +
	'GO' + CHAR(13) + CHAR(10) +
	'CREATE PROCEDURE pInsUpd_' + LOWER(t.NAME) + '('
	FROM sys.tables t
	WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
	AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
	UNION ALL
	SELECT t.NAME TableName, 2 OrderBy, c.column_id, '	@' + LOWER(c.NAME) + ' ' + typ.NAME +
		CASE
			--No Precision or scale
			WHEN typ.NAME IN ('bigint','int','smallint','tinyint','bit','datetime','smalldatetime','smallmoney','money','real','text') THEN ''
			--Precision
			ELSE
				CASE WHEN c.max_length = -1 THEN '(MAX'
				ELSE
					'(' + CAST(COLUMNPROPERTY(c.[object_id],c.NAME,'Precision') AS VARCHAR(10)) +
					--and Scale
					CASE WHEN typ.NAME IN ('decimal','numeric') THEN ',' + CAST(COLUMNPROPERTY(c.[object_id],c.NAME,'Scale') AS VARCHAR(10)) ELSE '' END
				END +
			')'
		END +
		CASE WHEN c.column_id < t.max_column_id_used THEN ','
		ELSE
			CHAR(13) + CHAR(10) +
			')' + CHAR(13) + CHAR(10) +
			'AS' + CHAR(13) + CHAR(10) +
			'SET NOCOUNT ON' + CHAR(13) + CHAR(10) +
			'BEGIN' 
		END
	FROM sys.tables t
	INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
	INNER JOIN sys.types typ ON typ.user_type_id = c.user_type_id
	WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
	AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
	UNION ALL
	SELECT OBJECT_NAME(c.[object_id]) TableName, 3 OrderBy, NULL column_id,
		'	IF @' + LOWER(c.NAME) + ' = 0'
	FROM sys.tables t
	INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.is_identity = 1
	WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
	UNION ALL
	SELECT t.NAME TableName, 4 OrderBy, null,
			'		INSERT INTO ' + OBJECT_NAME(t.[object_id]) + '(' + dbo.fGetColumnList(t.OBJECT_ID, 0) + ')'
	FROM sys.tables t
	WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
	AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
	UNION ALL
	SELECT t.NAME TableName, 5 OrderBy, null,
			'			VALUES(' + dbo.fGetColumnList(t.OBJECT_ID, 1) + ')'
	FROM sys.tables t
	WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
	AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
	UNION ALL
	SELECT t.NAME TableName, 6 OrderBy, c.column_id,
			CASE WHEN c.column_id = MinMaxColumn.MinColumn THEN '	ELSE' + CHAR(13) + CHAR(10) + '		UPDATE ' + t.[name] + ' SET' + CHAR(13) + CHAR(10) ELSE '' END +
			'			' + LOWER(c.NAME) + ' = @' + LOWER(c.NAME) +
			CASE WHEN c.column_id < MinMaxColumn.MaxColumn THEN ',' ELSE '' END
	FROM sys.tables t
	INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.is_identity = 0
	INNER JOIN (
		SELECT c.[object_id], MIN(c.column_id) MinColumn, MAX(c.column_id) MaxColumn
		FROM sys.columns c
		WHERE c.is_identity = 0
		GROUP BY c.[object_id]
	) MinMaxColumn ON MinMaxColumn.[object_id] = c.[object_id]
	WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
	AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
	UNION ALL
	SELECT OBJECT_NAME(c.[object_id]) TableName, 7 OrderBy, NULL column_id,
			'		WHERE ' + LOWER(c.NAME) + ' = @' + LOWER(c.NAME) + CHAR(13) + CHAR(10) + 'END' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
	FROM sys.tables t
	INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.is_identity = 1
	WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
	AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
	ORDER BY TableName, OrderBy, column_id
END
GO