Below is the SQL script that automatically generates stored procedures, you can run in the query analyzer.
After running, you will see more four stored procedures.
PR__sys_makeinsertrecordproc
PR__sys_makeupdateRecordproc
PR__sys_makeselectrtructureRecordproc
PR__sys_makedeleterecordproc
Implementation: Execute in the query analyzer
PR__sys_makeinsertrecordProc 'Name - Get inserted statements
PR__sys_makeinsertRecordProc 'Name, 1 - Get inserted statements, and create an Insert stored procedure
The full script is as follows:
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
Create PrUP PR__SYS_MAKEDELETERECORDPROC
@stablename varchar (128),
@Bexecute bit = 0
AS
If DBO.FNTABLEHASPRIMARYKEY (@Stablename) = 0
Begin
Raiserror ('Procedure Cannot Be CREATED ON A Table with no primary key.', 10, 1)
Return
End
Declare @sproctext varchar (8000),
@skeyfields varchar (2000),
@Swhereclause Varchar (2000),
@scolumnname varchar (128),
@ncolumnid smallint,
@BPrimaryKeyColumn bit,
@nalternateType Int,
@ncolumnLength Int,
@ncolumnPrecision Int,
@ncolumnscale int,
@Isnullable bit,
@IsIndentity Int,
@stypename varchar (128),
@SDefaultValue Varchar (4000),
@scrlf char (2),
@stab char (1)
Set @stab = char (9)
Set @scrlf = char (13) char (10)
Set @sproctext = ''
Set @skeyfields = ''
Set @SwhereClause = ''
Set @sproctext = @sproctext 'if exists (select * from sysobjects where name =' 'prapp_' @stablename '_delete' ')' @SCRLF
Set @SPROCTEXT = @SPROCTEXT @STAB 'Drop Proc Prapp_' @stablename '_Delete ' @SCRLF
IF @bexecute = 0
Set @sproctext = @sproctext 'Go' @scrlfset @sprocktext = @SprockText @scrlf
Print @sproctext
IF @bexecute = 1
Exec (@sproctext)
Set @sproctext = ''
Set @sproctext = @sproctext '------------------------------------------------------------------------------------------------------------------------------------ -------------------------------- ' @SCRLF
Set @sproctext = @sproctext '- delete a single record from' @stablename @scrlf
Set @sproctext = @sproctext '------------------------------------------------------------------------------------------------------------------------------------ -------------------------------- ' @SCRLF
Set @sproctext = @sprocText 'Create Proc Prapp_' @stablename '_delete' @SCRLF
Declare CRKEYFIELDS CURSOR for
SELECT *
From dbo.fntableColumnInfo (@stablename)
ORDER BY 2
Open crKeyfields
Fetch next
From crKeyfields
INTO @scolumnname, @ncolumnid, @BprimaryKeyColumn, @nalternatetype,
@ncolumnlength, @ncolumnprecision, @ncolumnscale, @isnullable,
@IsiDentity, @SDEFAULTVALUE
While (@@ fetch_status = 0)
Begin
IF (@BPRIMARYKEYCOLUMN = 1)
Begin
IF (@skeyfields <> ')
Set @skeyfields = @skeyfields ',' @SCRLF
Set @skeyfields = @skeyfields @STAB '@' @scolumnname '' @stypename
IF (@NALTERNATETYPE = 2) - Decimal, Numeric
Set @skeyfields = @skeyfields '(' CAST (@ncolumnprecision as varchar (3)) ','
CAST (@ncolumnscale as varchar (3)) ')' Else IF (@nalternatetype = 1) - Character and Binary
Set @skeyfields = @skeyfields '(' CAST (@ncolumnlength as varchar (4)) ')'
IF (@swhereclause = '')
Set @Swhereclause = @SwhereClause 'Where'
Else
Set @SwhereClause = @SwherecLaSe 'and'
Set @Swhereclause = @SwhereClause @stab @scolumnname '= @' @scolumnname @scrlf
End
Fetch next
From crKeyfields
INTO @scolumnname, @ncolumnid, @BprimaryKeyColumn, @nalternatetype,
@ncolumnlength, @ncolumnprecision, @ncolumnscale, @isnullable,
@IsiDentity, @SDEFAULTVALUE
End
Close Crkeyfields
Deallocate CRKEYFIELDS
Set @sproctext = @sproctext @SKEYFIELDS @SCRLF
Set @sproctext = @sproctext 'as' @scrlf
Set @sproctext = @sproctext @scrlf
Set @sproctext = @sproctext 'delete' @stableName @scrlf
Set @sproctext = @sprocText @SwhereClause
Set @sproctext = @sproctext @scrlf
IF @bexecute = 0
Set @sproctext = @sproctext 'Go' @scrlf
Print @sproctext
IF @bexecute = 1
Exec (@sproctext)
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
Set quoted_identifier on
Go
SET ANSI_NULLS ON
Go
CREATE PR__SYS_MAKEINSERTRECORDPROC
@stablename varchar (128),
@Bexecute bit = 0
AS
If DBo.fntableHasprimaryKey (@stablename) = 0begin
Raiserror ('Procedure Cannot Be CREATED ON A Table with no primary key.', 10, 1)
Return
End
Declare @sproctext varchar (8000),
@skeyfields varchar (2000),
@sallfields varchar (2000),
@sallparams varchar (2000),
@Swhereclause Varchar (2000),
@scolumnname varchar (128),
@ncolumnid smallint,
@BPrimaryKeyColumn bit,
@nalternateType Int,
@ncolumnLength Int,
@ncolumnPrecision Int,
@ncolumnscale int,
@Isnullable bit,
@IsIndentity Int,
@HASIDENTITY INT,
@stypename varchar (128),
@SDefaultValue Varchar (4000),
@scrlf char (2),
@stab char (1)
Set @HASIDENTITY = 0
Set @stab = char (9)
Set @scrlf = char (13) char (10)
Set @sproctext = ''
Set @skeyfields = ''
Set @sallfields = ''
Set @SwhereClause = ''
Set @sallparams = ''
Set @sprocktext = @sproctext 'if exists (select * from sysobjects where name =' 'prapp_' @Stablename '_insert') ' @scrlf
Set @sproctext = @sproctext @STAB 'DROP Proc Prapp_' @stablename '_insert' @scrlf
IF @bexecute = 0
Set @sproctext = @sproctext 'Go' @scrlf
Set @sproctext = @sproctext @scrlf
Print @sproctext
IF @bexecute = 1
Exec (@sproctext)
Set @sproctext = ''
Set @sproctext = @sproctext '------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------- ' @scrlfset @sproctext = @SPROCTEXT ' - Insert A Single Record INTO ' @stablename @scrlf
Set @sproctext = @sproctext '------------------------------------------------------------------------------------------------------------------------------------ -------------------------------- ' @SCRLF
Set @SPROCTEXT = @sprocText 'Create Proc Prapp_' @Stablename '_INSERT ' @SCRLF
Declare CRKEYFIELDS CURSOR for
SELECT *
From dbo.fntableColumnInfo (@stablename)
ORDER BY 2
Open crKeyfields
Fetch next
From crKeyfields
INTO @scolumnname, @ncolumnid, @BprimaryKeyColumn, @nalternatetype,
@ncolumnlength, @ncolumnprecision, @ncolumnscale, @isnullable,
@IsiDentity, @SDEFAULTVALUE
While (@@ fetch_status = 0)
Begin
IF (@isiDentity = 0)
Begin
IF (@skeyfields <> ')
Set @skeyfields = @skeyfields ',' @SCRLF
Set @skeyfields = @skeyfields @STAB '@' @scolumnname '' @stypename
IF (@sallfields <> ')
Begin
Set @sallparams = @sallparams ','
Set @sallfields = @sallfields ','
End
IF (@stypename = 'TimeStamp')
Set @sallparams = @sallparams 'null'
Else IF (@SDEFAULTVALUE IS NOT NULL)
Set @SallParams = @sallparams 'coalesce (@' @scolumnname ',' @SDEFAULTVALUE ')' ELSE
Set @sallparams = @sallparams '@' @scolumnname
Set @sallfields = @sallfields @scolumnname
End
Else
Begin
Set @HASIDENTITY = 1
End
IF (@NALTERNATETYPE = 2) - Decimal, Numeric
Set @skeyfields = @skeyfields '(' CAST (@ncolumnprecision as varchar (3)) ','
CAST (@ncolumnscale as varchar (3)) ')'
Else if (@nalternatetype = 1) - Character and Binary
Set @skeyfields = @skeyfields '(' CAST (@ncolumnlength as varchar (4)) ')'
IF (@isiDentity = 0)
Begin
IF (@SDEFAULTVALUE IS NOT NULL) or (@SNULLABLE = 1) or (@stypename = 'TimeStamp')
Set @skeyfields = @skeyfields '= null'
End
Fetch next
From crKeyfields
INTO @scolumnname, @ncolumnid, @BprimaryKeyColumn, @nalternatetype,
@ncolumnlength, @ncolumnprecision, @ncolumnscale, @isnullable,
@IsiDentity, @SDEFAULTVALUE
End
Close Crkeyfields
Deallocate CRKEYFIELDS
Set @sproctext = @sproctext @SKEYFIELDS @SCRLF
Set @sproctext = @sproctext 'as' @scrlf
Set @sproctext = @sproctext @scrlf
Set @SPROCTEXT = @sproctext 'INSERT' @Stablename '(' @sallfields ')' @scrlf
Set @sproctext = @sproctext 'Values (' @SallParams ')' @scrlfset @sprocktext = @SprockText @scrlf
IF (@HASIDENTITY = 1)
Begin
Set @sproctext = @SPROCTEXT 'RETURN Scope_Identity ()' @scrlf
Set @sproctext = @sproctext @scrlf
End
IF @bexecute = 0
Set @sproctext = @sproctext 'Go' @scrlf
Print @sproctext
IF @bexecute = 1
Exec (@sproctext)
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
CREATE PR__SYS_MAKESELECTRECORDPROC
@stablename varchar (128),
@Bexecute bit = 0
AS
If DBO.FNTABLEHASPRIMARYKEY (@Stablename) = 0
Begin
Raiserror ('Procedure Cannot Be CREATED ON A Table with no primary key.', 10, 1)
Return
End
Declare @sproctext varchar (8000),
@skeyfields varchar (2000),
@SselectcLause Varchar (2000),
@Swhereclause Varchar (2000),
@scolumnname varchar (128),
@ncolumnid smallint,
@BPrimaryKeyColumn bit,
@nalternateType Int,
@ncolumnLength Int,
@ncolumnPrecision Int,
@ncolumnscale int,
@Isnullable bit,
@IsIndentity Int,
@stypename varchar (128),
@SDefaultValue Varchar (4000),
@scrlf char (2),
@stab char (1)
Set @stab = char (9)
Set @scrlf = char (13) char (10)
Set @sproctext = ''
Set @skeyfields = ''
Set @ssselectclause = ''
Set @SwhereClause = ''
SET @sProcText = @sProcText 'IF EXISTS (SELECT * FROM sysobjects WHERE name =' 'prApp_' @sTableName '_Select' ')' @sCRLFSET @sProcText = @sProcText @sTAB 'DROP PROC prApp_' @stablename '_select' @scrlf
IF @bexecute = 0
Set @sproctext = @sproctext 'Go' @scrlf
Set @sproctext = @sproctext @scrlf
Print @sproctext
IF @bexecute = 1
Exec (@sproctext)
Set @sproctext = ''
Set @sproctext = @sproctext '------------------------------------------------------------------------------------------------------------------------------------ -------------------------------- ' @SCRLF
Set @sproctext = @SPROCTEXT '- SELECT A SINGLE RECORD FROM' @stablename @SCRLF
Set @sproctext = @sproctext '------------------------------------------------------------------------------------------------------------------------------------ -------------------------------- ' @SCRLF
Set @SPROCTEXT = @SPROCTEXT 'CREATE PROC PRAPP_' @Stablename '_select' @scrlf
Declare CRKEYFIELDS CURSOR for
SELECT *
From dbo.fntableColumnInfo (@stablename)
ORDER BY 2
Open crKeyfields
Fetch next
From crKeyfields
INTO @scolumnname, @ncolumnid, @BprimaryKeyColumn, @nalternatetype,
@ncolumnlength, @ncolumnprecision, @ncolumnscale, @isnullable,
@IsiDentity, @SDEFAULTVALUE
While (@@ fetch_status = 0)
Begin
IF (@BPRIMARYKEYCOLUMN = 1)
Begin
IF (@skeyfields <> ')
Set @skeyfields = @skeyfields ',' @scrlfset @skeyfields = @Skeyfields @stab '@' @scolumnname '' @stypename
IF (@NALTERNATETYPE = 2) - Decimal, Numeric
Set @skeyfields = @skeyfields '(' CAST (@ncolumnprecision as varchar (3)) ','
CAST (@ncolumnscale as varchar (3)) ')'
Else if (@nalternatetype = 1) - Character and Binary
Set @skeyfields = @skeyfields '(' CAST (@ncolumnlength as varchar (4)) ')'
IF (@swhereclause = '')
Set @Swhereclause = @SwhereClause 'Where'
Else
Set @SwhereClause = @SwherecLaSe 'and'
Set @Swhereclause = @SwhereClause @stab @scolumnname '= @' @scolumnname @scrlf
End
IF (@SselectcLause = ')
Set @ssselectclause = @SselectcLaSe 'SELECT'
Else
Set @ssselectclause = @SselectcLaSe ',' @scrlf
Set @ssselectclause = @SselectcLaSe @stab @scolumnname
Fetch next
From crKeyfields
INTO @scolumnname, @ncolumnid, @BprimaryKeyColumn, @nalternatetype,
@ncolumnlength, @ncolumnprecision, @ncolumnscale, @isnullable,
@IsiDentity, @SDEFAULTVALUE
End
Close Crkeyfields
Deallocate CRKEYFIELDS
Set @ssselectclause = @SselectcLaSe @scrlf
Set @sproctext = @sproctext @SKEYFIELDS @SCRLF
Set @sproctext = @sproctext 'as' @scrlfset @sprockText = @SPROCTEXT @SCRLF
Set @sproctext = @SPROCTEXT @SselectcLause
Set @sproctext = @sproctext 'from' @stablename @scrlf
Set @sproctext = @sprocText @SwhereClause
Set @sproctext = @sproctext @scrlf
IF @bexecute = 0
Set @sproctext = @sproctext 'Go' @scrlf
Print @sproctext
IF @bexecute = 1
Exec (@sproctext)
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
Set quoted_identifier on
Go
SET ANSI_NULLS ON
Go
CREATE PR__SYS_MAKEUPDATERECORDPROC
@stablename varchar (128),
@Bexecute bit = 0
AS
If DBO.FNTABLEHASPRIMARYKEY (@Stablename) = 0
Begin
Raiserror ('Procedure Cannot Be CREATED ON A Table with no primary key.', 10, 1)
Return
End
Declare @sproctext varchar (8000),
@skeyfields varchar (2000),
@ssetclause varchar (2000),
@Swhereclause Varchar (2000),
@scolumnname varchar (128),
@ncolumnid smallint,
@BPrimaryKeyColumn bit,
@nalternateType Int,
@ncolumnLength Int,
@ncolumnPrecision Int,
@ncolumnscale int,
@Isnullable bit,
@IsIndentity Int,
@stypename varchar (128),
@SDefaultValue Varchar (4000),
@scrlf char (2),
@stab char (1)
Set @stab = char (9)
Set @scrlf = char (13) char (10)
Set @sproctext = ''
Set @skeyfields = ''
Set @ssetclause = ''
Set @SwhereClause = ''
SET @sProcText = @sProcText 'IF EXISTS (SELECT * FROM sysobjects WHERE name =' 'prApp_' @sTableName '_Update' ')' @sCRLFSET @sProcText = @sProcText @sTAB 'DROP PROC prApp_' @stablename '_Update' @scrlf
IF @bexecute = 0
Set @sproctext = @sproctext 'Go' @scrlf
Set @sproctext = @sproctext @scrlf
Print @sproctext
IF @bexecute = 1
Exec (@sproctext)
Set @sproctext = ''
Set @sproctext = @sproctext '------------------------------------------------------------------------------------------------------------------------------------ -------------------------------- ' @SCRLF
Set @sproctext = @sproctext '- Update a Single Record in' @stablename @scrlf
Set @sproctext = @sproctext '------------------------------------------------------------------------------------------------------------------------------------ -------------------------------- ' @SCRLF
Set @sproctext = @sprocText 'Create Proc Prapp_' @stablename '_Update' @SCRLF
Declare CRKEYFIELDS CURSOR for
SELECT *
From dbo.fntableColumnInfo (@stablename)
ORDER BY 2
Open crKeyfields
Fetch next
From crKeyfields
INTO @scolumnname, @ncolumnid, @BprimaryKeyColumn, @nalternatetype,
@ncolumnlength, @ncolumnprecision, @ncolumnscale, @isnullable,
@IsiDentity, @SDEFAULTVALUE
While (@@ fetch_status = 0)
Begin
IF (@skeyfields <> ')
Set @skeyfields = @skeyfields ',' @SCRLF
Set @skeyfields = @skeyfields @STAB '@' @scolumnname ' @stypenameif (@nalternateType = 2) - Decimal, Numeric
Set @skeyfields = @skeyfields '(' CAST (@ncolumnprecision as varchar (3)) ','
CAST (@ncolumnscale as varchar (3)) ')'
Else if (@nalternatetype = 1) - Character and Binary
Set @skeyfields = @skeyfields '(' CAST (@ncolumnlength as varchar (4)) ')'
IF (@BPRIMARYKEYCOLUMN = 1)
Begin
IF (@swhereclause = '')
Set @Swhereclause = @SwhereClause 'Where'
Else
Set @SwhereClause = @SwherecLaSe 'and'
Set @Swhereclause = @SwhereClause @stab @scolumnname '= @' @scolumnname @scrlf
End
Else
IF (@isiDentity = 0)
Begin
IF (@ssetclause = '')
Set @ssetclause = @ssetclause 'set'
Else
Set @ssetclause = @ssetclause ',' @SCRLF
Set @ssetclause = @ssetclause @stab @scolumnname '='
IF (@stypename = 'TimeStamp')
Set @ssetclause = @SsetClause 'Null'
Else IF (@SDEFAULTVALUE IS NOT NULL)
Set @ssetclause = @ssetclause 'coalesce (@' @scolumnname ',' @SDEFAULTVALUE ')'
Else
Set @ssetclause = @ssetclause '@' @scolumnname
End
IF (@isiDentity = 0)
Begin
IF (@isnullable = 1) or (@stypename = 'timestamp') set @skeyfields = @skeyfields '= null'
End
Fetch next
From crKeyfields
INTO @scolumnname, @ncolumnid, @BprimaryKeyColumn, @nalternatetype,
@ncolumnlength, @ncolumnprecision, @ncolumnscale, @isnullable,
@IsiDentity, @SDEFAULTVALUE
End
Close Crkeyfields
Deallocate CRKEYFIELDS
Set @ssetclause = @ssetclause @scrlf
Set @sproctext = @sproctext @SKEYFIELDS @SCRLF
Set @sproctext = @sproctext 'as' @scrlf
Set @sproctext = @sproctext @scrlf
Set @sproctext = @sprocText 'Update' @stableName @scrlf
Set @sproctext = @sproctext @ssetclause
Set @sproctext = @sprocText @SwhereClause
Set @sproctext = @sproctext @scrlf
IF @bexecute = 0
Set @sproctext = @sproctext 'Go' @scrlf
Print @sproctext
IF @bexecute = 1
Exec (@sproctext)
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
Set quoted_identifier on
Go
SET ANSI_NULLS ON
Go
Create Function DBO.fncleandefaultValue (@sdefaultvalue varchar (4000))
Returns varchar (4000)
AS
Begin
Return Substring (@SDefaultValue, 2, Datanceth (@SDEFAULTVALUE) -2)
End
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
Set quoted_identifier on
Go
SET ANSI_NULLS ON
Go
Create function dbo.fncolumndefault (@stablename varchar (128), @scolumnname varchar (128))
Returns varchar (4000)
AS
Begin
Declare @sdefaultvalue varchar (4000) SELECT @sdefaultValue = dbo.fncleandefaultValue (Column_Default)
From information_schema.columns
Where table_name = @stablename
And colorn_name = @scolumnname
Return @SDefaultValue
End
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
Set quoted_identifier on
Go
SET ANSI_NULLS ON
Go
Create function dbo.fniscolumnprimarykey (@stablename varchar (128), @ncolumnname varchar (128))
Returns bit
AS
Begin
Declare @ntableid Int,
@nindexid Int,
@i int
Set @ntableid = Object_ID (@stablename)
SELECT @nindexid = Indid
From sysindexes
Where id = @NTableID
And Indid Between 1 and 254
And (status & 2048) = 2048
IF @nindexid is null
Return 0
IF @ncolumnname in
Select sc. [Name]
From sysindexkeys sik
Inner Join Syscolumns Sc on Sik.Id = sc.id and sik.colid = sc.colid
Where sik.id = @NTableID
And sik.indid = @nindexid)
Begin
Return 1
End
Return 0
End
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
Set quoted_identifier on
Go
SET ANSI_NULLS ON
Go
Create function dbo.fntableColumninfo (@stablename varchar (128))
Returns Table
AS
Return
Select c.name as scolumnname,
C.colid as ncolumnid,
DBO.FNISCOLUMNPRIMARYKEY (@stablename, c.name) as bprimaryKeyColumn,
Case When T.Name In ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar') THEN 1
When T.Name in ('Decimal', 'Numeric') THEN 2
ELSE 0
End as nalternateType,
C.Length As ncolumnlength,
C.PREC AS NCOLUMNPRECISION,
C.scale as ncolumnscale,
C.ISNULLABLE,
Sign (C.Status & 128) As ISIDENTITY, T.NAME ASSTYPENAME,
DBO.fncolumndefault (@stablename, c.name) AS SDEFAULTVALUE
From syscolumns c
Inner Join SystemYpes T on C.XTYPE = T.XTYPE AND C.USERTYPE = T.USERTYPE
WHERE C.ID = Object_ID (@stablename)
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
Set quoted_identifier on
Go
SET ANSI_NULLS ON
Go
Create function dbo.fntablehaasprimarykey (@stablename varchar (128))
Returns bit
AS
Begin
Declare @ntableid Int,
@nindexid Int
Set @ntableid = Object_ID (@stablename)
SELECT @nindexid = Indid
From sysindexes
Where id = @NTableID
And Indid Between 1 and 254
And (status & 2048) = 2048
IF @nindexid is not null
Return 1
Return 0
End
Go
Set quoted_identifier off
Go
SET ANSI_NULLS ON
Go
Source document
