declare @SPPrefix varchar(5)
declare @Author nvarchar (250)
declare @Schema nvarchar(100)

declare @createInsert bit
declare @createupdate bit
declare @createdelete bit
declare @createSelect bit
declare @createSelectByPrimaryKey bit

declare @DisplaySQL bit
declare @ExecuteSQL bit

-------------------------------------------------------------------------
--
-- Script settings begin
--
-- Original location : http://www.glossover.co.uk/archives/349
--
-- Please update the script settings as needed. 
-------------------------------------------------------------------------

set @Author = SUSER_NAME()
set @SPPrefix   = 'usp_'
set @Schema = 'dbo'

set @createInsert = 1
set @createUpdate = 1
set @createDelete = 1
set @createSelect = 1
set @createSelectByPrimaryKey = 1

set @DisplaySQL = 0
set @ExecuteSQL = 1

-------------------------------------------------------------------------
--
-- Script settings end
--
-------------------------------------------------------------------------

-------------------------------------------------------------------------
--
-- Do not edit below this point. 
--
-------------------------------------------------------------------------

set NOCOUNT on 

declare @TableName nvarchar(max) = ''

declare cursorTables CURSOR FOR  
	select
		TABLE_NAME 
	from INFORMATION_SCHEMA.TABLES 
	where TABLE_TYPE = 'BASE TABLE'
	and   TABLE_NAME not like '%_Audit'
	and   TABLE_NAME != 'sysdiagrams'
    and   TABLE_SCHEMA = @Schema
	order by TABLE_NAME
 
open cursorTables   
fetch next from cursorTables into @TableName   

