--功能:十六进制转换为文件号
--使用: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 (&#64;pos <&#61; &#64;maxlength)
BEGIN
SET &#64;currchar &#61; SUBSTRING(&#64;p1,&#64;pos,1);
SET &#64;nextchar &#61; SUBSTRING(&#64;p1,&#64;pos&#43;1,1);
IF &#64;mode &#61; &#39;command&#39;
BEGIN
SET &#64;p2 &#61; LEFT(&#64;p2,&#64;p2len) &#43; &#64;currchar;
SET &#64;p2len &#61; &#64;p2len &#43; 1 ;
IF &#64;currchar IN (&#39;,&#39;,&#39;(&#39;,&#39; &#39;,&#39;&#61;&#39;,&#39;<&#39;,&#39;>&#39;,&#39;!&#39;)
AND &#64;nextchar BETWEEN &#39;0&#39; AND &#39;9&#39;
BEGIN
SET &#64;mode &#61; &#39;number&#39;;
SET &#64;p2 &#61; LEFT(&#64;p2,&#64;p2len) &#43; &#39;#&#39;;
SET &#64;p2len &#61; &#64;p2len &#43; 1;
END
IF &#64;currchar &#61; &#39;&#39;&#39;&#39;
BEGIN
SET &#64;mode &#61; &#39;literal&#39;;
SET &#64;p2 &#61; LEFT(&#64;p2,&#64;p2len) &#43; &#39;#&#39;&#39;&#39;;
SET &#64;p2len &#61; &#64;p2len &#43; 2;
END
END
ELSE IF &#64;mode &#61; &#39;number&#39; AND &#64;nextchar IN (&#39;,&#39;,&#39;)&#39;,&#39; &#39;,&#39;&#61;&#39;,&#39;<&#39;,&#39;>&#39;,&#39;!&#39;)
SET &#64;mode&#61; &#39;command&#39;;
ELSE IF &#64;mode &#61; &#39;literal&#39; AND &#64;currchar &#61; &#39;&#39;&#39;&#39;
SET &#64;mode&#61; &#39;command&#39;;
SET &#64;pos &#61; &#64;pos &#43; 1;
END
RETURN &#64;p2;
END
GO
------------------------------------------------
--功能&#xff1a;trace跟踪
--时间&#xff1a;年月日
-------------------------------------------------
SET NOCOUNT ON;
USE master;
GO
IF OBJECT_ID(&#39;dbo.sp_perfworkload_trace_start&#39;) 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
&#64;dbid AS INT,
&#64;tracefile AS NVARCHAR(254),
&#64;traceid AS INT OUTPUT
AS
-- Create a Queue
DECLARE &#64;rc AS INT;
DECLARE &#64;maxfilesize AS BIGINT;
SET &#64;maxfilesize &#61; 5;
EXEC &#64;rc &#61; sp_trace_create &#64;traceid OUTPUT, 0, &#64;tracefile, &#64;maxfilesize, NULL
IF (&#64;rc !&#61; 0) GOTO error;
-- Client side File and Table cannot be scripted
-- Set the events
DECLARE &#64;on AS BIT;
SET &#64;on &#61; 1;
exec sp_trace_setevent &#64;TraceID, 10, 1, &#64;on
exec sp_trace_setevent &#64;TraceID, 10, 3, &#64;on
exec sp_trace_setevent &#64;TraceID, 10, 11, &#64;on
exec sp_trace_setevent &#64;TraceID, 10, 35, &#64;on
exec sp_trace_setevent &#64;TraceID, 10, 12, &#64;on
exec sp_trace_setevent &#64;TraceID, 10, 13, &#64;on
exec sp_trace_setevent &#64;TraceID, 43, 1, &#64;on
exec sp_trace_setevent &#64;TraceID, 43, 3, &#64;on
exec sp_trace_setevent &#64;TraceID, 43, 11, &#64;on
exec sp_trace_setevent &#64;TraceID, 43, 35, &#64;on
exec sp_trace_setevent &#64;TraceID, 43, 12, &#64;on
exec sp_trace_setevent &#64;TraceID, 43, 28, &#64;on
exec sp_trace_setevent &#64;TraceID, 43, 13, &#64;on
exec sp_trace_setevent &#64;TraceID, 45, 1, &#64;on
exec sp_trace_setevent &#64;TraceID, 45, 3, &#64;on
exec sp_trace_setevent &#64;TraceID, 45, 11, &#64;on
exec sp_trace_setevent &#64;TraceID, 45, 35, &#64;on
exec sp_trace_setevent &#64;TraceID, 45, 12, &#64;on
exec sp_trace_setevent &#64;TraceID, 45, 28, &#64;on
exec sp_trace_setevent &#64;TraceID, 45, 13, &#64;on
exec sp_trace_setevent &#64;TraceID, 12, 1, &#64;on
exec sp_trace_setevent &#64;TraceID, 12, 3, &#64;on
exec sp_trace_setevent &#64;TraceID, 12, 11, &#64;on
exec sp_trace_setevent &#64;TraceID, 12, 35, &#64;on
exec sp_trace_setevent &#64;TraceID, 12, 12, &#64;on
exec sp_trace_setevent &#64;TraceID, 12, 13, &#64;on
exec sp_trace_setevent &#64;TraceID, 41, 1, &#64;on
exec sp_trace_setevent &#64;TraceID, 41, 3, &#64;on
exec sp_trace_setevent &#64;TraceID, 41, 11, &#64;on
exec sp_trace_setevent &#64;TraceID, 41, 35, &#64;on
exec sp_trace_setevent &#64;TraceID, 41, 12, &#64;on
exec sp_trace_setevent &#64;TraceID, 41, 13, &#64;on
-- Set the Filters
DECLARE &#64;intfilter AS INT;
DECLARE &#64;bigintfilter AS BIGINT;
-- Application name filter
EXEC sp_trace_setfilter &#64;traceid, 10, 0, 7, N&#39;SQL Server Profiler%&#39;;
-- Database ID filter
EXEC sp_trace_setfilter &#64;traceid, 3, 0, 0, &#64;dbid;
-- Set the trace status to start
EXEC sp_trace_setstatus &#64;traceid, 1;
-- Print trace id and file name for future references
PRINT &#39;Trce ID: &#39; &#43; CAST(&#64;traceid AS VARCHAR(10))
&#43; &#39;, Trace File: &#39;&#39;&#39; &#43; &#64;tracefile &#43; &#39;&#39;&#39;&#39;;
GOTO finish;
error:
PRINT &#39;Error Code: &#39; &#43; CAST(&#64;rc AS VARCHAR(10));
finish:
GO
-----------------------------------------------
--功能&#xff1a;清理索引和统计信息
--时间&#xff1a;年月日
-----------------------------------------------
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]
&#64;tabname nvarchar(150) -- 需要删除统计或索引的表
AS
/*
建立新的存储过程
*/
DECLARE &#64;idx_name nvarchar(150) -- 存放要删除的索引或统计的名称
DECLARE &#64;drop_idx_string nvarchar(200) -- 存放动态组织而成的DROPS index/stats 语法
--SET NOCOUNT ON
-- 查看用户所指定的表是否存在
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE table_type &#61; &#39;base table&#39; AND table_name &#61; &#64;tabname)
BEGIN
RAISERROR(N&#39;表&#xff1a;&#39;&#39;%s&#39;&#39; 并不存在&#39;,16, 1, &#64;tabname)
RETURN (1)
END
SET &#64;tabname &#61; OBJECT_ID(&#64;tabname)
IF EXISTS (SELECT id FROM sysindexes
WHERE id&#61;&#64;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&#61;&#64;tabname AND indid BETWEEN 1 AND 254
AND status IN (96,10485856,8388704)
OPEN idx_cursor
FETCH NEXT FROM idx_cursor INTO &#64;idx_name
WHILE &#64;&#64;FETCH_STATUS &#61; 0
BEGIN
SET &#64;drop_idx_string &#61; (&#39;DROP STATISTICS &#39;&#43;OBJECT_NAME(&#64;tabname)&#43;&#39;.&#39;&#43;&#64;idx_name)
EXECUTE(&#64;drop_idx_string)
FETCH NEXT FROM idx_cursor INTO &#64;idx_name
END
CLOSE idx_cursor
DEALLOCATE idx_cursor
END
PRINT N&#39; *** 统计删除完毕***&#39;
IF EXISTS (SELECT id FROM sysindexes
WHERE id&#61;&#64;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&#61;&#64;tabname AND indid BETWEEN 1 AND 254
AND status NOT IN (96,10485856,8388704)
OPEN idx_cursor
FETCH NEXT FROM idx_cursor INTO &#64;idx_name
WHILE &#64;&#64;FETCH_STATUS &#61; 0
BEGIN
--确定要删除的索引不是当做Constraint
IF OBJECTPROPERTY (OBJECT_ID(&#64;idx_name),&#39;IsConstraint&#39;) IS NULL
BEGIN
SET &#64;drop_idx_string &#61; (&#39;DROP INDEX &#39;&#43;OBJECT_NAME(&#64;tabname)&#43;&#39;.&#39;&#43;&#64;idx_name)
EXECUTE(&#64;drop_idx_string)
END
FETCH NEXT FROM idx_cursor INTO &#64;idx_name
END
CLOSE idx_cursor
DEALLOCATE idx_cursor
END
PRINT N&#39; *** 索引删除完毕***&#39;