根据表中的字段自动生成存储过程

/*
pr__SYS_MakeDeleteRecordProc 
生成删除数据的存储过程
pr__SYS_MakeInsertRecordProc
生成插入数据的存储过程
PROC pr__SYS_MakeSelectRecordProc
生成查询数据的存储过程
pr__SYS_MakeUpdateRecordProc
生成更新数据的存储过程
sp_decrypt
解密存储过程
*/

/*
Function :   Write SPstore Auto
Date     :   2/4/2005
Modify   :   Winking Tan
*/

CREATE  PROC pr__SYS_MakeDeleteRecordProc
/*
Function   :  Make Delete Record Proc
*/
 @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,
 @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 @sWhereClause = ''

SET  @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''lsp_' + @sTableName + '_Delete'')' + @sCRLF
SET  @sProcText = @sProcText + @sTAB + 'DROP PROC lsp_' + @sTableName + '_Delete' + @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 + '-- Delete a single record from ' + @sTableName + @sCRLF
SET  @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET  @sProcText = @sProcText + 'CREATE PROC lsp_' + @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 + ' ' + @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

 FETCH  NEXT
 FROM  crKeyFields
 INTO  @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
  @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
  @IsIdentity, @sTypeName, @sDefaultValue
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET  @sProcText = @sProcText + @sKeyFields +','+ @sCRLF
SET     @sProcText = @sProcText + '@ErrorDescription varchar(100) output '+@sCRLF
SET  @sProcText = @sProcText + 'WITH ENCRYPTION' + @sTableName + @sCRLF
SET  @sProcText = @sProcText + 'AS' + @sCRLF
SET  @sProcText = @sProcText + @sCRLF
SET  @sProcText = @sProcText + 'DELETE ' + @sTableName + @sCRLF
SET  @sProcText = @sProcText + @sWhereClause
SET     @sProcText = @sProcText + @sCRLF
SET     @sProcText = @sProcText + 'Select @ErrorDescription=@@error'+@sCRLF
SET     @sProcText = @sProcText + 'If @ErrorDescription != 0 GoTo RollbackAndReturn '+@sCRLF
SET     @sProcText = @sProcText + 'else'+@sCRLF
SET     @sProcText = @sProcText + 'Goto CommitAndReturn'+@sCRLF
SET     @sProcText = @sProcText + @sCRLF
SET     @sProcText = @sProcText + 'RollbackAndReturn: '+@sCRLF
SET     @sProcText = @sProcText + 'Rollback Transaction'+@sCRLF
SET     @sProcText = @sProcText + 'Raiserror(''数据删除错误'',1,1) '+@sCRLF
SET     @sProcText = @sProcText + 'GoTo ReturnResultSet'+@sCRLF
SET     @sProcText = @sProcText + @sCRLF
SET     @sProcText = @sProcText + 'CommitAndReturn:  '+@sCRLF
SET     @sProcText = @sProcText + 'Commit Transaction '+@sCRLF
SET     @sProcText = @sProcText + @sCRLF
SET     @sProcText = @sProcText + 'ReturnResultSet:  '+@sCRLF
SET     @sProcText = @sProcText + 'Return @ErrorDescription '+@sCRLF

SET  @sProcText = @sProcText + @sCRLF
IF @bExecute = 0
 SET  @sProcText = @sProcText + 'GO' + @sCRLF

PRINT @sProcText

IF @bExecute = 1
 EXEC (@sProcText)

GO

CREATE  PROC pr__SYS_MakeInsertRecordProc
 @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),
 @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 = 0