while @@fetch_status = 0   
begin   

	create table #TableInformation
	(
		myRowCount int, 
		tableName  nvarchar(max), 
		schemaName nvarchar(max), 
		columnName nvarchar(max), 
		isPrimaryKey bit, 
		dataType nvarchar(max), 
		defaultValue nvarchar(max)
	)

	insert into #TableInformation
	(
		myRowCount, 
		SchemaName, 
		tableName, 
		columnName, 
		DataType, 
		isPrimaryKey,
		defaultValue
	)
	SELECT
	INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION,
	INFORMATION_SCHEMA.COLUMNS.table_schema, 
	INFORMATION_SCHEMA.COLUMNS.TABLE_NAME, 
	INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
	information_schema.COLUMNS.DATA_TYPE + ' ' +
	case 
		when information_schema.COLUMNS.DATA_TYPE IN (N'numeric', N'decimal') then 
			'(' + cast( information_schema.COLUMNS.NUMERIC_PRECISION as varchar ) + ', ' + cast( information_schema.COLUMNS.NUMERIC_SCALE as varchar ) + ') '
		when information_schema.COLUMNS.DATA_TYPE IN (N'datetime2', N'datetimeoffset', N'time') then  
			'(' + cast( information_schema.COLUMNS.DATETIME_PRECISION as varchar ) + ') '
		when information_schema.COLUMNS.DATA_TYPE IN (N'geography', N'geometry', N'hierarchyid', N'image', N'ntext', N'sql_variant', N'text', N'xml') then  
			''
		else     
			case when information_schema.COLUMNS.CHARACTER_MAXIMUM_LENGTH is not null then REPLACE('('+ CAST( information_schema.COLUMNS.CHARACTER_MAXIMUM_LENGTH as varchar)  +')', '(-1)', '(max)') else '' end 
	end as DataTypeLength,

	case when INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE is not null then 1 else 0 end as IsPrimaryKey,

	information_schema.COLUMNS.COLUMN_DEFAULT as  defaultValue

	FROM INFORMATION_SCHEMA.COLUMNS
		LEFT OUTER JOIN information_schema.KEY_COLUMN_USAGE ON
			INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=information_schema.KEY_COLUMN_USAGE.TABLE_NAME AND 
			INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME=information_schema.KEY_COLUMN_USAGE.COLUMN_NAME AND 
			INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION=information_schema.KEY_COLUMN_USAGE.ORDINAL_POSITION
		LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON
			information_schema.KEY_COLUMN_USAGE.TABLE_NAME=information_schema.TABLE_CONSTRAINTS.TABLE_NAME AND 
			information_schema.KEY_COLUMN_USAGE.CONSTRAINT_NAME=information_schema.TABLE_CONSTRAINTS.CONSTRAINT_NAME
	WHERE information_schema.COLUMNS.TABLE_NAME= @TableName
	and   information_schema.COLUMNS.DATA_TYPE != 'timestamp' 
	and   INFORMATION_SCHEMA.COLUMNS.table_schema = @Schema 

	ORDER BY information_schema.COLUMNS.ORDINAL_POSITION

	declare @PKColumn as nvarchar(max) 
	declare @PKColumnType as nvarchar(max) 

	declare @IntoList as nvarchar(max) 
	declare @ParamList as nvarchar(max) 
	declare @IntoListValues as nvarchar(max) 
	declare @Line nvarChar(max)

	declare @SPText nvarChar(max)

	declare @i int
	declare @max int

	set @IntoListValues = ''
	set @IntoList = ''
	set @Line = ''
	set @SPText = ''
	set @ParamList = ''

	--
	--Set Schema & primary key values
	--
	
	set @PKColumn = null 
	set @PKColumnType = null 
	
	select @PKColumn = columnName, @PKColumnType = rtrim(ltrim(dataType)) from #TableInformation where isPrimaryKey = 1 

	if @PKColumn is null 
	begin
		print '----------------------------------------------------------------------------------------------------'
		print '--There is no primary key on the "' + @TableName + '" the CRUD process will not be run for this table.'
		print '----------------------------------------------------------------------------------------------------'
	end
	else
	begin
		
		--
		-- Insert stored procedure.
		--
		if @createInsert = 1
		begin
	    
		set @IntoListValues = ''
		set @IntoList = ''
		set @Line = ''
		set @SPText = ''
		set @ParamList = ''
	    
		select @i = min(myRowCount), @max = max(myRowCount) from #TableInformation where isPrimaryKey = 0
		while @i <= @max 
		begin    
			set @Line = ''
			if @i = @max 
			begin
				 select  
					 @ParamList = @ParamList + '
		@' + columnName + ' ' + DataType + ' = ' + isnull(replace(replace(defaultValue, '(', ''), ')', ''), 'NULL')
				 from #TableInformation where myRowCount = @i 
			end
			else
			begin
				 select  
					 @ParamList = @ParamList + '
		@' + columnName + ' ' + DataType + ' = ' + isnull(replace(replace(defaultValue, '(', ''), ')', ''), 'NULL') + ', '
				 from #TableInformation where myRowCount = @i 
			end
		    
			set @i = @i + 1
		end

		select @i = min(myRowCount), @max = max(myRowCount) from #TableInformation where isPrimaryKey = 0
		while @i <= @max 
		begin    
			set @Line = ''
			if @i = @max 
			begin
				 select  
					 @IntoList = @IntoList + '[' + columnName + ']',
					 @IntoListValues = @IntoListValues + '@' + columnName + ''
				 from #TableInformation where myRowCount = @i 
			end
			else
			begin
				 select  
					 @IntoList = @IntoList + '[' + columnName + '], ',
					 @IntoListValues = @IntoListValues + '@' + columnName + ', '
				 from #TableInformation where myRowCount = @i 
			end
		    
			set @i = @i + 1
		end

		set @SPText = 'if exists ( select * from sys.objects where object_id = object_id(N''['+ @Schema +'].['+ @SPPrefix + @TableName +'_Insert]'') AND type in (N''P'', N''PC''))
		drop procedure ['+ @Schema +'].[' + @SPPrefix + @TableName + '_Insert] '

		if @DisplaySQL = 1
			print @SPText + '
go

