updated http://www.sqlbook.com/scripts/AutoGenerateCRUDStoredProcs.txt

AutoGenerateCRUDStoredProcs.sql

-- sources: http://dba.stackexchange.com/questions/97188/how-to-output-more-than-4000-characters-in-sqlcmd/97350#97350
--			www.sqlbook.com/scripts/AutoGenerateCRUDStoredProcs.txt
-- Do we want to generate the SP definitions for every user defined
-- table in the database or just a single specified table?
-- Assign a blank string - '' for all tables or the table name for
-- a single table.
DECLARE @GenerateProcsFor VARCHAR(128) = ''

-- which database do we want to create the procs for?
-- Change both the USE and SET lines below to set the database name
-- to the required database.
USE Database
DECLARE @DatabaseName VARCHAR(128) = 'Database'

-- Is there a table name prefix i.e. 'tbl_' which we don't want
-- to include in our stored proc names?
DECLARE @TablePrefix VARCHAR(10) = ''

-- do SELECT * (1) or SELECT [ColumnName,...] (0)
DECLARE @UseSelectWildCard BIT = 0

-- prefix sprocs if you want
DECLARE @SprocPrefix VARCHAR(10) = ''

-- table names followed by operation (1) or GetTable (0)
DECLARE @TableNameFirst BIT = 0

-- ##########################################################
-- END CONFIG
-- ##########################################################
SET NOCOUNT ON
DECLARE TableCol CURSOR FOR 
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN
	INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.Table_Catalog = @DatabaseName
	AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION

DECLARE @TableSchema VARCHAR(128), @TableName VARCHAR(128), @ColumnName VARCHAR(128), @DataType VARCHAR(30), @CharLength INT, @CharLengthText VARCHAR(10)
DECLARE @ColumnNameCleaned VARCHAR(128), @CurrentTable VARCHAR(128) = '', @FirstColumnName VARCHAR(128), @FirstColumnNameCleaned VARCHAR(128), @FirstColumnIdentity BIT
DECLARE @ObjectName VARCHAR(117), @TablePrefixLength INT = LEN(@TablePrefix)
DECLARE @LIST NVARCHAR(MAX), @ARGS NVARCHAR(MAX), @SELECT NVARCHAR(MAX), @INSERT NVARCHAR(MAX), @VALUES VARCHAR(MAX), @UPDATE NVARCHAR(MAX), @DELETE NVARCHAR(MAX)
DECLARE @CREATELIST NVARCHAR(128), @CREATESELECT NVARCHAR(128), @CREATEINSERT NVARCHAR(128), @CREATEUPDATE NVARCHAR(128), @CREATEDELETE NVARCHAR(128)
DECLARE @CREATE VARCHAR(29) = 'CREATE PROC [dbo].[' + @SprocPrefix 
DECLARE @AS VARCHAR(20) = 'AS' + CHAR(10) + 'SET NOCOUNT ON' + CHAR(10) + CHAR(10)
DECLARE @GO VARCHAR(4) = CHAR(10) + 'GO' + CHAR(10)

  DECLARE @TextToDisplay NVARCHAR(MAX),
          @Buffer NVARCHAR(4000),
          @BufferLength INT,
          @StartIndex INT = 1,
          @NextNewlineIndex INT;

