sql server 2005 几个常用的存储过程或函数

 

------------------------------------------------------

--功能：十六进制转换为文件号 

--使用：SELECT dbo.convert_page_nums(0xCF0400000100)  

------------------------------------------------------  

CREATE FUNCTION convert_page_nums (@page_num binary(6))

   RETURNS varchar(11)

 

AS

  BEGIN

   RETURN(convert(varchar(2), (convert(int, substring(@page_num, 6, 1))

          * power(2, 8)) +

             (convert(int, substring(@page_num, 5, 1)))) + ':' +

               convert(varchar(11),

   (convert(int, substring(@page_num, 4, 1)) * power(2, 24)) +

   (convert(int, substring(@page_num, 3, 1)) * power(2, 16)) +

   (convert(int, substring(@page_num, 2, 1)) * power(2, 8)) +

   (convert(int, substring(@page_num, 1, 1)))) )

  END

 

 

--------------------------------------------------------------------

--功能：签名函数

--作者：

--时间：年月日

--使用：

--SELECT dbo.fn_SQLSigTSQL

--  (N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78', 4000);

------------------------------------------------------------------- 

 

  IF OBJECT_ID('dbo.fn_SQLSigTSQL') IS NOT NULL

  DROP FUNCTION dbo.fn_SQLSigTSQL;

GO

 

CREATE FUNCTION dbo.fn_SQLSigTSQL

  (@p1 NTEXT, @parselength INT = 4000)

RETURNS NVARCHAR(4000)

 

 

AS

BEGIN

  DECLARE @pos AS INT;

  DECLARE @mode AS CHAR(10);

  DECLARE @maxlength AS INT;

  DECLARE @p2 AS NCHAR(4000);

  DECLARE @currchar AS CHAR(1), @nextchar AS CHAR(1);

  DECLARE @p2len AS INT;

 

  SET @maxlength = LEN(RTRIM(SUBSTRING(@p1,1,4000)));

  SET @maxlength = CASE WHEN @maxlength > @parselength

                     THEN @parselength ELSE @maxlength END;

  SET @pos = 1;

  SET @p2 = '';

  SET @p2len = 0;

  SET @currchar = '';

  set @nextchar = '';

  SET @mode = 'command';

 

  WHILE (@pos <= @maxlength)

  BEGIN

    SET @currchar = SUBSTRING(@p1,@pos,1);

    SET @nextchar = SUBSTRING(@p1,@pos+1,1);

    IF @mode = 'command'

    BEGIN

      SET @p2 = LEFT(@p2,@p2len) + @currchar;

      SET @p2len = @p2len + 1 ;

      IF @currchar IN (',','(',' ','=','<','>','!')

        AND @nextchar BETWEEN '0' AND '9'

      BEGIN

        SET @mode = 'number';

        SET @p2 = LEFT(@p2,@p2len) + '#';

        SET @p2len = @p2len + 1;

      END

      IF @currchar = ''''

      BEGIN

        SET @mode = 'literal';

        SET @p2 = LEFT(@p2,@p2len) + '#''';

        SET @p2len = @p2len + 2;

      END

    END

    ELSE IF @mode = 'number' AND @nextchar IN (',',')',' ','=','<','>','!')

      SET @mode= 'command';

    ELSE IF @mode = 'literal' AND @currchar = ''''

      SET @mode= 'command';

 

    SET @pos = @pos + 1;

  END

  RETURN @p2;

END

GO

 

 

------------------------------------------------

--功能：trace跟踪

--时间：年月日

-------------------------------------------------

 

 

SET NOCOUNT ON;

USE master;

GO

 

IF OBJECT_ID('dbo.sp_perfworkload_trace_start') IS NOT NULL

  DROP PROC dbo.sp_perfworkload_trace_start;

GO

-- Creation script for the sp_perfworkload_trace_start stored procedure

CREATE PROC dbo.sp_perfworkload_trace_start

  @dbid      AS INT,

  @tracefile AS NVARCHAR(254),

  @traceid   AS INT OUTPUT

AS

-- Create a Queue

DECLARE @rc          AS INT;

DECLARE @maxfilesize AS BIGINT;

 

SET @maxfilesize = 5;

 

EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULL

IF (@rc != 0) GOTO error;

 

-- Client side File and Table cannot be scripted

 

-- Set the events

DECLARE @on AS BIT;

SET @on = 1;

exec sp_trace_setevent @TraceID, 10, 1, @on

exec sp_trace_setevent @TraceID, 10, 3, @on

exec sp_trace_setevent @TraceID, 10, 11, @on

exec sp_trace_setevent @TraceID, 10, 35, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 10, 13, @on

exec sp_trace_setevent @TraceID, 43, 1, @on

exec sp_trace_setevent @TraceID, 43, 3, @on

exec sp_trace_setevent @TraceID, 43, 11, @on

exec sp_trace_setevent @TraceID, 43, 35, @on

exec sp_trace_setevent @TraceID, 43, 12, @on

exec sp_trace_setevent @TraceID, 43, 28, @on

exec sp_trace_setevent @TraceID, 43, 13, @on

exec sp_trace_setevent @TraceID, 45, 1, @on

exec sp_trace_setevent @TraceID, 45, 3, @on

exec sp_trace_setevent @TraceID, 45, 11, @on

exec sp_trace_setevent @TraceID, 45, 35, @on

exec sp_trace_setevent @TraceID, 45, 12, @on

exec sp_trace_setevent @TraceID, 45, 28, @on

exec sp_trace_setevent @TraceID, 45, 13, @on

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 3, @on

exec sp_trace_setevent @TraceID, 12, 11, @on

exec sp_trace_setevent @TraceID, 12, 35, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

exec sp_trace_setevent @TraceID, 41, 1, @on

exec sp_trace_setevent @TraceID, 41, 3, @on

exec sp_trace_setevent @TraceID, 41, 11, @on

exec sp_trace_setevent @TraceID, 41, 35, @on

exec sp_trace_setevent @TraceID, 41, 12, @on

exec sp_trace_setevent @TraceID, 41, 13, @on

 

-- Set the Filters

DECLARE @intfilter AS INT;

DECLARE @bigintfilter AS BIGINT;

-- Application name filter

EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%';

-- Database ID filter

EXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;

 

-- Set the trace status to start

EXEC sp_trace_setstatus @traceid, 1;

 

-- Print trace id and file name for future references

PRINT 'Trce ID: ' + CAST(@traceid AS VARCHAR(10))

  + ', Trace File: ''' + @tracefile + '''';

 

GOTO finish;

 

error:

PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));

 

finish:

GO

 

 

-----------------------------------------------

--功能：清理索引和统计信息

--时间：年月日

-----------------------------------------------

 

 

USE [MASTER]

GO

/****** 对象:  StoredProcedure [dbo].[spCleanIdx]    脚本日期: 10/26/2008 20:06:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[spCleanIdx]

    @tabname nvarchar(150) -- 需要删除统计或索引的表

AS

 

/*

建立新的存储过程

*/

 

DECLARE @idx_name        nvarchar(150) -- 存放要删除的索引或统计的名称

DECLARE @drop_idx_string nvarchar(200) -- 存放动态组织而成的DROPS  index/stats 语法

 

--SET NOCOUNT ON

 

--  查看用户所指定的表是否存在

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

           WHERE table_type = 'base table' AND table_name = @tabname)

    BEGIN

       RAISERROR(N'表：''%s'' 并不存在',16, 1, @tabname)

       RETURN (1)

    END

 

SET @tabname = OBJECT_ID(@tabname)

IF EXISTS (SELECT id FROM sysindexes

       WHERE id=@tabname AND indid BETWEEN 1 AND 254

                AND status IN (96,10485856,8388704))

BEGIN

   DECLARE idx_cursor CURSOR

      FOR SELECT name FROM sysindexes

      WHERE id=@tabname AND indid BETWEEN 1 AND 254

               AND status IN (96,10485856,8388704)

   OPEN idx_cursor

   FETCH NEXT FROM idx_cursor INTO @idx_name

     WHILE @@FETCH_STATUS = 0

    BEGIN

       SET @drop_idx_string = ('DROP STATISTICS '+OBJECT_NAME(@tabname)+'.'+@idx_name)

       EXECUTE(@drop_idx_string)

       FETCH NEXT FROM idx_cursor INTO @idx_name

    END

   CLOSE idx_cursor

   DEALLOCATE idx_cursor

END

PRINT N'     *** 统计删除完毕***'

 

IF EXISTS (SELECT id FROM sysindexes

       WHERE id=@tabname AND indid BETWEEN 1 AND 254

                AND status NOT IN (96,10485856,8388704))

BEGIN

   DECLARE idx_cursor CURSOR

      FOR SELECT name FROM sysindexes

      WHERE id=@tabname AND indid BETWEEN 1 AND 254

               AND status NOT IN (96,10485856,8388704)

   OPEN idx_cursor

   FETCH NEXT FROM idx_cursor INTO @idx_name

     WHILE @@FETCH_STATUS = 0

    BEGIN

       --确定要删除的索引不是当做Constraint

       IF OBJECTPROPERTY (OBJECT_ID(@idx_name),'IsConstraint') IS NULL

       BEGIN

          SET @drop_idx_string = ('DROP INDEX '+OBJECT_NAME(@tabname)+'.'+@idx_name)

          EXECUTE(@drop_idx_string)

       END

       FETCH NEXT FROM idx_cursor INTO @idx_name

    END

   CLOSE idx_cursor

   DEALLOCATE idx_cursor

END

PRINT N'     *** 索引删除完毕***'