'

		if @ExecuteSQL  = 1
			EXEC sp_executesql @SPText
	    
		set @SPText = 'create procedure ['+ @Schema +'].[' + @SPPrefix + @TableName + '_Insert] ' + @ParamList + '
	as 
		---------------------------------------------------------------------------------
		--Script history 
		--Script name    ' + @SPPrefix + @TableName + '_Insert()
		--Creation date: '+ CONVERT(VARCHAR(20), GETDATE(), 113) +'
		--Author:        '+ @Author +'
		--
		--Comment date:  '+ CONVERT(VARCHAR(20), GETDATE(), 113) +'
		--Comment:       Generated via the CRUD generation process. 
		--               
		---------------------------------------------------------------------------------

		set NOCOUNT on 
		set XACT_ABORT on  
		
		declare @errorNumber int
		declare @rowCount int
		
		begin transaction
		
		insert into ['+ @TableName +'] 
		( ' + @IntoList + ' ) 
		values 
		( ' + @IntoListValues +' )
		
		set @errorNumber = @@ERROR 
		
		if @errorNumber != 0
			goto error_routine
		
		commit
			return 0

		error_routine:
			rollback transaction
			return @errorNumber	
	'
			
		if @DisplaySQL = 1
			print @SPText + '
	go

	'

		if @ExecuteSQL  = 1
			EXEC sp_executesql @SPText
		end

		--
		-- Update stored procedure. 
		--
		if @createUpdate = 1
		begin
	    
		set @IntoListValues = ''
		set @IntoList = ''
		set @Line = ''
		set @SPText = ''
		set @ParamList = ''
	    
		select @i = min(myRowCount), @max = max(myRowCount) from #TableInformation where isPrimaryKey = 0
		while @i <= @max 
		begin    
			set @Line = ''
			if @i = @max 
			begin
				 select  
					 @ParamList = @ParamList + '
		@' + columnName + ' ' + DataType + ' = ' + isnull(replace(replace(defaultValue, '(', ''), ')', ''), 'NULL')
				 from #TableInformation where myRowCount = @i 
			end
			else
			begin
				 select  
					 @ParamList = @ParamList + '
		@' + columnName + ' ' + DataType + ' = ' + isnull(replace(replace(defaultValue, '(', ''), ')', ''), 'NULL') + ', '
				 from #TableInformation where myRowCount = @i 
			end
		    
			set @i = @i + 1
		end

		select @i = min(myRowCount), @max = max(myRowCount) from #TableInformation where isPrimaryKey = 0
		while @i <= @max 
		begin    
			set @Line = ''
			if @i = @max 
			begin
				 select  
					 @IntoList = @IntoList + '[' + columnName + '] = @' + columnName 
				 from #TableInformation where myRowCount = @i 
			end
			else
			begin
				 select  
    				 @IntoList = @IntoList + '[' + columnName + '] = @' + columnName + ', 
			' 
				 from #TableInformation where myRowCount = @i 
			end
		    
			set @i = @i + 1
		end

		set @SPText = 'if exists ( select * from sys.objects where object_id = object_id(N''['+ @Schema +'].['+ @SPPrefix + @TableName +'_Update]'') AND type in (N''P'', N''PC''))
		drop procedure ['+ @Schema +'].[' + @SPPrefix + @TableName + '_Update] '

		if @DisplaySQL = 1
			print @SPText + '
