SQL Server常用脚本

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

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

常用的一些系统视图

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

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\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\DATA\templog.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

 

转载于:https://www.cnblogs.com/luck001221/p/4494840.html