SQL Server常用脚本

1、 sp_MS_marksystemobject 将存储过程或者对象标记为系统对象：

EXEC sp_MS_marksystemobject 'dbo.sp_spaceuseddba'; --注意需要“dbo.”关键字　

常用的一些系统视图

sys.dm_exec_requests SQL Server 中执行的每个请求的信息

锁定对象 ，只允许一个会话调用

exec sp_getapplock @Resource = '存储过程的名字',@LockMode = 'Exclusive', @LockOwner ='session'

2、迁移登录用户脚本：

select 'create login [' + p.name + '] ' + 
case when p.type in('U','G') then 'from windows ' else '' end + 
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + 
' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + 
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 
then ', default_language = "' + p.default_language_name +'"' else '''' end
from sys.server_principals p
    left join sys.sql_logins l on p.principal_id = l.principal_id
    left join sys.credentials c on l.credential_id = c.credential_id
where p.type in('S','U','G') and p.name <> 'sa'

3、查看数据库阻塞

SELECT wt.blocking_session_id                  AS BlockingSessesionId
      ,sp.program_name                         AS ProgramName
      ,COALESCE(sp.LOGINAME, sp.nt_username)   AS HostName    
      ,ec1.client_net_address                  AS ClientIpAddress
      ,db.name                                 AS DatabaseName        
      ,wt.wait_type                            AS WaitType                    
      ,ec1.connect_time                        AS BlockingStartTime
      ,wt.WAIT_DURATION_MS/1000                AS WaitDuration
      ,ec1.session_id                          AS BlockedSessionId
      ,h1.TEXT                                 AS BlockedSQLText
      ,h2.TEXT                                 AS BlockingSQLText
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db  ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt  ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1  ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2  ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp  ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

4、查看当前数据库脚本运行情况

SELECT  creation_time  N'语句编译时间'
        ,last_execution_time  N'上次执行时间'
        ,execution_count  N'执行次数'
        ,case datediff(ss,creation_time,last_execution_time) when 0 then 0 
            else execution_count/datediff(ss,creation_time,last_execution_time) end N'每秒执行次数'
        ,total_physical_reads N'物理读取总次数'
        ,total_logical_reads/execution_count N'每次逻辑读次数'
        ,total_logical_reads  N'逻辑读取总次数'
        ,total_logical_writes 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'执行语句'
        ,db_name(st.dbid) as dbname,st.objectid
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   execution_count DESC;

 5、处理Identity列的一些方法

DBCC CHECKIDENT (xxxxxx, NORESEED) 报告当前表的标识列

DBCC CHECKIDENT (xxxxxx, RESEED, 30) 强制将标识设置成30(如果有主键约束，后续插入可能会失败)。

在标识列插入数据(字段名称要写全)

set identity_insert xxxx on 

insert into xxxx (id,a,b,c)

select id,a,b,c

from yyyyy

set identity_insert xxxx on

6、迁移tmpdb

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:DATA	empdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:DATA	emplog.ldf');
GO

7、查看目前正在运行的查询

SELECT [Spid] = session_Id
    ,ecid
    ,[Database] = DB_NAME(sp.dbid)
    ,[User] = nt_username
    ,[Status] = er.STATUS
    ,[Wait] = wait_type
    ,[Individual Query] = SUBSTRING(qt.TEXT, er.statement_start_offset / 2, (
            CASE
                WHEN er.statement_end_offset = - 1
                    THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset
            ) / 2)
    ,[Parent Query] = qt.TEXT
    ,Program = program_name
    ,Hostname
    ,loginame
    ,kpid
    ,nt_domain
    ,start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE session_Id > 50 /* Ignore system spids.*/

8、查看索引使用情况

select db_name(database_id) as N'数据库名称',
       object_name(a.object_id) as N'表名',
       b.name N'索引名称',
       user_seeks N'用户索引查找次数',
       user_scans N'用户索引扫描次数',
       user_lookups,
       last_user_seek N'最后查找时间',
       last_user_scan N'最后扫描时间'
from sys.dm_db_index_usage_stats a join 
     sys.indexes b
     on a.index_id = b.index_id
     and a.object_id = b.object_id
where database_id=db_id('gpaydb')   ---改成要查看的数据库
and object_name(a.object_id) ='xxxxxx‘
order by user_seeks,user_scans desc

 9、dos下查看一个目录下的文件

dir *.exe /a-d/b/s
就是查找当前目录下的所有exe文件

 10、查看所有用户开启的事务，和批量删除长时间事物

SELECT es.session_id, es.login_name, es.host_name, est.text 
  , cn.last_read, cn.last_write, es.program_name 
  ,es.status,last_request_start_time,client_net_address,most_recent_session_id
FROM sys.dm_exec_sessions es 
INNER JOIN sys.dm_tran_session_transactions st 
            ON es.session_id = st.session_id 
INNER JOIN sys.dm_exec_connections cn 
            ON es.session_id = cn.session_id 
CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est 
LEFT OUTER JOIN sys.dm_exec_requests er                     
            ON st.session_id = er.session_id 
                AND er.session_id IS NULL   


select session_id,transaction_id,is_user_transaction,is_local 
from sys.dm_tran_session_transactions 
where is_user_transaction=1 


declare @sessionid int
,@sqltxt varchar(max)
declare mycursor cursor for
select session_id
from sys.dm_tran_session_transactions 
where is_user_transaction=1 

open mycursor
fetch next from mycursor into @sessionid
while @@FETCH_STATUS = 0

begin 

--print @sessionid
set @sqltxt = 'kill '+convert(varchar(5),@sessionid)
print @sqltxt
exec (@sqltxt)
fetch next from mycursor into @sessionid
end 
close mycursor
deallocate mycursor

 查询数据库备份相关的信息

;WITH    CTE_BACKUP
          AS ( SELECT   a.database_name
                       ,CASE a.[type]
                          WHEN 'D' THEN 'Database'
                          WHEN 'I' THEN 'Differential Database'
                          WHEN 'L' THEN 'Log'
                          WHEN 'F' THEN 'File or filegroup'
                          WHEN 'G' THEN 'Defferential file'
                          WHEN 'P' THEN 'Partial'
                          WHEN 'Q' THEN 'Differential partial'
                          ELSE NULL
                        END AS backup_type
                       ,a.backup_start_date AS [start_date]
                       ,a.backup_finish_date AS [end_date]
                       ,CAST(DATEDIFF(SECOND, a.backup_start_date,
                                 a.backup_finish_date) / 60.0 AS DECIMAL(8,2)) AS duration_minute
                       ,CAST(a.backup_size / 1024 / 1024 AS DECIMAL(18, 2)) AS backup_size_mb
                       ,CAST(a.compressed_backup_size / 1024 / 1024 AS DECIMAL(18,
                                                              2)) AS compressed_backup_size_mb
                       ,CAST(c.file_size / 1024 / 1024 AS DECIMAL(18, 2)) AS file_size_mb
                       ,b.physical_device_name AS backup_path
                       ,a.[name] AS backup_set_name
                       ,a.backup_set_id
                       ,a.media_set_id
                       ,c.file_type
                       ,c.physical_name AS db_files_path
                       ,c.logical_name
    
                        --,'--------->>>>' split
                        --, *
               FROM     msdb.dbo.backupset AS a
                        LEFT JOIN msdb.dbo.backupmediafamily AS b ON b.media_set_id = a.media_set_id
                        LEFT JOIN msdb.dbo.backupfile AS c ON c.backup_set_id = a.backup_set_id
                --ORDER BY backup_start_date DESC
             )
    SELECT  *
    FROM    CTE_BACKUP
    WHERE   
            -- CTE_BACKUP.backup_type = 'Log'
            --and CTE_BACKUP.[start_date] >= '2020-02-12'
           -- AND CTE_BACKUP.[start_date] < '2020-02-13'
             file_type = 'D'

查看数据库数据、索引和剩余空间

create table #tmp(dbname varchar(500), dbsizeMB varchar(500), unspaceMB varchar(500), reservedKB varchar(500), DataKB varchar(500),indexKB varchar(500), unsedKB varchar(500))
insert into #tmp
exec ('
sp_msforeachdb ''
declare @dbsize bigint
declare @logsize bigint
declare @reservedpages bigint
declare @usedpages bigint
declare @pages bigint
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)), @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from [?].dbo.sysfiles

select @reservedpages = sum(a.total_pages),
    @usedpages = sum(a.used_pages),
    @pages = sum(CASE When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
            When a.type <> 1 and p.index_id < 2 Then a.used_pages
            When p.index_id < 2 Then a.data_pages
            Else 0 END)
    from [?].sys.partitions p join [?].sys.allocation_units a on p.partition_id = a.container_id
    left join [?].sys.internal_tables it on p.object_id = it.object_id
select ''''?'''' as dbname, ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))* 8192 / 1048576,15,2)) as database_sizeMB
 ,ltrim(str((case when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576 else 0 end),15,2)) as unallocated_spaceMB
 ,ltrim(str(@reservedpages * 8192 / 1024.,15,0)) as reservedKB
 ,ltrim(str(@pages * 8192 / 1024.,15,0)) as dataKB
 ,ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0)) as indexKB
 ,ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0)) as unusedKB
 ''')

 select * from #tmp
 where dbname not in ('master','tempdb','model','msdb','YWMonitor')
 drop table #tmp