go
'

		if @ExecuteSQL  = 1
			EXEC sp_executesql @SPText
	    
		set @SPText = 'create procedure ['+ @Schema +'].[' + @SPPrefix + @TableName + '_Update] 
		@' + @PKColumn + ' ' + @PKColumnType + ', ' + @ParamList + '
	as 
		---------------------------------------------------------------------------------
		--Script history 
		--Script name    ' + @SPPrefix + @TableName + '_Update()
		--Creation date: '+ CONVERT(VARCHAR(20), GETDATE(), 113) +'
		--Author:        '+ @Author +'
		--
		--Comment date:  '+ CONVERT(VARCHAR(20), GETDATE(), 113) +'
		--Comment:       Generated via the CRUD generation process. 
		--               
		---------------------------------------------------------------------------------

		set NOCOUNT on 
		set XACT_ABORT on  
		
		declare @errorNumber int
		declare @rowCount int
		
		begin transaction
		
		update ['+ @TableName +'] set
			' + @IntoList + '
		where ['+ @PKColumn +'] = @'+ @PKColumn +'
		
		set @errorNumber = @@ERROR 
		
		if @errorNumber != 0
			goto error_routine
		
		commit
			return 0

		error_routine:
			rollback transaction
			return @errorNumber	
	'
			
		if @DisplaySQL = 1
			print @SPText + '
	go

	'

		if @ExecuteSQL  = 1
			EXEC sp_executesql @SPText
		end

		--
		-- Delete stored procedure. 
		--
		if @createdelete = 1
		begin
	    
		set @IntoListValues = ''
		set @IntoList = ''
		set @Line = ''
		set @SPText = ''
		set @ParamList = ''
	    
		set @SPText = 'if exists ( select * from sys.objects where object_id = object_id(N''['+ @Schema +'].['+ @SPPrefix + @TableName +'_Delete]'') AND type in (N''P'', N''PC''))
		drop procedure ['+ @Schema +'].[' + @SPPrefix + @TableName + '_Delete] '
		

		if @DisplaySQL = 1
			print @SPText + '
	go

	'

		if @ExecuteSQL  = 1
			EXEC sp_executesql @SPText
	    
		set @SPText = 'create procedure ['+ @Schema +'].[' + @SPPrefix + @TableName + '_Delete] 
		@' + @PKColumn + ' ' + @PKColumnType + '
	as 
		---------------------------------------------------------------------------------
		--Script history 
		--Script name    ' + @SPPrefix + @TableName + '_Delete()
		--Creation date: '+ CONVERT(VARCHAR(20), GETDATE(), 113) +'
		--Author:        '+ @Author +'
		--
		--Comment date:  '+ CONVERT(VARCHAR(20), GETDATE(), 113) +'
		--Comment:       Generated via the CRUD generation process. 
		--               
		---------------------------------------------------------------------------------

		set NOCOUNT on 
		set XACT_ABORT on  
		
		declare @errorNumber int
		declare @rowCount int
		
		begin transaction
		
		delete from ['+ @TableName +'] 
		where ['+ @PKColumn +'] = @'+ @PKColumn +'
		
		set @errorNumber = @@ERROR 
		
		if @errorNumber != 0
			goto error_routine
		
		commit
			return 0

		error_routine:
			rollback transaction
			return @errorNumber	
	'
			
		if @DisplaySQL = 1
			print @SPText + '
	go

	'

		if @ExecuteSQL  = 1
			EXEC sp_executesql @SPText
		end

		--
		-- Select stored procedure. 
		--
		if @createSelect = 1
		begin
	    
		set @IntoListValues = ''
		set @IntoList = ''
		set @Line = ''
		set @SPText = ''
		set @ParamList = ''
	    

		select @i = min(myRowCount), @max = max(myRowCount) from #TableInformation where isPrimaryKey = 0
		while @i <= @max 
		begin    
			set @Line = ''
			if @i = @max 
			begin
				 select  
					 @IntoList = @IntoList + '
			[' + columnName + '] '
				 from #TableInformation where myRowCount = @i 
			end
			else
			begin
				 select  
    				 @IntoList = @IntoList + '
			[' + columnName + '], ' 
				 from #TableInformation where myRowCount = @i 
			end
		    
			set @i = @i + 1
		end

		set @SPText = 'if exists ( select * from sys.objects where object_id = object_id(N''['+ @Schema +'].['+ @SPPrefix + @TableName +'_Select]'') AND type in (N''P'', N''PC''))
		drop procedure ['+ @Schema +'].[' + @SPPrefix + @TableName + '_Select] '

		if @DisplaySQL = 1
			print @SPText + '
	go

	'

		if @ExecuteSQL  = 1
			EXEC sp_executesql @SPText
	    
		set @SPText = 'create procedure ['+ @Schema +'].[' + @SPPrefix + @TableName + '_Select] 
	as 
		---------------------------------------------------------------------------------
		--Script history 
		--Script name    ' + @SPPrefix + @TableName + '_Select()
		--Creation date: '+ CONVERT(VARCHAR(20), GETDATE(), 113) +'
		--Author:        '+ @Author +'
		--
		--Comment date:  '+ CONVERT(VARCHAR(20), GETDATE(), 113) +'
		--Comment:       Generated via the CRUD generation process. 
		--               
		---------------------------------------------------------------------------------

		set NOCOUNT on 
		set XACT_ABORT on  
		
		declare @errorNumber int
		declare @rowCount int
		
		begin transaction
		
		select 
			[' + @PKColumn + '], '+ @IntoList +'
		from 
			['+ @TableName +'] (nolock)
		
		set @errorNumber = @@ERROR 
		
		if @errorNumber != 0
			goto error_routine
		
		commit
			return 0

		error_routine:
			rollback transaction
			return @errorNumber	
	'
			
		if @DisplaySQL = 1
			print @SPText + '
	go

	'

		if @ExecuteSQL  = 1
			EXEC sp_executesql @SPText
		end

		--
		-- Select by primary key stored procedure. 
		--
		if @createSelectByPrimaryKey = 1
		begin
	    
		set @IntoListValues = ''
		set @IntoList = ''
		set @Line = ''
		set @SPText = ''
		set @ParamList = ''
	    

		select @i = min(myRowCount), @max = max(myRowCount) from #TableInformation where isPrimaryKey = 0
		while @i <= @max 
		begin    
			set @Line = ''
			if @i = @max 
			begin
				 select  
					 @IntoList = @IntoList + '
			[' + columnName + '] '
				 from #TableInformation where myRowCount = @i 
			end
			else
			begin
				 select  
    				 @IntoList = @IntoList + '
			[' + columnName + '], ' 
				 from #TableInformation where myRowCount = @i 
			end
		    
			set @i = @i + 1
		end

		set @SPText = 'if exists ( select * from sys.objects where object_id = object_id(N''['+ @Schema +'].['+ @SPPrefix + @TableName +'_Select_ByPrimaryKey]'') AND type in (N''P'', N''PC''))
		drop procedure ['+ @Schema +'].[' + @SPPrefix + @TableName + '_Select_ByPrimaryKey] '

		if @DisplaySQL = 1
			print @SPText + '
	go

	'

		if @ExecuteSQL  = 1
			EXEC sp_executesql @SPText
	    
		set @SPText = 'create procedure ['+ @Schema +'].[' + @SPPrefix + @TableName + '_Select_ByPrimaryKey] 
		@' + @PKColumn + ' ' + @PKColumnType + '
	as 
		---------------------------------------------------------------------------------
		--Script history 
		--Script name    ' + @SPPrefix + @TableName + '_Select_ByPrimaryKey()
		--Creation date: '+ CONVERT(VARCHAR(20), GETDATE(), 113) +'
		--Author:        '+ @Author +'
		--
		--Comment date:  '+ CONVERT(VARCHAR(20), GETDATE(), 113) +'
		--Comment:       Generated via the CRUD generation process. 
		--               
		---------------------------------------------------------------------------------

		set NOCOUNT on 
		set XACT_ABORT on  
		
		declare @errorNumber int
		declare @rowCount int
		
		begin transaction
		
		select 
			[' + @PKColumn + '], '+ @IntoList +'
		from 
			['+ @TableName +'] (nolock)
		where ['+ @PKColumn +'] = @'+ @PKColumn +'	    
		
		set @errorNumber = @@ERROR 
		
		if @errorNumber != 0
			goto error_routine
		
		commit
			return 0

		error_routine:
			rollback transaction
			return @errorNumber	
	'
			
		if @DisplaySQL = 1
			print @SPText + '
	go

	'

		if @ExecuteSQL  = 1
			EXEC sp_executesql @SPText
		end

	end 
	
   	drop table #TableInformation

    fetch next from cursorTables into @TableName    
end

CLOSE cursorTables   
DEALLOCATE cursorTables