/ * Pr__SYS_MakeDeleteRecordProc generate stored procedures to delete data stored pr__SYS_MakeInsertRecordProc generate insert data generation process PROC pr__SYS_MakeSelectRecordProc stored procedure to query data pr__SYS_MakeUpdateRecordProc generate stored procedures sp_decrypt update data stored decryption process * // * Function: Write SPstore AutoDate: 2/4 / 2005Modify : Winking Tan * / CREATE PROC pr__SYS_MakeDeleteRecordProc / * Function: Make Delete Record Proc * / @sTableName varchar (128), @bExecute bit = 0ASIF 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), @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 @sprocktext = '' set @skeyfields = 'set @swher eClause = '' SET @sProcText = @sProcText 'IF EXISTS (SELECT * FROM sysobjects WHERE name =' 'lsp_' @sTableName '_Delete' ')' @sCRLFSET @sProcText = @sProcText @sTAB 'DROP PROC lsp_ ' @sTableName ' _Delete ' @sCRLFIF @bExecute = 0 SET @sProcText = @sProcText ' GO ' @sCRLFSET @sProcText = @sProcText @sCRLFPRINT @sProcTextIF @bExecute = 1 EXEC (@
SPROCTEXT) SET @SPROCTEXT = '' set @sproctext = @sproctext '------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------ ' @scrlfset @SPROCTEXT = @ Sproctext '- Delete a single record from' @Stablename @scrlfset @SPROCTEXT = @sproctext '-------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ' @sCRLFSET @sProcText = @sProcText ' CREATE PROC lsp_ ' @sTableName ' _Delete ' @sCRLFDECLARE crKeyFields cursor for SELECT * FROM dbo.fnTableColumnInfo (@sTableName) ORDER BY 2OPEN crKeyFieldsFETCH 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) --de Cimal, Numeric set @skeyfields = @SKEYFIELDS '(' Cast (@ncolumnprecision as var (3)) ',' Cast (@ncolumnscale as varcha (3)) ')' Else IF (@NALTERNATYPE = 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 ENDCLOSE crKeyFieldsDEALLOCATE crKeyFieldsSET @sProcText = @ sProcText @sKeyFields ',' @sCRLFSET @sProcText = @sProcText '@ErrorDescription varchar (100) output' @ sCRLFSET @sProcText = @sProcText 'WITH ENCRYPTION' @sTableName @sCRLFSET @sProcText = @sProcText 'AS' @sCRLFSET @sProcText = @sProcText @sCRLFSET @sProcText = @sProcText 'DELETE' @sTableName @sCRLFSET @sProcText = @sProcText @sWhereClauseSET @sProcText = @sProcText @sCRLF SET @sProcText = @sprocText 'SELECT @errORDESBRIPTION = @@ error' @ Scrlfset @S ProCText = @SPROCTEXT 'IF @ErRordescription! = 0 goto rollbackandreturn' @ Scrlfset @SprockText = @SPrOCText 'else' @ SCRLFSET @SPROCTEXT = @sprocText '
Goto CommitAndReturn ' @ sCRLFSET @sProcText = @sProcText @sCRLF SET @sProcText = @sProcText ' RollbackAndReturn: ' @ sCRLFSET @sProcText = @sProcText ' Rollback Transaction ' @ sCRLFSET @sProcText = @sProcText ' Raiserror ( 'deleting data error' ', 1,1)' @ sCRLFSET @sProcText = @sProcText 'GoTo ReturnResultSet' @ sCRLFSET @sProcText = @sProcText @sCRLF SET @sProcText = @sProcText 'CommitAndReturn:' @ sCRLFSET @sProcText = @sProcText 'Commit Transaction' @ sCRLFSET @sProcText = @sProcText @sCRLF SET @sProcText = @sProcText 'ReturnResultSet:' @ sCRLFSET @sProcText = @sProcText 'Return @ErrorDescription' @ sCRLFSET @sProcText = @sProcText @sCRLFIF @bExecute = 0 SET @sProcText = @sProcText 'GO' @sCRLFPRINT @sProcTextIF @bExecute = 1 EXEC (@sProcText) GOCREATE PROC pr__SYS_MakeInsertRecordProc @sTableName varchar (128), @bExecute bit = 0ASF DBO.FNTABLASPRIMARYKEY (@stablename) = 0 Begin Raiserror ('Procedure Cannot Be CREATED ON A TABLE with no primary key. ', 10, 1) RETURN ENDDECLARE @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 @HASIDENTITITY = 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 =' 'lsp_' @sTableName '_Insert' ')' @sCRLFSET @sProcText = @sProcText @sTAB 'DROP PROC lsp_' @sTableName '_Insert' @sCRLFIF @bExecute = 0 SET @sProcText = @sProcText 'GO' @sCRLFSET @sProcText = @sProcText @sCRLFPRINT @sProcTextIF @bExecute = 1 EXEC (@SPROCTEXT) SET @SPROCTEXT = '' set @sproctext = @sproctext '---------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------- ' @scrlfset @sprocText = @SPROCTEXT '- INSERT A SINGLE RECORD INTO' @Stablename @scrlfset @sproctext = @sproctext '------------------------------------------- ------------------------------- ' @scrlfset @sprocktext = @SPROCTEXT ' CREATE PROC LSP_ ' @stablename '_Insert' @
sCRLFDECLARE crKeyFields cursor for SELECT * FROM dbo.fnTableColumnInfo (@sTableName) ORDER BY 2OPEN crKeyFieldsFETCH 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 ',' @sCRLF SET @sKeyFields = @sKeyFields @STAB '@' @scolumnname '' @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 i F (@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 (@isdefaultity = 0) begin if (@isnull = 1) or (@Sstypename) OR = 'timestamp') SET @sKeyFields = @sKeyFields '= NULL' END FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @ sTypeName, @sDefaultValue ENDCLOSE crKeyFieldsDEALLOCATE crKeyFieldsSET @sProcText = @sProcText @sKeyFields ',' @sCRLFSET @sProcText = @sProcText '@ErrorDescription varchar (100) output' @ sCRLFSET @sProcText = @sProcText 'WITH ENCRYPTION' @sTableName @sCRLFSET @sProcText = @sProcText 'AS' @sCRLFSET @sProcText = @sProcText @sCRLFSET @sProcText = @sProcText 'INSERT' @sTableName '(' @sAllFields ')' @scrlfset @SPROCTEXT = @sprocktext 'VALUES (' @SallParams ')' @scrlfset @SPROCTEXT = @SPROCTEXT @SCRLFSET @SPROCTEXT = @sproc Text @sCRLF SET @sProcText = @sProcText 'Select @ErrorDescription = @@ error' @ sCRLFSET @sProcText = @sProcText 'If @ErrorDescription! = 0 GoTo RollbackAndReturn' @
sCRLFSET @sProcText = @sProcText 'else' @ sCRLFSET @sProcText = @sProcText 'Goto CommitAndReturn' @ sCRLFSET @sProcText = @sProcText @sCRLF SET @sProcText = @sProcText 'RollbackAndReturn:' @ sCRLFSET @sProcText = @sProcText 'Rollback Transaction' @ sCRLFSET @sProcText = @sProcText 'Raiserror (' 'data insertion error' ', 1,1)' @ sCRLFSET @sProcText = @sProcText 'GoTo ReturnResultSet' @ sCRLFSET @ sProcText = @sProcText @sCRLF SET @sProcText = @sProcText 'CommitAndReturn:' @ sCRLFSET @sProcText = @sProcText 'Commit Transaction' @ sCRLFSET @sProcText = @sProcText @sCRLF SET @sProcText = @sProcText ' ReturnResultSet: ' @ sCRLFSET @sProcText = @sProcText ' Return @ErrorDescription ' @ sCRLFIF (@HasIdentity = 1) BEGIN --SET @sProcText = @sProcText ' RETURN SCOPE_IDENTITY () ' @sCRLF SET @sProcText = @ SPROCTEXT @Scrlf Endif @bexecute = 0 set @sprock = @sprocText 'Go' @scrlfprint @SPROCTEXTIF @Bexecute = 1 Exec (@sproctext) GOCR Eate proc PR__SYS_MAKESELECTRECORDPROC @stablename varchar (128), @bexecute bit = 0asif 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 @sprockText = 'set @skeyfields =' set @sslectcLaSe = '' SET @sWhereClause = '' SET @sProcText = @sProcText 'IF EXISTS (SELECT * FROM sysobjects WHERE name =' 'lsp_' @sTableName '_Select' ')' @sCRLFSET @sProcText = @sProcText @sTAB 'DROP PROC prApp_' @sTableName '_Select' @sCRLFIF @bExecute = 0 SET @sProcText = @sProcText 'GO' @sCRLFSET @sProcText = @sProcText @sCRLFPRINT @sProcTextIF @bExecute = 1 EXEC (@SPROCTEXT) SET @SPROCTEXT = '' set @SprockText = @sproctext '------------------------------------------- ------------------------------- ' @scrlfset @sproctext = @SPROCTEXT ' - SELECT A SINGLE RECORD From ' @stablename @scrlfset @sprocktext = @sproctext '
-------------------------------------------------- -------------------------- ' @scrlfset @SPROCTEXT = @SprockText ' Create Proc Prapp_ ' @Stablename '_Select' @ sCRLFDECLARE crKeyFields cursor for SELECT * FROM dbo.fnTableColumnInfo (@sTableName) ORDER BY 2OPEN crKeyFieldsFETCH 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 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 = @sWhereClause 'AND' SET @sWhereClause = @sWhereClause @sTAB @ Scolumnname '= @' @scolumnname @
sCRLF END IF (@sSelectClause = '') SET @sSelectClause = @sSelectClause 'SELECT' ELSE SET @sSelectClause = @sSelectClause ',' @sCRLF SET @sSelectClause = @sSelectClause @sTAB @sColumnName FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue ENDCLOSE crKeyFieldsDEALLOCATE crKeyFieldsSET @sSelectClause = @sSelectClause @sCRLFSET @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' @sCRLFPRINT @sProcTextIF @bExecute = 1 EXEC (@sProcText) GOCREATE PROC pr__SYS_MakeUpdateRecordProc @sTableName varchar ( 128), @bExecute bit = 0ASIF 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), @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 @sprocktext = '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' @sCRLFIF @bExecute = 0 SET @sProcText = @sProcText 'GO' @sCRLFSET @sProcText = @sProcText @sCRLFPRINT @sProcTextIF @bExecute = 1 EXEC (@sproctext) set @sproctext = '' set @sproctext = @sproctext '------------------------------- -------------------------------------------- ' @scrlfset @ SprockText = @SPROCTEXT '- UPDATE A SINGLE RECORD IN' @stablename @SCRLFS ET @sproctext = @sproctext '-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------- ' @scrlfset @sproctext = @SPROCTEXT ' CREATE PROC PRAPP_ ' @Stablename '_Update' @
sCRLFDECLARE crKeyFields cursor for SELECT * FROM dbo.fnTableColumnInfo (@sTableName) ORDER BY 2OPEN crKeyFieldsFETCH 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 = @sWhereCl ause @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 , @sTypeName, @sDefaultValue ENDCLOSE crKeyFieldsDEALLOCATE crKeyFieldsSET @sSetClause = @sSetClause @sCRLFSET @sProcText = @sProcText @sKeyFields @sCRLFSET @sProcText = @sProcText 'AS' @sCRLFSET @sProcText = @sProcText @sCRLFSET @sProcText = @sproctext 'update' @stablename @scrlfset @sprocktext = @SprockText @ssetclauseset @sproctex t = @sProcText @sWhereClauseSET @sProcText = @sProcText @sCRLFIF @bExecute = 0 SET @sProcText = @sProcText 'GO' @sCRLFPRINT @sProcTextIF @bExecute = 1 EXEC (@
sProcText) GOCREATE FUNCTION dbo.fnCleanDefaultValue (@sDefaultValue varchar (4000)) RETURNS varchar (4000) ASBEGIN RETURN SubString (@sDefaultValue, 2, DataLength (@sDefaultValue) -2) ENDGOCREATE 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 COLUMN_NAME = @sColumnName RETURN @sDefaultValueENDGOCREATE 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 .col id = sc.colid WHERE sik.id = @nTableID AND sik.indid = @nIndexID) BEGIN RETURN 1 END RETURN 0ENDGOCREATE 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 systypes t ON c.xtype = t.xtype and c.usertype = t.usertype WHERE c.id = OBJECT_ID (@sTableName) GOCREATE 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 0ndgo / * End SPS Write SPStore Auto * // ****************************************** ************************************************** Function: Decrypt SpStoredate: 2/4 / 2005Modify: WINKING TAN * / CREATE Procedure SP_Decrypt (@objectname varchar (50)) asbeginset NoCount Onbegin TRANDECLARE @Obje ctname1 varchar (100), @ orgvarbin varbinary (8000) declare @ sql1 nvarchar (4000), @ sql2 varchar (8000), @ sql3 nvarchar (4000), @ sql4 nvarchar (4000) DECLARE @ OrigSpText1 nvarchar (4000), @ OrigSpText2 NVARCHAR (4000), @ origsptext3 nvarchar (4000), @Resultsp nvarchar (4000) Declare @i int, @ status int, @ type varchar (10), @parecture INTDECLARE @colid int, @ n int, @ q Int, @ J int, @ K int, @ncrypted int, @ Number INTSELECT @ type = xtype, @ ParentId = Parent_obj from sysobjects where id = Object_ID (@
ObjectName) create table #temp (number int, colid int, ctext varbinary (8000), encrypted int, status int) insert #temp SELECT number, colid, ctext, encrypted, status FROM syscomments WHERE id = object_id (@objectName) select @ number = max (number) from #tempset @ k = 0while @k <= @ number beginif exists (select 1 from syscomments where id = object_id (@objectname) and number = @ k) beginif @ type = 'P'set @ sql1 = (case when @number> 1 then 'ALTER PROCEDURE' @objectName ';' rtrim (@k) 'WITH ENCRYPTION AS' else 'ALTER PROCEDURE' @ objectName 'WITH ENCRYPTION AS' end) if @type = 'Tr'Set @ SQL1 =' ALTER TRIGGER ' @ ObjectName ' on ' Object_name (@parentid) ' with Encryption for Insert Asprint 1 'if @ Type =' fn 'or @ type =' tf 'OR @ Type = 'iF'Set @ SQL1 = (Case @Type when' TF 'TEN' ALTER FUNCTION ' @ObjectName ' (@ a char (1)) Returns @B Table (a varchar (10)) with Encryption as begin insert @B SELECT @A Return End 'when' Fn 'Then'Alter Function' @Objectname '(@ a char (1)) Returns Char 1) with encryption as begin Return @a end'when 'f '1n'alter function' @objectname '(@ a char (1)) Returns Table with encryption as return select @a as a'end) if @ Type = 'V'set @ SQL1 =' Alter View ' @ ObjectName ' with Encryption As SELECT 1 as F'SET @ q =
LEN (@ SQL1) set @ SQL1 = @ SQL1 Replicate ('-', 4000- @ q) SELECT @ SQL2 = Replicate ('-', 8000) set @ SQL3 = 'Exec (@ SQL1'Select @ colid = max (Colid) from #temp where number = @ K set @ n = 1WHILE @n <= ceiling (1.0 * (@ color-1) / 2) and Len (@ SQL3) <= 3996begin set @ SQL3 = @ SQL3 ' @ 'set @ n = @n 1ndset @ SQL3 = @ SQL3 ') 'EXEC SP_EXECUTESQL @ SQL3, N' @ SQL1 NVARCHAR (4000), @ sql1 = @ SQL1, @ = @ SQL2ndset @ = @ SQL2ENDSET @ = @ k = @ k 1endset @ k = 0while @k <= @ number beginif exists (select 1 from syscomments where id = object_id (@objectname) and number = @ k) beginselect @ colid = max (colid) from #temp where number = @ k set @ n = 1while @n <= @ colidbeginselect @ OrigSpText1 = ctext, @ encrypted = encrypted, @ status = status FROM #temp WHERE colid = @ n and number = @ kSET @ OrigSpText3 = (SELECT ctext FROM syscomments WHERE id = object_id (@objectName) and colid = @ n and number = @ k) if @ n = 1beginif @ type = 'P'SET @ OrigSpText2 = (case when @number> 1 then' CREATE PROCEDURE ' @objectName ' ; ' RTRIM ' with encryption as' else 'crete processname ' with encrypti On as 'end) if @ type =' fn 'or @ type =' f '- ??????? set @ OrigspText2 = (Case @Type when' TF 'THEN' CREATE Function ' @
Objectname '(@ a char (1)) Returns @B Table (a varchar (10)) with encryption as begin insert @B select @a return end' when 'fn' dam '@Objectname ' (@ a Char (1)) Returns Char (1) with Encryption As Begin Return @a end'when 'f' Then'create Function ' @ObjectName ' (@ a char (1)) Returns Table with Encryption As Return SELECT @a As aeend) if @ type = 'tr'set @ origsptext2 =' create trigger ' @ Objectname ' on ' Object_name (@parentid) ' with encryption for insert as print 1 'if @ type =' V'Set @ @ Type = 'V'Set @ OrigSpText2 = 'CREATE VIEW' @ objectname 'WITH ENCRYPTION AS SELECT 1 as f'set @ q = 4000-len (@ OrigSpText2) set @ OrigSpText2 = @ OrigSpText2 REPLICATE (' - ', @ q) endelsebeginSET @ OrigSpText2 = REPLICATE ('-', 4000) endset @ i = 1set @Resultsp = replicate (N'A ', (Datalength (@ ptext1) / 2))))))))))))))))))))) " Resultsp, @i, 1, nchar (unicode (@ OrigspText1, @i, 1)) ^ (unicode (@ ptext2, @i, 1)) ^ Unicode (@ OrigspText3, @i, 1))))))) SET @ i = @ i 1ndset @ orgvarbin = CAST (@ OrigsPText1 As Varbinary (8000)) Set @ resultsp = (Case by @ Encrypted = 1 Then @ @ Resultsp Else Convert (NVARCHAR (4000), Case When @ status & 2 = 2 Then Uncompress (@orgvarbin) Else @
