SQLServer常用技巧一

/*===============================================================================================================================

功 能：去除字符串中如下字符:
char(9) :tab
char(10) ：换行
char(13)：回车
char(32)：空格
===============================================================================================================================*/

CREATE FUNCTION [dbo].[TrimString]
(@strSource NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN LTRIM(RTRIM(REPLACE(replace(replace(replace(replace(@strSource,' ',''),char(9),''),char(10),''),char(13),''),char(32),'')))
END

--━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅

 

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[SplitString]

(@string nvarchar(max))

RETURNS @strset table(substr nvarchar(100))

AS

begin


declare @str1 nvarchar(60),@beg_index int
DECLARE @Seperator nvarchar(2)

set @Seperator = ','
if (right(@string,1) <> @Seperator ) set @string = @string+@Seperator

while len(@string)>0
begin
set @beg_index=CHARINDEX(@Seperator,@string)
set @str1=SUBSTRING(@string,1,@beg_index-1)
set @string=SUBSTRING(@string,@beg_index+1,len(@string)-@beg_index)

insert @strset(substr) values(@str1)

if CHARINDEX(@Seperator,@string)=0
BREAK
else
CONTINUE

end

return
end
GO

--━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅

SELECT  STUFF(( SELECT  ',' + tb.Stf_Id
                                FROM    ( SELECT DISTINCT
                                                    a.Stf_Id
                                          FROM      tempdb..#tmp_TbSource a
                                          WHERE     NOT EXISTS ( SELECT
                                                              b.Stf_Id
                                                              FROM
                                                              tempdb..#tmp_TbXZ b
                                                              WHERE
                                                              b.Stf_Id = a.Stf_Id )
                                                    AND a.Stf_Id <> '123456789012'
                                                    AND a.Stf_Id <> ''
                                        ) AS tb
                              FOR
                                XML PATH('')
                              ), 1, 1, '') ,
                        '123456789012';

--┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅

--获取表信息
EXEC sp_help '表名'

--获取表上建立的触发器
EXEC sp_helptrigger '表名'

--获取触发器具体内容
EXEC sp_helptext '触发器名称'

 

ALTER TABLE $table_name$ DISABLE TRIGGER 

--┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅

 

/*b━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━*/
--While 循环
-----------------------------------------------------------------------------------------------------------------------------------
IF(SELECT OBJECT_ID('tempdb..#tmp_Ret')) IS NOT NULL
DROP TABLE tempdb..#tmp_Ret ;

CREATE TABLE #tmp_Ret(
Id INT IDENTITY(1,1),
TbName nvarchar(300),
TbRowCount INT,
TbRowCountLen INT
);

IF(SELECT OBJECT_ID('tempdb..#Temp_Lists')) IS NOT NULL
DROP TABLE tempdb..#Temp_Lists ;
-- 获取待处理的数据记录到临时表
-- 字段说明：REFID：记录行号 / DealFlg：行处理标识
SELECT REFID = IDENTITY(INT , 1, 1), DealFlg = 0, ORDERID=CAST(name AS NVARCHAR(400))
INTO #Temp_Lists
FROM sysobjects
WHERE xtype='U';

DECLARE @REFID INT
,@ORDERID NVARCHAR(300)
,@LXH_SQLStr NVARCHAR(4000)
,@LXH_Name NVARCHAR(300)
,@LXH_Count INTEGER
,@LXH_Len INTEGER;

-- 获取临时表数据的最小行号
SELECT @REFID = MIN(REFID) FROM #Temp_Lists WHERE DealFlg = 0;

-- 若最小行号不为空（有需要处理的数据）
WHILE @REFID IS NOT NULL
BEGIN
-- 获取当前处理行的信息
SELECT @ORDERID = ORDERID FROM #Temp_Lists WHERE REFID = @REFID;

/*
此处编写对当前行数据的业务逻辑处理代码
*/
SET @LXH_SQLStr = 'SELECT @Curr_Count=COUNT(*),@Curr_Len=LEN( CAST(COUNT(*) AS NVARCHAR)) FROM '+@ORDERID+' WITH(NOLOCK)'
EXEC sp_executesql @LXH_SQLStr, N'@Curr_Count INTEGER OUT,@Curr_Len INTEGER OUT',@LXH_Count OUT, @LXH_Len OUT;

IF( PATINDEX('%[0-9]%',@ORDERID)=0)
INSERT INTO #tmp_Ret(TbName, TbRowCount,TbRowCountLen )
VALUES (@ORDERID,@LXH_Count,@LXH_Len);

-- 标识当前行已处理完毕
UPDATE #Temp_Lists SET DealFlg = 1 WHERE REFID = @REFID

-- 选择下一行号
SELECT @REFID = MIN(REFID) FROM #Temp_Lists WHERE DealFlg = 0 AND REFID > @REFID
END
SELECT * FROM #tmp_Ret WHERE TbRowCountLen IN(7,8,9,10,11,12) ORDER BY TbRowCountLen DESC,TbRowCount DESC,TbName

SELECT 'Num'=COUNT(*),TbRowCountLen FROM #tmp_Ret WHERE TbRowCountLen IN(7,8,9,10,11,12)
GROUP BY TbRowCountLen
ORDER BY TbRowCountLen DESC;
/*e━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━*/

 

 

--apply的使用┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅

 -- create Employees table and insert values
IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
 empid INT NOT NULL,
 mgrid INT NULL,
 empname VARCHAR(25) NOT NULL,
 salary MONEY NOT NULL
)
GO
IF OBJECT_ID('Departments') IS NOT NULL
DROP TABLE Departments
GO
-- create Departments table and insert values
CREATE TABLE Departments
(
 deptid INT NOT NULL PRIMARY KEY,
 deptname VARCHAR(25) NOT NULL,
 deptmgrid INT
)
GO

-- fill datas
INSERT INTO employees VALUES  (1,NULL,'Nancy',00.00)
INSERT INTO employees VALUES  (2,1,'Andrew',00.00)
INSERT INTO employees VALUES  (3,1,'Janet',00.00)
INSERT INTO employees VALUES  (4,1,'Margaret',00.00)
INSERT INTO employees VALUES  (5,2,'Steven',00.00)
INSERT INTO employees VALUES  (6,2,'Michael',00.00)
INSERT INTO employees VALUES  (7,3,'Robert',00.00)
INSERT INTO employees VALUES  (8,3,'Laura',00.00)
INSERT INTO employees VALUES  (9,3,'Ann',00.00)
INSERT INTO employees VALUES  (10,4,'Ina',00.00)
INSERT INTO employees VALUES  (11,7,'David',00.00)
INSERT INTO employees VALUES  (12,7,'Ron',00.00)
INSERT INTO employees VALUES  (13,7,'Dan',00.00)
INSERT INTO employees VALUES  (14,11,'James',00.00)

INSERT INTO departments VALUES  (1,'HR',2)
INSERT INTO departments VALUES  (2,'Marketing',7)
INSERT INTO departments VALUES  (3,'Finance',8)
INSERT INTO departments VALUES  (4,'R&D',9)
INSERT INTO departments VALUES  (5,'Training',4)
INSERT INTO departments VALUES  (6,'Gardening',NULL)
GO
--SELECT * FROM departments

-- table-value function
IF OBJECT_ID('fn_getsubtree') IS NOT NULL
DROP FUNCTION  fn_getsubtree
GO
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) 
RETURNS TABLE 
AS 
RETURN(
  WITH Employees_Subtree(empid, empname, mgrid, lvl)
  AS 
  (
    -- Anchor Member (AM)
    SELECT empid, empname, mgrid, 0
    FROM employees
    WHERE empid = @empid   
    UNION ALL
    -- Recursive Member (RM)
    SELECT e.empid, e.empname, e.mgrid, es.lvl+1
    FROM employees AS e
       join employees_subtree AS es
          ON e.mgrid = es.empid
  )
    SELECT * FROM Employees_Subtree
)
GO

SELECT * FROM Departments 

-- cross apply query
SELECT *
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST



-- outer apply query
SELECT *
FROM Departments AS D
    OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST

 

--┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅

 SET XACT_ABORT ON

--执行出错，回滚TRANS
BEGIN CATCH
IF @@TRANCOUNT>0
BEGIN
ROLLBACK TRAN
END

SET @chvErrorMsg = ERROR_MESSAGE()
RAISERROR(@chvErrorMsg,16,1)

RETURN -1

END CATCH

END

--┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅

--=================================================================================================
--查看索引碎片:
--=================================================================================================

--USE dbname
SELECT schema_name(T.schema_id) AS Schema_Name,T.Name AS Table_Name,I.name AS Index_Name,
I.type AS Index_Type,D.avg_fragmentation_in_percent AS avg_fragmentation_in_percent,page_count
--into #t_index
FROM sys.dm_db_index_physical_stats(DB_id(),null, null, null, null) AS D
INNER JOIN sys.indexes AS I WITH(NOLOCK) ON D.index_id=I.index_id AND D.object_id=I.object_id
INNER JOIN sys.tables AS T WITH(NOLOCK) ON T.object_id=D.object_id
WHERE I.type>0 AND T.is_ms_shipped=0 AND D.avg_fragmentation_in_percent>=30
and page_count>=1000
order by D.avg_fragmentation_in_percent desc

--=================================================================================================
--生成重建索引代码
--=================================================================================================
--USE dbname
SELECT schema_name(T.schema_id) AS Schema_Name,T.Name AS Table_Name,I.name AS Index_Name,
I.type AS Index_Type,D.avg_fragmentation_in_percent AS avg_fragmentation_in_percent,page_count
into #t_index
FROM sys.dm_db_index_physical_stats(DB_id(),null, null, null, null) AS D
INNER JOIN sys.indexes AS I WITH(NOLOCK) ON D.index_id=I.index_id AND D.object_id=I.object_id
INNER JOIN sys.tables AS T WITH(NOLOCK) ON T.object_id=D.object_id
WHERE I.type>0 AND T.is_ms_shipped=0 AND D.avg_fragmentation_in_percent>=30
and page_count>=1000
order by D.avg_fragmentation_in_percent desc

SELECT 'ALTER INDEX ' + index_name + ' ON ' + schema_name + '.'
+ table_name + ' REBUILD WITH (ONLINE = ON)'
FROM #t_index

--ALTER INDEX ix_userid ON dbo.user_service REBUILD WITH (ONLINE = ON)

drop table #t_index

 

 

--┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅

 

 

 

 

--┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅━┅━┅━┅━┅━━┅━┅━┅━┅