------------------------------------------------------<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
--功能：十六進制轉換為文件號?
sql函數大全及舉例。
--使用：SELECT dbo.convert_page_nums(0xCF0400000100)??
------------------------------------------------------??
sqlserver調用執行存儲過程語句。
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'???? *** 索引刪除完畢***'
?
?