OPEN TableCol
FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength
WHILE @@FETCH_STATUS = 0 BEGIN
	SET @ColumnNameCleaned = REPLACE(@ColumnName, ' ', '')
	IF @CharLength = -1
		SET @CharLengthText = 'MAX'
	ELSE
		SET @CharLengthText = CAST(@CharLength AS VARCHAR(10)) 

	IF @TableName <> @CurrentTable BEGIN
		IF @CurrentTable <> '' BEGIN
			IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
				SET @LIST = @LIST + CHAR(10) + 'FROM [' + @CurrentTable + ']' + CHAR(10)
				
				SET @SELECT = @SELECT + CHAR(10) + 'FROM [' + @CurrentTable + ']' + CHAR(10)
				SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + @FirstColumnNameCleaned + CHAR(10)

				SET @ARGS = @ARGS + CHAR(10)					

				SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + CHAR(10) + CHAR(9) + ')' + CHAR(10)
				SET @VALUES = SUBSTRING(@VALUES, 0, LEN(@VALUES) - 1) + CHAR(10) + CHAR(9) + ')' + CHAR(10)
				SET @INSERT = @INSERT + @VALUES
				IF @FirstColumnIdentity = 1
					SET @INSERT = @INSERT + 'RETURN SCOPE_IDENTITY()' + CHAR(10)
				SET @INSERT = @CREATEINSERT + @ARGS + @AS + @INSERT
				
				SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE) - 1) + CHAR(10) + CHAR(9) + 'WHERE [' + @FirstColumnName + '] = @' + @FirstColumnNameCleaned + CHAR(10)
				SET @UPDATE = @CREATEUPDATE + @ARGS + @AS + @UPDATE
				
		SET @TextToDisplay = @LIST + @GO + @SELECT + @GO + @INSERT + @GO + @UPDATE + @GO + @DELETE + @GO
  WHILE (1 = 1)
  BEGIN
    SET @Buffer = SUBSTRING(@TextToDisplay, @StartIndex, 4000);
    SET @BufferLength = (DATALENGTH(@Buffer) / 2);
  
    IF (@BufferLength < 4000)
    BEGIN
      BREAK;
    END;
 
    SET @NextNewlineIndex = CHARINDEX(NCHAR(10), @Buffer COLLATE Latin1_General_100_BIN2);
 
    IF (@NextNewlineIndex > 0)
    BEGIN
      PRINT SUBSTRING(@Buffer, 1, (@NextNewlineIndex - 1));
      SET @StartIndex += @NextNewlineIndex;
    END;
    ELSE
    BEGIN
      PRINT @Buffer;
      SET @StartIndex += @BufferLength;
    END;
  END;
 
  IF (DATALENGTH(@Buffer) > 0)
  BEGIN
    PRINT @Buffer;
  END;
  			END
		END
		
		SET @CurrentTable = @TableName
		SET @FirstColumnName = @ColumnName
		SET @FirstColumnNameCleaned = REPLACE(@FirstColumnName, ' ', '')
		SET @FirstColumnIdentity = COLUMNPROPERTY(OBJECT_ID(@TableSchema + '.' + @TableName), @FirstColumnName, 'IsIdentity')
		
		IF @TablePrefixLength > 0 
			IF SUBSTRING(@CurrentTable, 1, @TablePrefixLength) = @TablePrefix 
				SET @ObjectName = RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
			 ELSE 
				SET @ObjectName = @CurrentTable
		ELSE 
			SET @ObjectName = @CurrentTable

		IF @TableNameFirst = 1 BEGIN
			SET @CREATELIST = @CREATE + @ObjectName + '_Select_All]'
			SET @CREATESELECT = @CREATE + @ObjectName + '_Select]'
			SET @CREATEINSERT = @CREATE + @ObjectName + '_Insert]'
			SET @CREATEUPDATE = @CREATE + @ObjectName + '_Update]'
			SET @CREATEDELETE = @CREATE + @ObjectName + '_Delete]'
		END ELSE BEGIN
			SET @CREATELIST = @CREATE + 'Get' + @ObjectName + 's]'
			SET @CREATESELECT = @CREATE + 'Get' + @ObjectName + ']'
			SET @CREATEINSERT = @CREATE + 'Add' + @ObjectName + ']'
			SET @CREATEUPDATE = @CREATE + 'Update' + @ObjectName + ']'
			SET @CREATEDELETE = @CREATE + 'Delete' + @ObjectName + ']'
		END
		
		IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN		
			SET @LIST = @CREATELIST + CHAR(10) + @AS
			IF @UseSelectWildcard = 1 
				SET @LIST = @LIST + 'SELECT * '
			ELSE 
				SET @LIST = @LIST + 'SELECT [' + @ColumnName + ']'
	
			SET @SELECT = @CREATESELECT + CHAR(10)
			SET @SELECT = @SELECT + CHAR(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
			IF @DataType IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR')
				SET @SELECT = @SELECT + '(' + @CharLengthText + ')'
			SET @SELECT = @SELECT + CHAR(10) + @AS
			IF @UseSelectWildcard = 1 
				SET @SELECT = @SELECT + 'SELECT * '
			ELSE 
				SET @SELECT = @SELECT + 'SELECT [' + @ColumnName + ']'
	
			SET @ARGS = CHAR(10) + CHAR(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
			IF @DataType IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR') 
				SET @ARGS = @ARGS + '(' + @CharLengthText + ')'
			
			SET @INSERT = 'INSERT INTO [' + @TableName + '] (' + CHAR(10)
			SET @VALUES = CHAR(9) + 'VALUES (' + CHAR(10)
			IF @FirstColumnIdentity = 0	BEGIN
				SET @INSERT = @INSERT + CHAR(9) + CHAR(9) + '[' + @ColumnName + '],' + CHAR(10)
				SET @VALUES = @VALUES + CHAR(9) + CHAR(9) + '@' + @ColumnNameCleaned + ',' + CHAR(10)
			END
	
			SET @UPDATE = 'UPDATE [' + @TableName + '] SET ' + CHAR(10)
	
			SET @DELETE = @CREATEDELETE + CHAR(10)
			SET @DELETE = @DELETE + CHAR(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
			IF @DataType IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR')
				SET @DELETE = @DELETE + '(' + @CharLengthText + ')'
			SET @DELETE = @DELETE + CHAR(10) + @AS
			SET @DELETE = @DELETE + 'DELETE FROM [' + @TableName + ']' + CHAR(10)
			SET @DELETE = @DELETE + 'WHERE [' + @ColumnName + '] = @' + @ColumnNameCleaned + CHAR(10)
		END	
	END
	ELSE BEGIN
		IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
			IF @UseSelectWildCard = 0 BEGIN
				SET @LIST = @LIST + ', ' + CHAR(10) + CHAR(9) + '[' + @ColumnName + ']'
				SET @SELECT = @SELECT + ', ' + CHAR(10) + CHAR(9) + '[' + @ColumnName + ']'
			END
			
			SET @ARGS = @ARGS + ',' + CHAR(10) + CHAR(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
			IF @DataType IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR', 'VARBINARY', 'BINARY') 
				SET @ARGS = @ARGS + '(' + @CharLengthText + ')'
	
			SET @INSERT = @INSERT + CHAR(9) + CHAR(9) + '[' + @ColumnName + '],' + CHAR(10)
			SET @VALUES = @VALUES + CHAR(9) + CHAR(9) + '@' + @ColumnNameCleaned + ',' + CHAR(10)
			
			SET @UPDATE = @UPDATE + CHAR(9) + CHAR(9) + '[' + @ColumnName + '] = @' + @ColumnNameCleaned + ',' + CHAR(10)
		END
	END
	FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength
END
CLOSE TableCol
DEALLOCATE TableCol
IF @CurrentTable <> '' BEGIN
	IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
		SET @LIST = @LIST + CHAR(10) + 'FROM [' + @CurrentTable + ']' + CHAR(10)
		
		SET @SELECT = @SELECT + CHAR(10) + 'FROM [' + @CurrentTable + ']' + CHAR(10)
		SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + @FirstColumnNameCleaned + CHAR(10)
		
		SET @ARGS = @ARGS + CHAR(10)
				
		SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + CHAR(10) + CHAR(9) + ')' + CHAR(10)
		SET @VALUES = SUBSTRING(@VALUES, 0, LEN(@VALUES) - 1) + CHAR(10) + CHAR(9) + ')' + CHAR(10)
		SET @INSERT = @INSERT + @VALUES
		IF @FirstColumnIdentity = 1
			SET @INSERT = @INSERT + 'RETURN SCOPE_IDENTITY()' + CHAR(10)
		SET @INSERT = @CREATEINSERT + @ARGS + @AS + @INSERT
		
		SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE) - 1) + CHAR(10) + CHAR(9) + 'WHERE [' + @FirstColumnName + '] = @' + @FirstColumnNameCleaned + CHAR(10)
		SET @UPDATE = @CREATEUPDATE + @ARGS + @AS + @UPDATE
		
		SET @TextToDisplay = @LIST + @GO + @SELECT + @GO + @INSERT + @GO + @UPDATE + @GO + @DELETE + @GO
  WHILE (1 = 1)
  BEGIN
    SET @Buffer = SUBSTRING(@TextToDisplay, @StartIndex, 4000);
    SET @BufferLength = (DATALENGTH(@Buffer) / 2);
  
    IF (@BufferLength < 4000)
    BEGIN
      BREAK;
    END;
 
    SET @NextNewlineIndex = CHARINDEX(NCHAR(10), @Buffer COLLATE Latin1_General_100_BIN2);
 
    IF (@NextNewlineIndex > 0)
    BEGIN
      PRINT SUBSTRING(@Buffer, 1, (@NextNewlineIndex - 1));
      SET @StartIndex += @NextNewlineIndex;
    END;
    ELSE
    BEGIN
      PRINT @Buffer;
      SET @StartIndex += @BufferLength;
    END;
  END;
 
  IF (DATALENGTH(@Buffer) > 0)
  BEGIN
    PRINT @Buffer;
  END;
	END
END