SQL Server日常维护常用的一些脚本整理

--SQL Server日常维护常用的一些脚本整理。

--1.sql server开启clr权限：

exec sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
ALTER DATABASE HWMESTC SET TRUSTWORTHY ON
ALTER AUTHORIZATION ON Database::HWMESTC TO sa;
--2.查询数据库大小

Exec sp_spaceused
select name, convert(float,size) * (8192.0/1024.0)/1024. from dbo.sysfiles
--3.数据库日志压缩

SQL Server 日常维护经典应用
--选择需要使用的数据库
USE PIMS
--将数据库模式设置为SIMPLE
ALTER DATABASE PIMS SET RECOVERY SIMPLE
-- 将日志文件收缩到1M 
DBCC SHRINKFILE ('PIMS_log', 1)
-- 还原数据库
ALTER DATABASE PIMS SET RECOVERY FULL
SQL Server 日常维护经典应用
--4.查看数据库连接用户

Select * From sys.dm_exec_connections
--5.查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)

select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc
---6.查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局

SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text] 
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 
ORDER BY usecounts,p.size_in_bytes  desc
--7.看BUFFER POOL中，都缓存了哪些表(当前数据库)的数据


select OBJECT_NAME(object_id) 表名,COUNT(*) 页数,COUNT(*)*8/1024.0 Mb                              
from   sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c                              
where  a.allocation_unit_id=b.allocation_unit_id   
       and b.container_id=c.hobt_id             
       and database_id=DB_ID()                              
group by OBJECT_NAME(object_id)                           
order by 2 desc  

--8.查询SQLSERVER内存使用情况

select * from sys.dm_os_process_memory
--9.查询SqlServer总体的内存使用情况

SQL Server 日常维护经典应用
select      type,
sum(virtual_memory_reserved_kb)*0.1*10/1024/1024 as vm_Reserved_gb,--保留的内存  
sum(virtual_memory_committed_kb)*0.1*10/1024/1024 as vm_Committed_gb,--提交的内存  
sum(awe_allocated_kb)*0.1*10/1024/1024 as awe_Allocated_gb,--开启AWE后使用的内存  
sum(shared_memory_reserved_kb)*0.1*10/1024/1024 as sm_Reserved_gb,--共享的保留内存  
sum(shared_memory_committed_kb)*0.1*10/1024/1024 as sm_Committed_gb--共享的提交内存  
from    sys.dm_os_memory_clerks
group by type
order by type
SQL Server 日常维护经典应用
--10.查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量


-- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量
-- 从这些信息可以看出，系统经常要访问的都是哪些表，有多大？
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*) 
from sys.allocation_units a, 
    sys.dm_os_buffer_descriptors b, 
    sys.partitions p 
where a.allocation_unit_id=b.allocation_unit_id 
    and a.container_id=p.hobt_id 
    and b.database_id=db_id()
group by p.object_id,p.index_id 
order by buffer_pages desc 

--11.查询缓存的各类执行计划，及分别占了多少内存


-- 查询缓存的各类执行计划，及分别占了多少内存
-- 可以对比动态查询与参数化SQL（预定义语句）的缓存量
select    cacheobjtype
        , objtype
        , sum(cast(size_in_bytes as bigint))/1024 as size_in_kb
        , count(bucketid) as cache_count
from    sys.dm_exec_cached_plans
group by cacheobjtype, objtype
order by cacheobjtype, objtype

--12.查询缓存中具体的执行计划，及对应的SQL


-- 查询缓存中具体的执行计划，及对应的SQL
-- 将此结果按照数据表或SQL进行统计，可以作为基线，调整索引时考虑
-- 查询结果会很大，注意将结果集输出到表或文件中
SELECT  usecounts ,
        refcounts ,
        size_in_bytes ,
        cacheobjtype ,
        objtype ,
        TEXT
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO
SQL Server 日常维护经典应用
--13.查询sql server内存整体使用情况

--查询sql server内存整体使用情况
  SELECT object_name, cntr_value*0.1*10/1024/1024 ,cntr_value,cntr_type,t.counter_name,t.instance_name
  FROM sys.dm_os_performance_counters t
  WHERE counter_name = 'Total Server Memory (KB)';
--14.一次性清除数据库所有表的数据


CREATE PROCEDURE sp_DeleteAllData  
AS  
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'  
EXEC sp_MSForEachTable 'DELETE FROM ?'  
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'  
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'  
GO  

--15.SQL优化相关、执行时间