SET @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'')' + @sCRLF
SET  @sProcText = @sProcText + @sTAB + 'DROP PROC lsp_' + @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 + '----------------------------------------------------------------------------' + @sCRLF
SET  @sProcText = @sProcText + '-- Insert a single record into ' + @sTableName + @sCRLF
SET  @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET  @sProcText = @sProcText + 'CREATE PROC lsp_' + @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 + ',' + @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 (@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 crKeyFields
DEALLOCATE crKeyFields

SET  @sProcText = @sProcText + @sKeyFields + ',' + @sCRLF
SET     @sProcText = @sProcText + '@ErrorDescription varchar(100) output '+@sCRLF
SET  @sProcText = @sProcText + 'WITH ENCRYPTION' + @sTableName + @sCRLF
SET  @sProcText = @sProcText + 'AS' + @sCRLF
SET  @sProcText = @sProcText + @sCRLF
SET  @sProcText = @sProcText + 'INSERT ' + @sTableName + '(' + @sAllFields + ')' + @sCRLF
SET  @sProcText = @sProcText + 'VALUES (' + @sAllParams + ')' + @sCRLF
SET  @sProcText = @sProcText + @sCRLF
SET     @sProcText = @sProcText + @sCRLF
SET     @sProcText = @sProcText + 'Select @ErrorDescription=@@error'+@sCRLF
SET     @sProcText = @sProcText + 'If @ErrorDescription != 0 GoTo RollbackAndReturn '+@sCRLF
SET     @sProcText = @sProcText + 'else'+@sCRLF
SET     @sProcText = @sProcText + 'Goto CommitAndReturn'+@sCRLF
SET     @sProcText = @sProcText + @sCRLF
SET     @sProcText = @sProcText + 'RollbackAndReturn: '+@sCRLF
SET     @sProcText = @sProcText + 'Rollback Transaction'+@sCRLF
SET     @sProcText = @sProcText + 'Raiserror(''数据插入错误'',1,1) '+@sCRLF
SET     @sProcText = @sProcText + 'GoTo ReturnResultSet'+@sCRLF
SET     @sProcText = @sProcText + @sCRLF
SET     @sProcText = @sProcText + 'CommitAndReturn:  '+@sCRLF
SET     @sProcText = @sProcText + 'Commit Transaction '+@sCRLF
SET     @sProcText = @sProcText + @sCRLF
SET     @sProcText = @sProcText + 'ReturnResultSet:  '+@sCRLF
SET     @sProcText = @sProcText + 'Return @ErrorDescription '+@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

CREATE     PROC 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,
 @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 @sSelectClause = ''
SET @sWhereClause = ''

SET  @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''lsp_' + @sTableName + '_Select'')' + @sCRLF
SET  @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, @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
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET  @sSelectClause = @sSelectClause + @sCRLF

SET  @sProcText = @sProcText + @sKeyFields + @sCRLF
SET  @sProcText = @sProcText + 'AS' + @sCRLF
SET  @sProcText = @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

CREATE         PROC 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,
 @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'')' + @sCRLF
SET  @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, @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 = @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
 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

CREATE FUNCTION dbo.fnCleanDefaultValue(@sDefaultValue varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
 RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2)
END

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  COLUMN_NAME = @sColumnName

 RETURN  @sDefaultValue

END

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

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 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)

GO

CREATE FUNCTION dbo.fnTableHasPrimaryKey(@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
/*End  SPS Write SPstore Auto*/

/**********************************************************************
Function   :Decrypt SPstore
Date       :2/4/2005
Modify     :Winking Tan
*/
create   PROCEDURE sp_decrypt(@objectName varchar(50))
AS
begin
set nocount on

begin tran
declare @objectname1 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),@parentid int
declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int
select @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 #temp
set @k=0

while @k<=@number
begin
if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
if @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 AS PRINT 1 '

if @type='FN' or @type='TF' or @type='IF'
set @sql1=(case @type when 'TF' then
'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 'IF' then
'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=1
while @n<=CEILING(1.0*(@colid-1)/2) and len(@sQL3)<=3996
begin
set @sql3=@sql3+'+@'
set @n=@n+1
end
set @sql3=@sql3+')'
exec sp_executesql @sql3,N'@Sql1 nvarchar(4000),@ varchar(8000)',@sql1=@sql1,@=@sql2

end
set @k=@k+1
end

set @k=0
while @k<=@number
begin

if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
select @colid=max(colid) from #temp where number=@k
set @n=1

while @n<=@colid
begin
select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp  WHERE colid=@n and number=@k

SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n and number=@k)
if @n=1
begin
if @type='P'
SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
                       else 'CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
                       end)

if @type='FN' or @type='TF' or @type='IF'--???????
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' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
when 'IF' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
end)

if @type='TR'
set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '

if @type='V'
set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f'

set @q=4000-len(@OrigSpText2)
set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
end
else
begin
SET @OrigSpText2=REPLICATE('-', 4000)
end
SET @i=1

SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))

WHILE @i<=datalength(@OrigSpText1)/2
BEGIN

SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
                                (UNICODE(substring(@OrigSpText2, @i, 1)) ^
                                UNICODE(substring(@OrigSpText3, @i, 1)))))
 SET @i=@i+1
END
set @orgvarbin=cast(@OrigSpText1 as varbinary(8000))
set @resultsp=(case when @encrypted=1
                    then @resultsp
                    else convert(nvarchar(4000),case when @status&2=2 then uncompress(@orgvarbin) else @orgvarbin end)
               end)
print @resultsp

set @n=@n+1

end

end
set @k=@k+1
end

drop table #temp
rollback tran
end

GO