CRUD generator

set nocount on
/********************************************************************************************************************
Author :Ryan Foote
***********************************************************************************************************************/declare @TableName varchar(100)
,@SQL1 varchar(2000)
,@SQL2 varchar(2000)
,@SQL3 varchar(2000)
,@SQL4 varchar(2000)
,@SQL5 varchar(2000)
,@SQL6 varchar(2000)
,@Parameters int 
,@TypeOfCrud int--1 = Insert, 2 = Update, 3 = Read, 4 = Delete, 5 = All

select @TableName = '' --<<Enter the name of the table 
,@Parameters = 0 --<< If using parameters for the insert statement then use 1 if not then use 0
,@TypeOfCrud = 5

declare @FinalSelect table
([--SelectText]varchar(2000))

declare @HeaderSelect table
(HeaderID int
,[--SelectText] varchar(2000))

if object_id('tempdb..#ParameterTable') is not NULL
begin
drop table #ParameterTable
end

create table #ParameterTable 
(ParameterName varchar(40)
,DataType varchar(20)
,Nullability varchar(10))


if (@TypeOfCrud = 1 or @TypeOfCrud = 5)
begin
select @SQL1 = 'select case when C1.Ordinal_Position = C3.MinOrdinalPosition
then ''Insert into dbo.' + @TableName + '('' else '' '' end +
case when C1.Ordinal_Position = C3.MinOrdinalPosition then + Char(10) + '' '' 
else '','' end + Column_Name +
case when C1.Ordinal_Position = C2.MaxOrdinalPosition then '')'' 
else '' '' end
from Information_schema.columns C1
join (select max(Ordinal_Position) MaxOrdinalPosition
,table_name
from Information_schema.columns
group by table_name) C2
on C1.Table_Name = C2.Table_Name
join (select min(Ordinal_Position) MinOrdinalPosition
,table_name
from Information_Schema.columns
where ColumnProperty(object_id(Table_Name), Column_Name, ''IsIdentity'') <> 1
group by table_name) C3
on C1.Table_Name = C3.Table_Name
where C1.table_name = ''' + @TableName + '''
and ColumnProperty(object_id(C1.Table_Name), C1.Column_Name, ''IsIdentity'') <> 1
order by C1.ordinal_position'
if @Parameters = 0
begin
select @SQL2 = 'select case when Ordinal_Position = C3.MinOrdinalPosition 
then ''Select'' else '' '' end +
case when Ordinal_Position = C3.MinOrdinalPosition then '' ''
else '','' end + Column_Name +
case when Ordinal_Position = C2.MaxOrdinalPosition then + Char(10) + '' from dbo.' + @TableName + ''' 
else '' '' end
from Information_schema.columns C1
join (select max(Ordinal_Position) MaxOrdinalPosition
,table_name
from Information_schema.columns
group by table_name) C2
on C1.Table_Name = C2.Table_Name
join (select min(Ordinal_Position) MinOrdinalPosition
,table_name
from Information_Schema.columns
where ColumnProperty(object_id(Table_Name), Column_Name, ''IsIdentity'') <> 1
group by table_name) C3
on C1.Table_Name = C3.Table_Name
where C1.table_name = ''' + @TableName + '''
and ColumnProperty(object_id(C1.Table_Name), C1.Column_Name, ''IsIdentity'') <> 1
order by C1.ordinal_position'
end
end
if @TypeOfCrud in (1, 2, 5) 
begin
select @SQL6 = ('insert into #ParameterTable(ParameterName, DataType, Nullability)
select case when C1.Ordinal_Position = C2.MinOrdinalPosition then ''Declare''
else ''''
end
+ case when C1.Ordinal_Position <> C2.MinOrdinalPosition then '' ,'' else '' '' end +
''@'' + Column_Name
,upper(Data_Type)
+ case when character_maximum_length is NULL and Data_Type <> ''Decimal'' then ''''
when character_maximum_length is not NULL then '''' + ''('' + cast(character_maximum_length as varchar(10))+ '')''
when Data_Type = ''Decimal'' then ''('' + cast(Numeric_Precision as varchar(10)) + '','' + cast(Numeric_Scale as varchar(10))+ '')''
end 
,case when is_nullable = ''yes'' then ''NULL'' 
when is_nullable = ''no'' then ''NOT NULL''
end
from information_schema.columns C1
join (select min(Ordinal_Position) MinOrdinalPosition
,table_name
from Information_Schema.columns
where ColumnProperty(object_id(Table_Name), Column_Name, ''IsIdentity'') <> 1
group by table_name) C2
on C1.Table_Name = C2.Table_Name
where C1.table_name = ''' + @TableName + '''
and ColumnProperty(object_id(C1.Table_Name), C1.Column_Name, ''IsIdentity'') <> 1
order by C1.Ordinal_Position')

exec(@SQL6)
end

if @Parameters = 1 and @TypeOfCrud in (1, 5)
begin
select @SQL2 = 'select case when Ordinal_Position = C3.MinOrdinalPosition then ''Values('' else '' '' end +
case when Ordinal_Position = C3.MinOrdinalPosition then ''@''
else '',@'' end + Column_Name + 
case when Ordinal_Position = C2.MaxOrdinalPosition then '')'' else '''' end
from Information_schema.columns C1
join (select max(Ordinal_Position) MaxOrdinalPosition
,table_name
from Information_schema.columns
group by table_name) C2
on C1.Table_Name = C2.Table_Name
join (select min(Ordinal_Position) MinOrdinalPosition
,table_name
from Information_Schema.columns
where ColumnProperty(object_id(Table_Name), Column_Name, ''IsIdentity'') <> 1
group by table_name) C3
on C1.Table_Name = C3.Table_Name
where C1.table_name = ''' + @TableName + '''
and ColumnProperty(object_id(C1.Table_Name), C1.Column_Name, ''IsIdentity'') <> 1
order by C1.ordinal_position'
end

if (@TypeOfCrud = 2 or @TypeOfCrud = 5)
begin

declare @PrimaryKey varchar(50)

select @PrimaryKey = CCU.Column_Name 
from Information_Schema.Constraint_Column_Usage CCU
join Information_Schema.Table_Constraints TC
on CCU.Constraint_Name = TC.Constraint_Name
where TC.Table_Name = @TableName 
and TC.Constraint_Type = 'Primary Key'


select @SQL3 = 'select case when Ordinal_Position = C3.MinOrdinalPosition then ''Update dbo.' + @TableName + ''' + Char(10) + ''set '' 
else '' ''
end
+ case when Ordinal_Position = C3.MinOrdinalPosition then '''' + Column_Name else + '' '' + '','' + Column_Name end 
+ '' = '' + ''@'' + Column_Name + 
+ case when Ordinal_Position = C2.MaxOrdinalPosition
then '''' + Char(10) + '''' + ''where '' + ''' + @PrimaryKey + ''' + '' = '' + ''@'' + ''' + @PrimaryKey + ''' 
else ''''
end
from Information_schema.columns C1
join (select max(Ordinal_Position) MaxOrdinalPosition
,table_name
from Information_schema.columns
group by table_name) C2
on C1.Table_Name = C2.Table_Name
join (select min(Ordinal_Position) MinOrdinalPosition
,table_name
from Information_Schema.columns
where ColumnProperty(object_id(Table_Name), Column_Name, ''IsIdentity'') <> 1
group by table_name) C3
on C1.Table_Name = C3.Table_Name
where C1.table_name = ''' + @TableName + '''
and ColumnProperty(object_id(C1.Table_Name), C1.Column_Name, ''IsIdentity'') <> 1
order by C1.ordinal_position'



select [--SelectText]
from @HeaderSelect

select ParameterName as '--Parameter Name' 
,DataType as '--DataType'
--,Nullability
from #ParameterTable

exec (@SQL3) 
end

if (@TypeOfCrud = 3 or @TypeOfCrud = 5)
begin
select @SQL4 = 'select case when Ordinal_Position = 1 then ''Select'' else '' '' end +
case when Ordinal_Position = 1 then '' ''
else '','' end + Column_Name +
case when Ordinal_Position = C2.MaxOrdinalPosition then + Char(10) + '' from dbo.' + @TableName + ''' 
else '' '' end
from Information_schema.columns C1
join (select max(Ordinal_Position) MaxOrdinalPosition
,table_name
from Information_schema.columns
group by table_name) C2
on C1.Table_Name = C2.Table_Name
where C1.table_name = ''' + @TableName + '''
order by C1.ordinal_position'

select [--SelectText]
from @HeaderSelect
exec (@SQL4)
end

if (@TypeOfCrud = 4 or @TypeOfCrud = 5)
begin


select @PrimaryKey = CCU.Column_Name 
from Information_Schema.Constraint_Column_Usage CCU
join Information_Schema.Table_Constraints TC
on CCU.Constraint_Name = TC.Constraint_Name
where TC.Table_Name = @TableName 
and TC.Constraint_Type = 'Primary Key'


select @SQL5 = 'Delete from dbo.' + @TableName + ' 
where ' + @PrimaryKey + ' = ' 


select [--SelectText]
from @HeaderSelect
select @SQL5
end


if isnull(@SQL1, '') <> ''
begin
insert into @FinalSelect([--SelectText])
exec (@SQL1)
end

if isnull(@SQL2, '') <> ''
begin
insert into @FinalSelect([--SelectText])
exec(@SQL2)
end

if (select count(*) from @FinalSelect) > 0
begin
select [--SelectText] 
from @HeaderSelect 
if exists(select 1 from #ParameterTable) and @Parameters = 1
begin
select ParameterName as '--Paramter Name'
,DataType as '--DataType'
--,Nullability
from #ParameterTable
end
select [--SelectText] as '--Insert'
from @FinalSelect
end