SELECT creation_time  N'语句编译时间'  
        ,last_execution_time  N'上次执行时间'  
        ,total_physical_reads N'物理读取总次数'  
        ,total_logical_reads/execution_count N'每次逻辑读次数'  
        ,total_logical_reads  N'逻辑读取总次数'  
        ,total_logical_writes N'逻辑写入总次数'  
        ,execution_count  N'执行次数'  
        ,total_worker_time/1000 N'所用的CPU总时间ms'  
        ,total_elapsed_time/1000  N'总花费时间ms'  
        ,(total_elapsed_time / execution_count)/1000  N'平均时间ms'  
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,  
         ((CASE statement_end_offset   
          WHEN -1 THEN DATALENGTH(st.text)  
          ELSE qs.statement_end_offset END   
            - qs.statement_start_offset)/2) + 1) N'执行语句'  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st  
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,  
         ((CASE statement_end_offset   
          WHEN -1 THEN DATALENGTH(st.text)  
          ELSE qs.statement_end_offset END   
            - qs.statement_start_offset)/2) + 1) NOT LIKE '%fetch%'  
ORDER BY  total_elapsed_time / execution_count DESC;  

--16.truncate外键表存储过程


USE PIMS
GO

CREATE PROCEDURE [dbo].[usp_Truncate_Table]
  @TableToTruncate VARCHAR(64)
AS 

BEGIN

SET NOCOUNT ON

--==变量定义
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)

DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)   
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)

 SET @Debug = 0--(0:将执行相关语句|1:不执行语句)
 SET @Recycle = 0--(0:不创建/不清除存储表|1:将创建/清理存储表)
 set @Verbose = 1--(1:每步执行均打印消息|0:不打印消息)

 SET @i = 1
    SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>]  WITH NOCHECK ADD  CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
    SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
    SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'

-- 创建外键临时表
IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
    DROP TABLE #FKs

-- 获取外键
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       OBJECT_NAME(parent_object_id) as TableName,
       clm1.name as ColumnName, 
       OBJECT_NAME(referenced_object_id) as ReferencedTableName,
       clm2.name as ReferencedColumnName
  INTO #FKs
  FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1 ON fk.parent_column_id = clm1.column_id AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id AND fk.referenced_object_id= clm2.object_id
 --WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
 WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
 ORDER BY OBJECT_NAME(parent_object_id)

-- 外键操作(删除|重建)表
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
    IF @Verbose = 1
        PRINT '1. 正在创建表(Internal_FK_Definition_Storage)...'
    CREATE TABLE [Internal_FK_Definition_Storage] 
    (
        ID int not null identity(1,1) primary key,
        FK_Name varchar(250) not null,
        FK_CreationStatement varchar(max) not null,
        FK_DestructionStatement varchar(max) not null,
        Table_TruncationStatement varchar(max) not null
    ) 
END 
ELSE
BEGIN
    IF @Recycle = 0
    BEGIN
        IF @Verbose = 1
        PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
        TRUNCATE TABLE [Internal_FK_Definition_Storage]    
    END
    ELSE
        PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
END

IF @Recycle = 0
BEGIN
    IF @Verbose = 1
        PRINT '2. 正在备份外键定义...'           
    WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
    BEGIN
        SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
        SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
        SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
        SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
        SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)

        SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
        SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
        SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName) 

        INSERT INTO [Internal_FK_Definition_Storage]
        SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
        
        SET @i = @i + 1
        
        IF @Verbose = 1
            PRINT '  > 已备份外键:[' + @ConstraintName + '] 所属表: [' + @TableName + ']'
    END   
END   
ELSE 
    PRINT '2. 正在备份外键定义...'

IF @Verbose = 1
    PRINT '3. 正在删除外键...'
BEGIN TRAN    
BEGIN TRY
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
        PRINT '  > 已删除外键:[' + @ConstraintName + ']'
END     

IF @Verbose = 1
    PRINT '4. 正在清理数据表...'
--先清除该外键所在表(由于外键所在表仍可能又被其他外键所引用，因此需要循环递归处理)(注：本处理未实现)
--请不要使用下面注释代码
/*    
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
        PRINT '  > ' + @Statement
END
*/

IF @Debug = 1 
    PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
ELSE
    EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')
IF @Verbose = 1
    PRINT '  > 已清理数据表[' + @TableToTruncate + ']'
    
IF @Verbose = 1
    PRINT '5. 正在重建外键...'
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
    PRINT '  > 已重建外键:[' + @ConstraintName + ']'
END
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK 
    PRINT '出错信息:'+ERROR_MESSAGE()
