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 '''' endfrom 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_idwhere 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 BlockingSQLTextFROM sys.dm_tran_locks AS tlINNER JOIN sys.databases db ON db.database_id = tl.resource_database_idINNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_addressINNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_idINNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_idLEFT OUTER JOIN master.dbo.sysprocesses sp ON SP.spid = wt.blocking_session_idCROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1CROSS 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.objectidFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stwhere 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;GOALTER DATABASE tempdbMODIFY FILE (NAME = tempdev, FILENAME = 'E:\DATA\tempdb.mdf');GOALTER DATABASE tempdbMODIFY 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_timeFROM sys.dm_exec_requests erINNER JOIN sys.sysprocesses sp ON er.session_id = sp.spidCROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qtWHERE 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_idwhere 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_idFROM 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 forselect session_idfrom sys.dm_tran_session_transactions where is_user_transaction=1 open mycursorfetch next from mycursor into @sessionidwhile @@FETCH_STATUS = 0begin --print @sessionidset @sqltxt = 'kill '+convert(varchar(5),@sessionid)print @sqltxtexec (@sqltxt)fetch next from mycursor into @sessionidend close mycursordeallocate mycursor