--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