END CATCH
IF @Verbose = 1
    PRINT '6. 处理完成！'
END

--17. 查看job运行持续时间


SELECT 
     [T1].[job_id]
    ,[T1].[name] AS [job_name] 
    ,[T2].[run_status]
    ,[T2].[run_date]
    ,[T2].[run_time]
    ,[dbo].[agent_datetime]([T2].[run_date], [T2].[run_time]) AS [run_datetime]
    ,[T2].[run_duration]
    ,DATEDIFF(SECOND, '1900-01-01', DATEADD(SECOND, 31, [dbo].[agent_datetime](19000101, [run_duration]))) AS [run_duration_s]
FROM 
    [dbo].[sysjobs] AS T1
    INNER JOIN [dbo].[sysjobhistory] AS T2
        ON [T2].[job_id] = [T1].[job_id]
WHERE 
    [T1].[enabled] = 1
    AND [T2].[step_id] = 0
    AND [T2].[run_duration] >= 1
    and [T1].[name]='PIMS_CreatePaperCraftParameterAnalysisData'
ORDER BY
     [T2].[job_id] ASC
    ,[T2].[run_date] ASC
GO
SQL Server 日常维护经典应用
--18. 从所有缓存中释放所有未使用的缓存条目

DBCC FREESYSTEMCACHE('ALL');
--19. 查询、解除死锁


--查询表死锁信息
select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_locks
where resource_type = 'OBJECT'

dbcc opentran

--查看死锁的详细信息、执行的sql语句
exec sp_who2 53
--exec sp_who 53 
DBCC inputbuffer (53)

--解除死锁
kill 53

--20. 查询SQL Server根据CPU消耗列出前5个最差性能的查询


-- Worst performing CPU bound queries
SELECT TOP 5
    st.text,
    qp.query_plan,
    qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC
GO

--21. 显示如何依据I/O消耗来找出你性能最差的查询


-- Worst performing I/O bound queries
SELECT TOP 5
    st.text,
    qp.query_plan,
    qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_logical_reads DESC
GO

22. 查询服务器部分特殊信息

select SERVERPROPERTY(N'edition') as Edition     --数据版本，如企业版、开发版等
    ,SERVERPROPERTY(N'collation') as Collation   --数据库字符集
    ,SERVERPROPERTY(N'servername') as ServerName --服务名
    ,@@VERSION as Version   --数据库版本号
    ,@@LANGUAGE AS Language  --数据库使用的语言，如us_english等
--23.查询数据库中各数据表大小


-- =============================================
-- 描  述：更新查询数据库中各表的大小，结果存储到数据表中
-- =============================================

    --查询是否存在结果存储表
    IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'temp_tableSpaceInfo') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        --不存在则创建
        CREATE TABLE temp_tableSpaceInfo
        (name NVARCHAR(128), 
        rows char(11), 
        reserved VARCHAR(18),
        data VARCHAR(18),
        index_size VARCHAR(18),
        unused VARCHAR(18))
    END
    --清空数据表
    DELETE FROM temp_tableSpaceInfo

    --定义临时变量在遍历时存储表名称
    DECLARE @tablename VARCHAR(255)

    --使用游标读取数据库内所有表表名
    DECLARE table_list_cursor CURSOR FOR 
    SELECT name FROM sysobjects 
    WHERE OBJECTPROPERTY(id, N'IsTable') = 1 AND name NOT LIKE N'#%%' ORDER BY name

    --打开游标
    OPEN table_list_cursor
    --读取第一条数据
    FETCH NEXT FROM table_list_cursor INTO @tablename 

    --遍历查询到的表名
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --检查当前表是否为用户表
        IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
        BEGIN
            --当前表则读取其信息插入到表格中
            EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo EXEC sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablename
        END
        --读取下一条数据
        FETCH NEXT FROM table_list_cursor INTO @tablename 
    END

    --释放游标
    CLOSE table_list_cursor
    DEALLOCATE table_list_cursor

    SELECT *,replace(reserved,'KB','')/1024 数据表大小M FROM temp_tableSpaceInfo order by replace(reserved,'KB','')/1024 desc
    drop table temp_tableSpaceInfo

--24.压缩数据库、文件、日志

SQL Server 日常维护经典应用
DBCC ShrinkFile(‘数据库名’,  targetsize);            /* 收缩数据库文件 */
DBCC ShrinkFile(‘数据库名_log’,  targetsize);        /* 收缩日志文件 */
Targetsize：单位为兆，必须为整数，DBCC SHRINKFILE 尝试将文件收缩到指定大小。

