--CRUD스크립트만들기
--SSMS에서 Ctrl + T 누른 다음에 적당히 편집해서 사용하세요.

/****** Object:  StoredProcedure [dbo].[usp_createsql]    Script Date: 2012-07-25 오전 10:59:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--crud스크립트만들기
--ssms에서 ctrl + t 누른 다음에 적당히 편집해서 사용하세요.
ALTER proc [dbo].[usp_createsql]
	@sname varchar(255) --schema name
,	@tname varchar(255) --table name
,	@curd char(1)
,	@spyn tinyint = 1
as
--exec usp_createsql '스키마명', '테이블명', 'c'
--exec usp_createsql '스키마명', '테이블명', 'u'
--exec usp_createsql '스키마명', '테이블명', 'r'
--exec usp_createsql '스키마명', '테이블명', 'd'
begin
 
	set concat_null_yields_null on
	set nocount on 
	set statistics io off
 
	declare @maxno varchar(1024)
 
	select 
		@maxno = cast(max(ordinal_position) as varchar) 
	from information_schema.columns
	where table_name = @tname
	and table_schema = @sname
 
 
	if upper(@curd) = 'c'
	begin
 
		select sp
		from (
			select 'create proc usp_' + 
					case 
						when @curd = 'c' then 'create' 
						when @curd = 'r' then 'read' 
						when @curd = 'u' then 'update' 
						when @curd = 'd' then 'delete' 
					end + '_' + @tname sp
			union all
			select
				
				case 
					when ordinal_position = 1 then '    ' + '@' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '')
					else ','+ '   ' + '@' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '') end columnname
			from information_schema.columns
			where table_name = @tname 
			and table_schema = @sname
			union all
			select 'as') t
		where @spyn = 1
		union all
		select 
			case 
				when ordinal_position = 1 then 'insert ' + @sname + '.' + @tname + char(13) + '(' + char(13) + '    ' + column_name
				when ordinal_position = @maxno then ','+ '   ' + column_name + char(13) + ')'
				else ','+ '   ' + column_name end columnname
		from information_schema.columns
		where table_name = @tname
		and table_schema = @sname
		union all
		select 
			case 
				when ordinal_position = 1 then 'values' + char(13) + '(' + char(13) + '    ' + '@' + column_name
				when ordinal_position = @maxno then ','+ '   ' + '@' + column_name + char(13) + ')'
				else ','+ '   ' + '@' + column_name end columnname
		from information_schema.columns
		where table_name = @tname
		and table_schema = @sname
	end else if upper(@curd) = 'u'
	begin
		select sp
		from (
			select 'create proc usp_' + 
					case 
						when @curd = 'c' then 'create' 
						when @curd = 'r' then 'read' 
						when @curd = 'u' then 'update' 
						when @curd = 'd' then 'delete' 
					end + '_' + @tname sp
			union all
			select
				
				case 
					when ordinal_position = 1 then '    ' + '@' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '')
					else ','+ '   ' + '@' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '') end columnname
			from information_schema.columns
			where table_name = @tname 
			and table_schema = @sname
			union all
			select 'as') t
		where @spyn = 1
		union all
		select 
			case 
				when ordinal_position = 1 then 'update ' + @sname + '.' + @tname + char(13) + 'set ' + char(13) + '    ' + column_name + ' = @' + column_name
				else ','+ '   ' + column_name + ' = @' + column_name end columnname
		from information_schema.columns
		where table_name = @tname
		and table_schema = @sname
		union all
		select 'from ' + @sname + '.' + @tname
	end else if upper(@curd) = 'r'
	begin
		select sp
		from (
			select 'create proc usp_' + 
					case 
						when @curd = 'c' then 'create' 
						when @curd = 'r' then 'read' 
						when @curd = 'u' then 'update' 
						when @curd = 'd' then 'delete' 
					end + '_' + @tname sp
			union all
			select
				
				case 
					when ordinal_position = 1 then '    ' + '@' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '')
					else ','+ '   ' + '@' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '') end columnname
			from information_schema.columns
			where table_name = @tname 
			and table_schema = @sname
			union all
			select 'as') t
		where @spyn = 1
		union all
		select 
			case 
				when ordinal_position = 1 then 'select ' + char(13) + '    ' + column_name 
				else ','+ '   ' + column_name end columnname
		from information_schema.columns
		where table_name = @tname
		and table_schema = @sname
 		union all
		select 'from ' + @sname + '.' + @tname
	end else if upper(@curd) = 'd'
	begin
		select sp
		from (
			select 'create proc usp_' + 
					case 
						when @curd = 'c' then 'create' 
						when @curd = 'r' then 'read' 
						when @curd = 'u' then 'update' 
						when @curd = 'd' then 'delete' 
					end + '_' + @tname sp
			union all
			select
				case 
					when ordinal_position = 1 then '    ' + '@' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '')
					else ','+ '   ' + '@' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '') end columnname
			from information_schema.columns
			where table_name = @tname 
			and table_schema = @sname
			union all
			select 'as') t
		where @spyn = 1
		union all
		select 'delete ' + @sname + '.' + @tname + char(13) + 'from ' + @sname + '.' + @tname columnname
		union all
		select 
			case 
				when ordinal_position = 1 then 'where 1=1' + char(13) + 'and ' + column_name + ' = @' + column_name
				else 'and '+ column_name + ' = ' + '@' + column_name end columnname
		from information_schema.columns
		where table_name = @tname
		and table_schema = @sname
	end else
	begin
		select '사용법: exec usp_createsql ''스키마명'', ''테이블명'', {''c'', ''u'', ''r'', ''d''}'
	end
 
	--변수
	select 
		case 
			when ordinal_position = 1 then '    ' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '')
			else ','+ '   ' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '') end columnname
	from information_schema.columns
	where table_name = @tname
	and table_schema = @sname
 
	select
		case 
			when ordinal_position = 1 then '    @' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '')
			else ','+ '   ' + '@' + column_name + ' ' + data_type  collate korean_wansung_ci_as + isnull('(' + cast(character_maximum_length as varchar) + ')', '') end columnname
	from information_schema.columns
	where table_name = @tname
	and table_schema = @sname

	select
		case 
			when ordinal_position = 1 then '        convert(nvarchar(200), ' + case when data_type like '%date%' then 'convert(varchar, @' + column_name + ', 121)' else '@' + column_name end + ')' 
			else '+ '','' + '+ 'convert(nvarchar(200), ' + case when data_type like '%date%' then 'convert(varchar, @' + column_name + ', 121)' else '@' + column_name end + ')' end columnname
	from information_schema.columns
	where table_name = @tname
	and table_schema = @sname
end