大旨提醒:急需:要求转移下边包车型地铁系列号,前半有些是yyyymmdd格式的年月日光阴数字,后半有的则是每天都从1逐项增加的数字,位数要稳住,中间不足的补0

USE MASTER GO IF EXISTS(SELECT * FROM dbo.sysdatabases WHERE
name=’my_test_database’) DROP DATABASE [my_test_database] GO
CREATE DATABASE [my_test_database] GO USE [my_test_database] GO
CREATE TABLE [my_table] ([my_id] VARCHACR-V(16卡塔尔(قطر‎卡塔尔国 GO –存款和储蓄进度开头 :
CREATE PROCEDURE get_new_id @NEW_ID VARCHATiggo(16卡塔尔(قطر‎ OUTPUT AS BEGIN
DECLARE @DATE DATETIME DECLARE @YYYY VARCHAENCORE(4卡塔尔国 DECLARE @MM VARCHA奥德赛(2State of QatarDECLARE @DD VARCHAPRADO(2State of Qatar –保存得到的近些日子时间 : SET @DATE = GETDATE(卡塔尔 SET
@YYYY = DATEPART(yyyy, @DATE卡塔尔 SET @MM = DATEPART(mm, @DATE卡塔尔 SET @DD =
DATEPART(dd, @DATE卡塔尔 –位数相当不足的近来补0 SET @YYYY = REPLICATE(‘0’, 4 –
LEN(@YYYY)卡塔尔国 + @YYYY SET @MM = REPLICATE(‘0’, 2 – LEN(@MM卡塔尔(قطر‎State of Qatar + @MM SET @DD
= REPLICATE(‘0′, 2 – LEN(@DD卡塔尔国卡塔尔国 + @DD –抽出表中当昨日期的本来就有的最大ID
SET @NEW_ID = NULL SELECT TOP 1 @NEW_ID = [my_id] FROM
[my_table] WHERE [my_id] LIKE @YYYY+@MM+@DD+’%’ ORDER BY
[my_id] DESC –固然未收取来 IF @NEW_ID IS NULL
–表明还没曾当前几日期的数码,则直接从1方始编号 SET @NEW_ID =
(@YYYY+@MM+@DD+’00000001’卡塔尔国 –假若抽出来了 : ELSE BEGIN DECLARE @NUM
VARCHAWrangler(8卡塔尔(قطر‎ –抽取最大的号码加上1 : SET @NUM = CONVERT(VARCHA瑞虎,
(CONVERT(INT, 奥迪Q5IGHT(@NEW_ID, 8State of Qatar卡塔尔国 + 1卡塔尔(قطر‎卡塔尔(قطر‎–因为经过类型转变,错过了高位的0,必要补上 SET @NUM = REPLICATE(‘0’, 8

USE MASTER
GO
IF EXISTS(
       SELECT *
       FROM   sysobjects
       WHERE  [type] = ‘FN’ AND [name] = ‘kymp_岁月转字符’
   )
BEGIN
    DROP FUNCTION kymp_时刻转字符
END
GO
CREATE FUNCTION kymp_日子转字符
(
@date DATETIME, –时间
@type VARCHAR(10) –类型
)
RETURNS VARCHAR(4)
WITH ENCRYPTION
AS
BEGIN
DECLARE @retdate VARCHAR(4), @today DATETIME
SET @retdate = ”
SELECT @retdate = CASE RTRIM(LTRIM(@type))
                      WHEN ‘yy’ THEN CAST(YEAR(@date) AS VARCHAR(4)) –取年份
                      WHEN ‘mm’ THEN –取月份
                           CASE 
                                WHEN MONTH(@date) > 9 THEN CAST(MONTH(@date) AS VARCHAR(2))
                                ELSE ‘0’ + CAST(MONTH(@date) AS VARCHAR(2))
                           END
                      WHEN ‘dd’ THEN –取天数
                           CASE 
                                WHEN DAY(@date) > 9 THEN CAST(DAY(@date) AS VARCHAR(2))
                                ELSE ‘0’ + CAST(DAY(@date) AS VARCHAR(2))
                           END
                      WHEN ‘hh’ THEN –取小时
                           CASE 
                                WHEN DATEPART(hh, @date) > 9 THEN CAST(DATEPART(hh, @date) AS VARCHAR(2))
                                ELSE ‘0’ + CAST(DATEPART(hh, @date) AS VARCHAR(2))
                           END
                      WHEN ‘nn’ THEN –取分
                           CASE 
                                WHEN DATEPART(mi, @date) > 9 THEN CAST(DATEPART(mi, @date) AS VARCHAR(2))
                                ELSE ‘0’ + CAST(DATEPART(mi, @date) AS VARCHAR(2))
                           END
                      WHEN ‘ss’ THEN –取秒
                           CASE 
                                WHEN DATEPART(ss, @date) > 9 THEN CAST(DATEPART(ss, @date) AS VARCHAR(2))
                                ELSE ‘0’ + CAST(DATEPART(ss, @date) AS VARCHAR(2))
                           END
                      ELSE ”
                 END
RETURN @retdate
END
GO
–select dbo.kymp_岁月转字符(getdate(卡塔尔,’ss’卡塔尔
 
GO
IF EXISTS(
       SELECT *
       FROM   sysobjects
       WHERE  [type] = ‘P’ AND [name] = ‘database_backup’
   )
BEGIN
    DROP PROCEDURE database_backup
END
GO
CREATE PROCEDURE database_backup
@database VARCHA汉兰达(1000卡塔尔(قطر‎, –要备份的数据库名称
@database_dir VARCHA奥迪Q3(1000卡塔尔国, –备份的目录路径
@ispassword INT, –是或不是加密压缩文件 1:表示加密,0:表示不加密 暗中认可不加密
@password VARCHA中华V(16卡塔尔, –解压密码
@output VARCHA陆风X8(100卡塔尔国 OUTPUT –重返结果
WITH ENCRYPTION
AS
DECLARE @datefile  NVARCHA翼虎(1000卡塔尔(قطر‎, — 备份数据库的全名:数据库名+时间
       @bakfile   NVARCHASportage(1000卡塔尔国, — 备份文件名  加.bak后缀
       @rarfile   NVARCHALAND(1000卡塔尔国, — 压缩文件名 
       @rarcmd    NVARCHAGL450(1000卡塔尔国 — shell字符命令
SELECT @database AS 数据库名称, @database_dir AS 备份路线, CASE @ispassword
                                                               WHEN 1 THEN 
                                                                    ‘加密’
                                                               WHEN 0 THEN 
                                                                    ‘不加密’
                                                          END AS 是或不是加密, 
      @password AS 压缩密码
/*    对一一参数的论断      */
— 推断数据库
IF (@database IS NULL) OR (@database = ”)
BEGIN
   SELECT @output = ‘备份战败:数据库无法为空’
   RETURN
END
— 决断数据库
—-   判别密码
IF @ispassword IS NULL
BEGIN
   SET @ispassword = 0
END
ELSE 
IF @ispassword = 1 — 借使要加密文件,必需提交加密密码
BEGIN
   IF (@password IS NULL) OR (@password = ”)
   BEGIN
       SET @output = ‘备份退步:借使要给减少的备份文件加密,密码不能够为空’
       RETURN
   END
END
—-   推断密码
—- 剖断路线
IF (@database_dir IS NULL) OR (@database_dir = ”)
BEGIN
   SET @output = ‘备份战败:备份文件路线不能够为空’
   RETURN
END
—- 剖断路线
/*    对一一参数的判别      */
IF EXISTS(
      SELECT *
      FROM   sysdatabases
      WHERE  [name] = @database
  )
BEGIN
   /*  文件夹路线剖断   */
   SET @rarcmd = ‘dir ‘ + @database_dir
   CREATE TABLE #t
   (
    [output] VARCHAR(100)
   )
   INSERT INTO #t
   EXEC xp_cmdshell @rarcmd — 实行cmdshell命令,查找路线并把结果插入#t表
   IF EXISTS(
          SELECT *
          FROM   #t
          WHERE  [output] LIKE ‘%找不到%’
      卡塔尔–找不到路径就创办路线
   BEGIN
       SET @rarcmd = ‘md ‘ + @database_dir
       EXEC xp_cmdshell @rarcmd — 实践cmdshell命令,创设路线
   END
   
   SET @rarcmd = ‘dir ‘ + @database_dir —- 再度判别文件路线是不是成立成功(成功的门道不能够在系统路线中卡塔尔(قطر‎
   DELETE #t
   INSERT INTO #t
   EXEC xp_cmdshell @rarcmd — 推行命令
   IF EXISTS(
          SELECT *
          FROM   #t — 备份路线不可能在系统路线中
          WHERE  ([output] LIKE ‘%找不到%’) OR ([output] LIKE ‘%system32%’)
      )
   BEGIN
       DROP TABLE #t
       SET @output = ‘备份退步:传入的文本夹路线不对’
       RETURN
   END
   
   DROP TABLE #t
   /*  文件夹路线决断   */
   /*   备份模块    select getdate(卡塔尔(قطر‎*/
   –备份文件名  数据库名+系统当下时间+.bak
   SET @datefile = @database + ‘_backup_’ + dbo.kymp_光阴转字符(GETDATE(卡塔尔, ‘yy’卡塔尔国
       + dbo.kymp_时间转字符(GETDATE(卡塔尔, ‘mm’卡塔尔(قطر‎ + dbo.kymp_光阴转字符(GETDATE(卡塔尔(قطر‎, ‘dd’卡塔尔(قطر‎
       + dbo.kymp_时间转字符(GETDATE(State of Qatar, ‘hh’卡塔尔(قطر‎ + dbo.kymp_日子转字符(GETDATE(卡塔尔(قطر‎, ‘nn’State of Qatar
       + dbo.kymp_岁月转字符(GETDATE(卡塔尔, ‘ss’卡塔尔(قطر‎
   
   SET @bakfile = @database_dir + @datefile + ‘.bak’
   SET @rarfile = @database_dir + ” + @datefile + ‘.rar’ — 压缩的rar文件
   BACKUP DATABASE @database TO DISK = @bakfile WITH INIT , NOUNLOAD , NAME 
   = N’H奥迪Q3数据备份’, NOSKIP , STATS = 10, NOFORMAT
   IF @ispassword = 1 —  rar加密管理
   BEGIN
       SET @rarcmd = ‘C:\Progra1\WinRAR\WinRAR.exe a  -p’ + @password + 
           ‘ -df -ep1 ‘ + @rarfile + ‘ ‘ + @bakfile
       
       SET @output = ‘备份成功:已加密压缩备份文件,密码是:’ + @password
   END
   ELSE 
   IF @ispassword = 0 — rar不加密管理
   BEGIN
       SET @output = ‘备份成功:以不加密的不二秘籍减少了备份文件’
       SET @rarcmd = ‘C:\progra1\WinRAR\WinRAR.exe a  -df -ep1 ‘ + @rarfile
           + ‘ ‘ + @bakfile
   END
   /*  备份模块  */
   EXEC MASTER..xp_cmdshell @rarcmd –执行cmdshell命令,压缩备份文件
END
ELSE
BEGIN
   SELECT @output = ‘备份战败:未有这一个数目库’
END
GO
DECLARE @rt VARCHAR(100)
EXEC database_backup ‘QQ_data’, ‘D:1\’, 1, ‘2’, @rt OUTPUT
SELECT @rt AS 实行结果
GO
–sp_helptext database_backup
 

[推荐]得到三个加以顾客接纳了的权柄脚本

须求:需求转换下边包车型大巴行列号,前半有的是yyyymmdd格式的年月日时刻数字,后半局地则是天天都从1相继拉长的数字,位数要定点,中间不足的补0。
在SQL Server 二零零一数据库中测验后透过如下代码,成效完毕如下:

  • LEN(@NUMState of QatarState of Qatar + @NUM –最终回来日期加编号 : SET @NEW_ID = @YYYY+@MM+@DD
  • @NUM END END GO –实施21回调用及插入数据测验 : DECLARE @N INT SET @N
    = 0 WHILE @N 20 BEGIN DECLARE @NEW_ID VARCHAR(16) EXECUTE get_new_id
    @NEW_ID OUTPUT INSERT INTO [my_table] ([my_id]) VALUES (@NEW_ID)
    SET @N = @N + 1 END SELECT * FROM [my_table] GO –输出结果

——通过文化分享树立个人品牌。

USE MASTER

收获一个加以客户接纳了的权力脚本,直接上代码,自身商量。

GO

     SET ANSI_NULLS ON

IF EXISTS(SELECT * FROM dbo.sysdatabases WHERE name=my_test_database)

GO
SET QUOTED_IDENTIFIER ON
GO

DROP DATABASE [my_test_database]

CREATE PROCEDURE [dbo].[p_user_permissions_script_get] 

— OBJECT NAME :dbo.p_user_permissions_script_get
— AUTHOR: EricHu
— DATE: 01/05/2012
— INPUT PARAMETERS:         

GO

    @userName VARCHAR(500)

— OUTPUT PARAMETERS: none
— DEPENDENCIES: none
— DESCRIPTION: Used to script out permissions for a given user

CREATE DATABASE [my_test_database]

— MODIFICATION HISTORY: 

AS

SET NOCOUNT ON

DECLARE @DatabaseUserName [SYSNAME];

SET @DatabaseUserName = @userName;

DECLARE @errStatement   VARCHAR(1000),
        @msgStatement   VARCHAR(1000),
        @DatabaseUserID SMALLINT,
        @ServerUserName SYSNAME,
        @RoleName       VARCHAR(1000),
        @ObjectID       INT,
        @ObjectName     VARCHAR(1000),
        @StateDesc       VARCHAR(1000),
        @permissionName VARCHAR(1000)
        
        
      

SELECT @DatabaseUserID = su.[uid],
       @ServerUserName = sl.[loginname]
FROM   dbo.[sysusers] su
       INNER JOIN [master].dbo.[syslogins] sl
         ON su.[sid] = sl.[sid]
WHERE  su.[name] = @DatabaseUserName

IF @DatabaseUserID IS NULL
  BEGIN
      SET @errStatement = ‘User ‘ + @DatabaseUserName + ‘ does not exist in ‘ + DB_NAME() 
                        + CHAR(13) + ‘Please provide the name of a current user in ‘ + DB_NAME() 
                        + ‘ you wish to script.’

      RAISERROR(@errStatement,
                16,
                1)
  END
ELSE
  BEGIN
      SET @msgStatement = ‘–Security creation script for user ‘ + @ServerUserName + CHAR(13) 
                        + ‘–Created At: ‘ + CONVERT(VARCHAR, GETDATE(), 100) 
                        + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ‘:’, ”) + CHAR(13) 
                        + ‘–Created By: ‘ + SUSER_NAME() + CHAR(13) + ‘–Add User To Database’ 
                        + CHAR(13) + ‘USE [‘ + DB_NAME() + ‘]’ + CHAR(13) 
                        + ‘EXEC [sp_grantdbaccess]’ + CHAR(13) + CHAR(9) 
                        + ‘@loginame = ”’ + @ServerUserName + ”’,’ + CHAR(13) + CHAR(9) 
                        + ‘@name_in_db = ”’ + @DatabaseUserName + ”” + ‘;’+ CHAR(13) + ‘GO’ 
                        + CHAR(13) + ‘–Add User To Roles’

      PRINT @msgStatement

      DECLARE _sysusers CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
        SELECT [name]
        FROM   [dbo].[sysusers]
        WHERE  [uid] IN (SELECT [groupuid]
                         FROM   [dbo].[sysmembers]
                         WHERE  [memberuid] = @DatabaseUserID)

      OPEN _sysusers

      FETCH NEXT FROM _sysusers INTO @RoleName

      WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @msgStatement = ‘EXEC [sp_addrolemember]’ + CHAR(13) + CHAR(9) + ‘@rolename = ”’ 
                              + @RoleName + ”’,’ + CHAR(13) + CHAR(9) + ‘@membername = ”’ 
                              + @DatabaseUserName + ””  + ‘;’ ;

            PRINT @msgStatement

            FETCH NEXT FROM _sysusers INTO @RoleName
        END
        
        
        CLOSE _sysusers;
        
        DEALLOCATE _sysusers;
        
       –Database level perms;
       
       PRINT ‘–Set Database level Permissions’;
       DECLARE _databaselevelperms CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR      
        SELECT 
             sdp.state_desc,
             sdp.permission_name
        FROM 
            sys.database_permissions sdp WITH(NOLOCK)
            
            JOIN sysusers  su WITH(NOLOCK)
                ON su.uid = sdp.grantee_principal_id
                
        WHERE 
            su.name = @userName
            AND sdp.class_desc = ‘DATABASE’;
    

      OPEN _databaselevelperms;
      
      FETCH NEXT FROM _databaselevelperms INTO @StateDesc, @PermissionName;
      
      WHILE @@FETCH_STATUS = 0 
      BEGIN 
      
        PRINT @StateDesc  + CHAR(13) + CHAR(9) + @PermissionName  + CHAR(13) + CHAR(9) 
              + ‘TO ‘ + @userName + ‘;’;
        
        FETCH NEXT FROM _databaselevelperms INTO @StateDesc, @PermissionName
        
      END
      
      CLOSE _databaselevelperms;

      DEALLOCATE _databaselevelperms;

      SET @msgStatement = ‘GO’ + CHAR(13) + ‘–Set Object Specific Permissions’

      PRINT @msgStatement;

      DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
        SELECT DISTINCT( [sysobjects].[id] ),
                       ‘[‘ + USER_NAME([sysobjects].[uid]) + ‘].[‘ + [sysobjects].[name] + ‘]’
        FROM   [dbo].[sysprotects]
               INNER JOIN [dbo].[sysobjects]
                 ON [sysprotects].[id] = [sysobjects].[id]
        WHERE  [sysprotects].[uid] = @DatabaseUserID;

      OPEN _sysobjects;

      FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName;

      WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @msgStatement = ”;

            IF EXISTS(SELECT 1
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 193
                             AND [protecttype] = 205)
              SET @msgStatement = @msgStatement + ‘SELECT,’;

            IF EXISTS(SELECT 1
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 195
                             AND [protecttype] = 205)
              SET @msgStatement = @msgStatement + ‘INSERT,’;

            IF EXISTS(SELECT 1
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 197
                             AND [protecttype] = 205)
              SET @msgStatement = @msgStatement + ‘UPDATE,’;

            IF EXISTS(SELECT 1
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 196
                             AND [protecttype] = 205)
              SET @msgStatement = @msgStatement + ‘DELETE,’;

            IF EXISTS(SELECT 1
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 224
                             AND [protecttype] = 205)
              SET @msgStatement = @msgStatement + ‘EXECUTE,’;

            IF EXISTS(SELECT 1
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 26
                             AND [protecttype] = 205)
              SET @msgStatement = @msgStatement + ‘REFERENCES,’;

            IF LEN(@msgStatement) > 0
              BEGIN
                  IF RIGHT(@msgStatement, 1) = ‘,’
                    SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) – 1);

                  SET @msgStatement = ‘GRANT’ + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) 
                                    + CHAR(9) + ‘ON ‘ + @ObjectName + CHAR(13) + CHAR(9) + ‘TO ‘ 
                                    + @DatabaseUserName + ‘;’ ;

                  PRINT @msgStatement;
              END

            SET @msgStatement = ”;

            IF EXISTS(SELECT 1
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 193
                             AND [protecttype] = 206)
              SET @msgStatement = @msgStatement + ‘SELECT,’

            IF EXISTS(SELECT 1
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 195
                             AND [protecttype] = 206)
              SET @msgStatement = @msgStatement + ‘INSERT,’;

            IF EXISTS(SELECT 1
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 197
                             AND [protecttype] = 206)
              SET @msgStatement = @msgStatement + ‘UPDATE,’;

            IF EXISTS(SELECT 1
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 196
                             AND [protecttype] = 206)
              SET @msgStatement = @msgStatement + ‘DELETE,’

            IF EXISTS(SELECT 1
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 224
                             AND [protecttype] = 206)
              SET @msgStatement = @msgStatement + ‘EXECUTE,’;

            IF EXISTS(SELECT *
                      FROM   [dbo].[sysprotects]
                      WHERE  [id] = @ObjectID
                             AND [uid] = @DatabaseUserID
                             AND [action] = 26
                             AND [protecttype] = 206)
              SET @msgStatement = @msgStatement + ‘REFERENCES,’;

            IF LEN(@msgStatement) > 0
              BEGIN
                  IF RIGHT(@msgStatement, 1) = ‘,’
                    SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) – 1)

                  SET @msgStatement = ‘DENY’ + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) 
                                    + CHAR(9) + ‘ON ‘ + @ObjectName + CHAR(13) + CHAR(9) + ‘TO ‘ 
                                    + @DatabaseUserName + ‘;’ ;

                  PRINT @msgStatement;
              END

            FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName;
        END

      CLOSE _sysobjects;

      DEALLOCATE _sysobjects;
      
   

      PRINT ‘GO’
  END 

SET NOCOUNT OFF

RETURN 0

      运维结果实举例下:

 EXEC [p_user_permissions_script_get] ‘dbo’

–Security creation script for user sa
–Created At: 01 13 2012  4:37PM163729
–Created By: sa
–Add User To Database
USE [DB_TEST]
EXEC [sp_grantdbaccess]
    @loginame = ‘sa’,
    @name_in_db = ‘dbo’;
GO
–Add User To Roles
EXEC [sp_addrolemember]
    @rolename = ‘db_owner’,
    @membername = ‘dbo’;
–Set Database level Permissions
GRANT
    CONNECT
    TO dbo;
GO
–Set Object Specific Permissions
GO

GO

USE [my_test_database]

GO

CREATE TABLE [my_table] ([my_id] VARCHAR(16))

GO

–存款和储蓄进度开始

CREATE PROCEDURE get_new_id

@NEW_ID VARCHAR(16) OUTPUT

AS

BEGIN

DECLARE @DATE DATETIME

DECLARE @YYYY VARCHAR(4)

DECLARE @MM VARCHAR(2)

DECLARE @DD VARCHAR(2)

–保存得到的当下时光

SET @DATE = GETDATE()

发表评论

电子邮件地址不会被公开。 必填项已用*标注