http://blog.9cbs.net/lihonggen0/archive/2004/08/15/7547.aspx
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 goset ANSI_NULLS ON GO
Create proc PR__sys_makedeleterecordproc @stablename varchar (128), @bexecute bit = 0as
If DBO.FNTABLEHASPRIMARYKEYKEY (@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, @isisneletity 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' ')' @sCRLFSET @sProcText = @sProcText @sTAB 'DROP PROC prApp_' @stablename '_delete' @scrlf @Bexecute = 0 set @sproctext = @sprocText 'Go' @scrlfset @sprockText = @sproctext @SCRLF
Print @sproctext
IF @bexecute = 1 exec (@sproctext)
Set @sproctext = '' set @sproctext = @sproctext '------------------------------------ --------------------------------------- ' @scrlfset @SPROCTEXT = @SprockText '- delete a single record from' @Stablename @scrlfset @sproctext = @sproctext '------------------------------------------------------------------------------------------ ------------------------------------------------ ' @scrlfset @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, @sTypeName, @sDefaultValue WHILE (@@ FETCH_STATUS = 0) BEGIN
IF (@bPrimaryKeyColumn = 1) 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 varcha (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 = @SwhereClause 'and'
Set @Swhereclause = @SwhereClause @stab @scolumnname '= @' @scolumnname @SCRLF END
FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END
Close Crkeyfieldsdeallocate CRKEYFIELDS
SET @sProcText = @sProcText @sKeyFields @sCRLFSET @sProcText = @sProcText 'AS' @sCRLFSET @sProcText = @sProcText @sCRLFSET @sProcText = @sProcText 'DELETE' @sTableName @sCRLFSET @sProcText = @SPROCTEXT @SwhereClauseset @SPROCTEXT = @SPROCTEXT @Scrlf @SExecute = 0 set @sprockText = @SprockText 'Go' @scrlfprint @sprocText
IF @bexecute = 1 exec (@sproctext)
Goset quoted_identifier off goset ANSI_NULLS ON Go
Set quoted_identifier on goset ANSI_NULLS ON GO
Create proc PR__sys_makeinsertrecordproc @stablename varchar (128), @bexecute bit = 0as
If DBO.FNTABLEHASPRIMARYKEYKEY (@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), @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, @IsIdentity int, @HasIdentity int, @sTypeName varchar (128), @sDefaultValue varchar (4000), @sCRLF char (2), @stab char (1)
Set @hasidentity = 0set @STAB = char (9) set @scrlf = char (13) char (10) set @sproctext = '' set @skeyfields = 'set @sallfields =' set @Swhereclause = 'set @sAllParams = '' SET @sProcText = @sProcText 'IF EXISTS (SELECT * FROM sysobjects WHERE name =' 'prApp_' @sTableName '_Insert' ')' @sCRLFSET @sProcText = @sProcText @sTAB ' Drop Proc Prapp_ ' @Stablename '_INSERT' @SCRLFIF @Bexecute = 0 set @sprocktext = @SPROCTEXT 'Go' @SCRLF
Set @sproctext = @sproctext @scrlf
Print @sproctext
IF @bexecute = 1 exec (@sproctext)
Set @sproctext = '' set @sproctext = @sproctext '------------------------------------ --------------------------------------- ' @scrlfset @SPROCTEXT = @SprockText '- INSERT A SINGLE RECORD INTO' @Stablename @scrlfset @SPROCTEXT = @sprocText '------------------------------------------------------------------------------------------ ------------------------------------------------ ' @scrlfset @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, @sTypeName, @sDefaultValue WHILE (@@ FETCH_STATUS = 0) BEGIN IF (@IsIdentity = 0) Begin if (@skeyfields <> ') set @skeyfields = @skeyfields ', ' @scrlfset @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
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 (@IsNullable = 1) OR (@sTypeName = 'timestamp') SET @sKeyFields = @sKeyFields '= NULL' ENDFETCH NEXT FROM crKeyFields INTO @sColumnName , @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END
Close Crkeyfieldsdeallocate CRKEYFIELDS
SET @sProcText = @sProcText @sKeyFields @sCRLFSET @sProcText = @sProcText 'AS' @sCRLFSET @sProcText = @sProcText @sCRLFSET @sProcText = @sProcText 'INSERT' @sTableName '(' @ Sallfields ')' @scrlfset @SPROCTEXT = @SPROCTEXT 'VALUES (' @SallParams ')' @scrlfset @SPROCTEXT = @sproctext @scrlf
IF (@hasidentity = 1) begin set @sproctext = @SPROCTEXT 'RETURN Scope_Identity ()' @scrlf set @sprocktext = @sprocText @scrlf End
IF @bexecute = 0 set @sproctext = @sproctext 'Go' @scrlf
Print @sproctext
IF @bexecute = 1 exec (@sproctext)
Goset quoted_identifier off goset ANSI_NULLS ON Go
Set quoted_identifier off goset ANSI_NULLS ON GO
CREATE PR__SYS_MAKESELECTRECORDPROC @stablename varchar (128), @bexecute bit = 0as
IF dbo.fnTableHasPrimaryKey (@sTableName) = 0 BEGIN RAISERROR ( 'Procedure can not be created on a table with no primary key.', 10, 1) RETURN ENDDECLARE @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, @IsIdentity 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 @sslectclause = '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 @bexecute = 0 set @sprocktext = @SprockText 'Go' @SCRLF
Set @sproctext = @sproctext @scrlf
Print @sproctext
IF @bexecute = 1 exec (@sproctext)
Set @sproctext = '' set @sproctext = @sproctext '------------------------------------ --------------------------------------- ' @scrlfset @SPROCTEXT = @SprockText '- Select a Single Record from' @Stablename @scrlfset @SPROCTEXT = @sproctext '---------------------------- ------------------------------------------------ ' @sCRLFSET @sProcText = @sProcText 'CREATE PROC prApp_' @sTableName '_Select' @sCRLFDECLARE 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, @sTypeName, @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 varcha (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 = @SwhereClause 'and'
Set @Swhereclause = @SwhereClause @stab @scolumnname '= @' @scolumnname @SCRLF END
IF (@ssselectclause = ') set @ssselectclause = @ssselectclause ' select 'else set @sslectclause = @sslectclause ', ' @scrlf
Set @ssselectclause = @SselectcLaSe @stab @scolumnname
FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue ENDCLOSE crKeyFieldsDEALLOCATE crKeyFields
Set @ssselectclause = @SselectcLaSe @scrlf
SET @sProcText = @sProcText @sKeyFields @sCRLFSET @sProcText = @sProcText 'AS' @sCRLFSET @sProcText = @sProcText @sCRLFSET @sProcText = @sProcText @sSelectClauseSET @sProcText = @sProcText 'FROM' @sTableName @sCRLFSET @sProcText = @sProcText @sWhereClauseSET @sProcText = @sProcText @sCRLFIF @bExecute = 0 SET @sProcText = @sProcText 'GO' @sCRLF
Print @sproctext
IF @bexecute = 1 exec (@sproctext)
Goset quoted_identifier off goset ANSI_NULLS ON Go
Set quoted_identifier on goset ANSI_NULLS ON GO
Create PrsupdateRecordProc @stablename varchar (128), @bexecute bit = 0AS
If DBO.FNTABLEHASPRIMARYKEYKEY (@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, @IsIdentity 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 @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 = @SprockText '- Update a Single Record in' @Stablename @scrlfset @SPROCTEXT = @sprocText '-------------------------------------------------------------------------- ------------------------------------------------ ' @sCRLFSET @sProcText = @sProcText 'CREATE PROC prApp_' @sTableName '_Update' @sCRLFDECLARE 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, @sTypeName, @sDefaultValue WHILE (@@ FETCH_STATUS = 0) 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 (@bPrimaryKeyColumn = 1) BEGIN IF (@sWhereClause = '') SET @sWhereClause = @sWhereClause 'WHERE' ELSE SET @sWhereClause = @sWhereClause '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 = @SSETCLASE '@' @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, @sTypeName, @sDefaultValue END
Close Crkeyfieldsdeallocate CRKEYFIELDS
Set @ssetclause = @ssetclause @scrlf
SET @sProcText = @sProcText @sKeyFields @sCRLFSET @sProcText = @sProcText 'AS' @sCRLFSET @sProcText = @sProcText @sCRLFSET @sProcText = @sProcText 'UPDATE' @sTableName @sCRLFSET @sProcText = @sProcText @sSetClauseSET @sProcText = @sProcText @sWhereClauseSET @sProcText = @sProcText @sCRLFIF @bExecute = 0 SET @sProcText = @sProcText 'GO' @sCRLFPRINT @sProcText
IF @bexecute = 1 exec (@sproctext)
Goset quoted_identifier off goset ANSI_NULLS ON Go
Set quoted_identifier on goset ANSI_NULLS ON GO
Create Function DBO.FNCLEANDEFAULTVALUE (@sdefaultvalue varchar (4000) Returns varchar (4000) Asbegin Return Substring (@SDEFAULTVALUE, 2, DATALENGTH (@SDEFAULTVALUE) -2) End
Goset quoted_identifier off goset ANSI_NULLS ON Go
Set quoted_identifier on goset ANSI_NULLS ON GO
Create function dbo.fncolumndefault (@Stablename varchar (128), @scolumnname varchar (128)) Returns varchar (4000) asbegin 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
Goset quoted_identifier off goset ANSI_NULLS ON Go
Set quoted_identifier on goset ANSI_NULLS ON GO
CREATE FUNCTION dbo.fnIsColumnPrimaryKey (@sTableName varchar (128), @nColumnName varchar (128)) RETURNS bitASBEGIN 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 Endreturn 0nd
Goset quoted_identifier off goset ANSI_NULLS ON Go
Set quoted_identifier on goset ANSI_NULLS ON GO
CREATE FUNCTION dbo.fnTableColumnInfo (@sTableName varchar (128)) RETURNS TABLEAS 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 as sTypeName, 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)
Goset quoted_identifier off goset ANSI_NULLS ON Go
Set quoted_identifier on goset ANSI_NULLS ON GO
CREATE FUNCTION dbo.fnTableHasPrimaryKey (@sTableName varchar (128)) RETURNS bitASBEGIN 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 0ndgoset quoted_identifier off goset ANSI_NULLS ON Go