DBCC SHRINKFILE 不会将文件收缩到小于“实际使用的空间”大小，例如“分配空间”为10M，“实际使用空间”为6M，当制定targetsize为1时，则将该文件收缩到6M，不会将文件收缩到1M。

--收缩数据库
DBCC SHRINKDATABASE(数据库名，百分比)
百分比：即“收缩后文件中的最大可用空间”，取值范围“大于等于0， 小于100%”，实际使用中设为0即可。

--25.用扩展时间抓取过去的死锁信息



--26.数据库对象信息检索

SQL Server 日常维护经典应用
--查看对象的说明信息
exec sp_help 'T_papermachine'
--显示视图、存储过程、函数、触发器的定义脚本。 
exec sp_helptext 'proc_report_getmeasuredata' 
--显示表的行数和占用空间。  
exec sp_spaceused  'T_papermachine'
--显示表或视图的前100行，选定“tablename,1000”按Ctrl+F1可显示表的前1000行。
exec sp_executesql N'IF OBJECT_ID(@tablename) IS NOT NULL EXEC(N''SELECT TOP(''+@n+N'') * FROM ''+@tablename)',N'@tablename nvarchar(100)=''t_papermachine'',@n int=100' 
--显示表中每个索引占用的空间。  
exec sp_executesql N'SELECT index_name = ind.name, ddps.used_page_count, ddps.reserved_page_count, ddps.row_count FROM sys.indexes ind INNER JOIN sys.dm_db_partition_stats ddps ON ind.object_id = ddps.object_id AND ind.index_id = ddps.index_id WHERE ind.object_id = OBJECT_ID(@tablename)',N'@tablename nvarchar(100)=''t_papermachine''' 
--显示表或视图的字段名，以逗号分隔。  
exec sp_executesql N'SELECT columns = STUFF((SELECT '', ''+name FROM sys.columns WHERE object_id = OBJECT_ID(@tablename) FOR XML PATH('''')),1,2,'''')',N'@tablename nvarchar(100)=''T_Papermachine''' 
--根据选定关键词在当前数据库中查找表、视图、存储过程、函数  
exec sp_executesql N'SELECT * FROM sys.objects WHERE type IN (''U'',''V'',''P'',''FN'') AND name LIKE ''%''+@keyword+''%'' ORDER BY type,name',N'@keyword nvarchar(50)=''machine'''  
--查询数据库中包含指定关键词的表、视图、存储过程、函数
select routine_name,routine_definition,routine_type
from information_schema.routines
where routine_definition like '%AssessmentSpeed%'
order by routine_type
--模糊查询存储过程sql中包含某个文本
SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容
FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id = obj.ID
WHERE sc.TEXT LIKE '%存储过程内容%'

--27.数据库用户、权限操作



