转的sqlserver随机密码 

  1 http://www.sqlservercentral.com/articles/SQL+Puzzles/2878/
  2 
  3 CREATE PROCEDURE dbo.uspCreatePassword(    
  4   @UpperCaseItems SMALLINT--指定含有的大写个数
  5   ,    @LowerCaseItems SMALLINT--指定含有的小写个数
  6   ,    @NumberItems SMALLINT--指定含有的数字个数
  7   ,    @SpecialItems SMALLINT)--指定含有的特殊字符个数
  8  AS  
  9  SET NOCOUNT ON 
 10  DECLARE @UpperCase VARCHAR(26)
 11         , @LowerCase VARCHAR(26)
 12           , @Numbers VARCHAR(10)
 13           , @Special VARCHAR(13)
 14           , @Temp VARCHAR(8000)
 15           , @Password VARCHAR(8000)
 16           , @i SMALLINT
 17           , @c VARCHAR(1)
 18           , @v TINYINT 
 19 -- Set the default items in each group of characters
 20 SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 21     ,    @LowerCase = 'abcdefghijklmnopqrstuvwxyz'
 22      ,    @Numbers = '0123456789'
 23      ,    @Special = '!@#$%&*()_+-='
 24      ,    @Temp = ''
 25      ,    @Password = '' 
 26 -- Enforce some limits on the length of the password
 27 IF @UpperCaseItems > 20
 28   SET @UpperCaseItems = 20 
 29 IF @LowerCaseItems > 20
 30   SET @LowerCaseItems = 20 
 31 IF @NumberItems > 20    
 32   SET @NumberItems = 20 
 33 IF @SpecialItems > 20    
 34   SET @SpecialItems = 20 
 35 
 36 -- Get the Upper Case Items
 37 SET @i = ABS(@UpperCaseItems) 
 38 WHILE @i > 0 AND LEN(@UpperCase) > 0
 39   SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1
 40   ,    @c = SUBSTRING(@UpperCase, @v, 1)
 41   ,    @UpperCase = CASE 
 42        WHEN @UpperCaseItems < 0 
 43             THEN STUFF(@UpperCase, @v, 1, '') 
 44          ELSE @UpperCase 
 45   END
 46   ,    @Temp = @Temp + @c
 47   ,    @i = @i - 1 
 48 -- Get the Lower Case Items
 49 SET @i = ABS(@LowerCaseItems) 
 50 WHILE @i > 0 AND LEN(@LowerCase) > 0    
 51   SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1
 52   ,    @c = SUBSTRING(@LowerCase, @v, 1)
 53   ,    @LowerCase = CASE 
 54        WHEN @LowerCaseItems < 0 
 55            THEN STUFF(@LowerCase, @v, 1, '') 
 56          ELSE @LowerCase 
 57   END
 58   ,    @Temp = @Temp + @c
 59   ,    @i = @i - 1 
 60 
 61 -- Get the Number Items
 62 SET @i = ABS(@NumberItems) 
 63 WHILE @i > 0 AND LEN(@Numbers) > 0    
 64   SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1
 65   ,    @c = SUBSTRING(@Numbers, @v, 1)
 66   ,    @Numbers = CASE 
 67        WHEN @NumberItems < 0 
 68            THEN STUFF(@Numbers, @v, 1, '') 
 69          ELSE @Numbers 
 70   END
 71   ,    @Temp = @Temp + @c
 72   ,    @i = @i - 1 
 73   
 74 -- Get the Special Items
 75 SET @i = ABS(@SpecialItems) 
 76 WHILE @i > 0 AND LEN(@Special) > 0    
 77   SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1
 78   ,    @c = SUBSTRING(@Special, @v, 1)
 79   ,    @Special = CASE 
 80        WHEN @SpecialItems < 0 
 81            THEN STUFF(@Special, @v, 1, '') 
 82         ELSE @Special 
 83   END
 84   ,    @Temp = @Temp + @c
 85   ,    @i = @i - 1 
 86   
 87 -- Scramble the order of the selected items
 88 WHILE LEN(@Temp) > 0    
 89   SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1
 90   ,    @Password = @Password + SUBSTRING(@Temp, @v, 1)
 91   ,    @Temp = STUFF(@Temp, @v, 1, '') 
 92 
 93 SELECT @Password
 94 
 95 
 96 
 97 /*
 98 
 99 --
100 exec uspCreatePassword 2,2,2,2
101 
102 ----------
103 MT&4g(5j
104 
105 
106 exec uspCreatePassword 2,2,4,0
107 
108 ------------
109 38Io84Vw
110 
111 */

posted on 2012-05-16 21:59  小白白小  阅读(399)  评论(0)    收藏  举报