Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

CRUD generator

By Ryan Foote, 2009/07/06

Introduction

Often times DBA's are tasked with creating simple create, read, update, and delete stored procedures and scripts. I created a script to quickly generate the T-SQL for CRUD statements in an easy to read formatted output. This will give a DBA a good starting point to easily generate a template for CRUD statements.

How to use

Enter the name of the defined table as the @TableName parameter to generate the CRUD statements against. Choose the type of CRUD statement for the @TypeOfCrud parameter and there is a key listed as a comment in the script. If using parameters for the insert statement then set @Parameters equal to 1 if not using parameters for the insert statement then set @Parameters equal to 0.

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