USE [master]
GO
--待确认账号密码
CREATE LOGIN [NDIT] WITH PASSWORD=N'1', DEFAULT_DATABASE=[PIMS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE PIMS
go
CREATE USER [NDIT] FOR LOGIN [NDIT]
GO
--大权限， 如果是指定的部分表，不执行这个，如果是所有内容都可以读，用此脚本
--EXEC sp_addrolemember N'db_datareader', N'NDIT'
--GO
--指定特定表名赋予新增/更新/查询
DECLARE @Sql NVARCHAR(max)
SET @Sql=''
--table
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.tables AS a WHERE name IN ('Tab1','Tab2');
--view
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.views AS a WHERE name IN ('view1','view2');
--procedure
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.procedures AS a WHERE name IN ('proc1','proc2');

PRINT @Sql

EXEC(@Sql)
go

--禁用登陆帐户
alter login NDIT disable
--启用登陆帐户
alter login NDIT enable

--登陆帐户改名
alter login NDIT with name=dba_tom

--登陆帐户改密码： 
alter login NDIT with password='aabb@ccdd'

--数据库用户改名： 
alter user NDIT with name=dba_tom

--更改数据库用户 defult_schema： 
alter user NDIT with default_schema=sales

--删除数据库用户： 
drop user NDIT

--删除 SQL Server登陆帐户： 
drop login NDIT


--28.使用Checksum结合NewID获得随机数

SQL Server 日常维护经典应用
Create FUNCTION Scalar_CheckSumNEWID  
(  
    @From int,  
    @To int,  
    @Keep int,  
    @newid varchar(50)  
)  
RETURNS float  
BEGIN  
    DECLARE @ResultVar float  
    SELECT @ResultVar=CONVERT(BIGINT,RIGHT(ABS(CHECKSUM(@newid)),9))*0.1/100000000  
    RETURN @From+round((@To-@From)*@ResultVar,@Keep)  
END  
GO  

--29. 查询数据库表字段各项属性信息，便于直接复制导出excel表




--30. 判断是否存在数据库、表、列、视图




--31. CTE查询的存储过程执行时间明显超出T-Sql查询。 可以通过添加“WITH RECOMPILE”参数，强制存储过程每次执行时重编译，实现快速查询。

--大神的帖子： Parameter Sniffing, Embedding, and the RECOMPILE Options

--32. 解决insert exec 嵌套问题，解决办法是建立一个指向自己的数据库，增加链接服务器。


--1. 首先，增加链接服务器：

   exec sp_addlinkedserver 'srv1','','SQLOLEDB','(local)'   
   exec sp_addlinkedsrvlogin 'srv1','false',null,'sa','sa'

--2. 其次找到该链接服务器，右键属性，开启RPC：

---- 服务器对象->链接服务器->右键->属性->服务器选项->RPC、RPC Out 都设置为True

--3. 启动MSDTC服务：

 --- 服务名称为：MSDTC（显示名称为Distributed Transaction Coordinator）
 --- 如果没启动会报错如下：MSDTC on server 'servername' is unavailable 

--4. 调整存储过程访问，使用srv1调用存储过程 

 --- insert #Temp exec srv1.DBName.dbo.Proc_Test @param 

--5. 成功！结束！

--33. 查询数据库连接数、用户等


--查看连接到数据库"DB"的连接
SELECT * from master.dbo.sysprocesses WHERE dbid = DB_ID('DB')
--查询某个数据库用户的连接情况
sp_who 'sa'
--查看数据库允许的最大连接
select @@MAX_CONNECTIONS
--查看数据库自上次启动以来的连接次数
SELECT @@CONNECTIONS
--关闭连接，上面的查询可以得到spid，根据spid，关闭进程就可以了。
kill 54

--34. 数据库缓存清理


CREATE PROCEDURE [dbo].ClearMemory   
AS   
BEGIN 
    --清除所有缓存 
    DBCC DROPCLEANBUFFERS 
    --打开高级配置 
    exec sp_configure 'show advanced options', 1 
    --设置最大内存值，清除现有缓存空间 
    exec sp_configure 'max server memory', 25600 
    EXEC ('RECONFIGURE') 
    --设置等待时间 
    WAITFOR DELAY '00:00:01' 
    --重新设置最大内存值 
    EXEC  sp_configure 'max server memory',40960 
    EXEC ('RECONFIGURE') 
    --关闭高级配置 
    exec sp_configure 'show advanced options',0 
END
GO 

--35. sqlcmd命令修改memory


net start MSSQLServer /mSQLCMD /f

SQLCMD

EXEC sp_configure 'show advanced option', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max server memory', '40960'
GO
RECONFIGURE WITH OVERRIDE
GO
go

--36. 数据库还原中。。。

RESTORE DATABASE 数据库名 WITH RECOVERY
--37.SQLServer查询所有子节点


--用CTE递归
;with f as 
(
select * from tab where id=1
union all
select a.* from tab as a inner join f as b on a.pid=b.id
)
select * from f
SQL Server 日常维护经典应用
38.同数据集通过偏移量进行行关联计算

--LAG(访问相同结果集的先前行中的数据)、Lead(访问相同结果集的后续行中的数据)

--https://docs.microsoft.com/zh-cn/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15

--39.列拼接字符串

select stuff((
select ';'+name
from Student for xml path('')),1,1,'') as name
--40. 更新函数

EXECUTE sp_refreshsqlmodule N'[dbo].[Proc_Chart]';
--41.行转列


Declare @tanks nvarchar(200);
Declare @sql nvarchar(4000)
set  @tanks=(SELECT STUFF((SELECT ','+tl.UnitCode FROM dbo.V_CP_Tank tl   FOR XML PATH('')),1,1,''))
SELECT @tanks

SET @sql='
    SELECT DataDate,'+@tanks+'
    FROM CP_TankMass
    PIVOT(sum(RealQuantity) FOR [UnitCode] IN('+@tanks+')) AS T 
    '
Exec(@sql)

--42.延时

--SQL有定时执行的语句WaitFor。

--语法格式：waitfor {delay 'time'|time 'time'}
--delay后面的时间是需要延迟多长时间后执行。
--time后面的时间是指定何时执行，格式为'HH:MM:SS',不支持日期