1、检索关键字：sql server内置存储过程,sql server查看系统信息

2、查看磁盘空间：EXEC master.dbo.xp_fixeddrives ,

--查看各个数据库所在磁盘情况

SELECT DB_NAME(df.database_id) as dbName,physical_name AS DataFile,size*8/1024 AS 'FileSize(MB)',volume_mount_point AS Drive,CAST(total_bytes/1024/1024/1024 AS VARCHAR) + ' GB' AS DriveSize,CAST(available_bytes/1024/1024/ 1024 AS VARCHAR) + ' GB' AS SpaceAvailableFROM sys.master_files dfCROSS APPLY sys.dm_os_volume_stats(df.database_id, df.file_id) ovswhere DB_NAME(df.database_id)='db_tank'

3、修改表名/列名：(1)表：exec sp_rename 'test101','test100' （2）列：exec sp_rename 'test101.name','name1'

4、查看视图/过程/对象内容：exec sp_helptext v_test101（带有格式的）

5、创建文件目录：exec MASTER.dbo.xp_create_subdir 'c:\MSSQL\Data'

6、查看错误日志：exec xp_readerrorlog ，循环错误日志：sp_cycle_errorlog

详细参考：/wacthamu/article/details/24436629，/v1t1p9hvbd/article/details/71524155

查看错误信息代码select *from db_tank.sys.messageswhere message_id= 15281

查看错误日志物理路径：SELECT SERVERPROPERTY('ErrorLogFileName')

查看错误日志文件大小：exec sys.xp_enumerrorlogs

7、更新统计信息：exec sp_updatestats（所有） ，UPDATE STATISTICS Person.Address WITH FULLSCAN（单表）

8、查看系统进程：

系统进程：select * from sys.sysprocesses

用户进程：

select status,start_time,command,percent_complete,wait_type,text,session_id,blocking_session_idfrom sys.dm_exec_requests rcross apply sys.dm_exec_sql_text(r.sql_handle) s--详细版SELECT TOP 10[cpu_time],[session_id],[request_id],[start_time] AS '开始时间',[status] AS '状态',[command] AS '命令',dest.[text] AS 'sql语句', DB_NAME([database_id]) AS '数据库名',[blocking_session_id] AS '正在阻塞其他会话的会话ID',[wait_type] AS '等待资源类型',[wait_time] AS '等待时间',[wait_resource] AS '等待的资源',[reads] AS '物理读次数',[writes] AS '写次数',[logical_reads] AS '逻辑读次数',[row_count] AS '返回结果行数'FROM sys.[dm_exec_requests] AS der CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest ORDER BY [cpu_time] DESC

更具体分析

SELECT spid,start_time,[Database] = DB_NAME(sp.dbid) ,command,[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 = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))* 2ELSE er.statement_end_offsetEND - er.statement_start_offset )/ 2) ,[Parent Query] = qt.text ,Program = program_name ,hostname ,nt_domain ,[Spid] = session_id ,blocking_session_idFROM 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. AND session_id NOT IN ( @@SPID ) -- Ignore this current statement. ORDER BY 1

--查看所有SQL正在执行的进度详情

SELECT

r.session_id ,

DB_NAME(qt.[dbid]) AS [DatabaseName] ,

r.start_time,

r.[status],

r.blocking_session_id,

SUBSTRING(qt.[text], r.statement_start_offset / 2,

( CASE WHEN r.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2

ELSE r.statement_end_offset

END - r.statement_start_offset ) / 2) AS [statement] ,

r.wait_type,

r.wait_time,

r.wait_resource,

r.cpu_time ,

r.total_elapsed_time / 60000 AS[elapsed_minutes],

r.reads ,

r.writes ,

r.logical_reads,

s.host_name,s.program_name

FROM sys.dm_exec_requests AS r

join sys.dm_exec_sessions s on r.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt

WHERE r.session_id > 50

ORDER BY 1

---SQL Server查询正在执行的SQL语句及执行计划

select ds.session_id,dr.start_time,db_name(dr.database_id),dr.blocking_session_id,ds.host_name,

ds.program_name,ds.host_process_id,ds.login_name,dr.status,

mand,dr.wait_type,dr.wait_time,dr.open_transaction_count,

dr.percent_complete,dr.estimated_completion_time,dr.row_count,

SUBSTRING(st.text, (dr.statement_start_offset/2)+1,

((CASE dr.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE dr.statement_end_offset

END - dr.statement_start_offset)/2) + 1) AS statement_text,

st.text as full_text,

qp.query_plan

from sys.dm_exec_sessions ds,sys.dm_exec_requests dr--,sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(dr.sql_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(dr.plan_handle) as qp

where ds.session_id>50

and dr.session_id<>@@spid

and ds.session_id=dr.session_id

--and dr.sql_handle=qs.sql_handle

and dr.database_id>4

-- R2以下版本 想要看图形界面，直接复制内容，重命名为.sqlplan

--查看阻塞与被阻塞语句

SELECT R.session_id AS BlockedSessionID ,

S.session_id AS BlockingSessionID ,

Q1.text AS BlockedSession_TSQL ,

Q2.text AS BlockingSession_TSQL ,

C1.most_recent_sql_handle AS BlockedSession_SQLHandle ,

C2.most_recent_sql_handle AS BlockingSession_SQLHandle ,

S.original_login_name AS BlockingSession_LoginName ,

S.program_name AS BlockingSession_ApplicationName ,

S.host_name AS BlockingSession_HostName

FROM sys.dm_exec_requests AS R

INNER JOIN sys.dm_exec_sessions AS S ON R.blocking_session_id = S.session_id

INNER JOIN sys.dm_exec_connections AS C1 ON R.session_id = C1.most_recent_session_id

INNER JOIN sys.dm_exec_connections AS C2 ON S.session_id = C2.most_recent_session_id

CROSS APPLY sys.dm_exec_sql_text(C1.most_recent_sql_handle) AS Q1

CROSS APPLY sys.dm_exec_sql_text(C2.most_recent_sql_handle) AS Q2

9、查询数据库的数据文件及日志文件的相关信息（包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等）

select * from [数据库名].[dbo].[sysfiles]，sys.master_files

--查看数据库初始大小，现在大小，初始与现在的大小差

USE db_tank;WITH cteAS (SELECT DB_NAME(database_id) AS name,mf.name AS db_filename,mf.physical_name,CAST((mf.size / 128.0) AS DECIMAL(20, 2)) AS initial_size_MB,CAST((df.size / 128.0) AS DECIMAL(20, 2)) AS actual_size_MB,CASE mf.is_percent_growthWHEN 0 THEN STR(CAST((mf.growth / 128.0) AS DECIMAL(10, 2))) + ' MB'WHEN 1 THEN STR(mf.growth) + '%'END AS auto_grow_settingFROMsys.master_files mfJOIN sys.database_files df ON mf.name = df.nameWHEREmf.database_id = DB_ID())SELECT *,actual_size_MB - initial_size_MB AS change_in_MB_since_restartFROM cte;

---------------------

select size/128.0/1024 as size_GB,* from db_tank.[dbo].[sysfiles]

10、转换文件大小单位为MB：（sql server默认单位是kb） *8位KB /1024位MB

select name, convert(float,size) * (8192.0/1024.0)/1024. from [数据库名].dbo.sysfiles

11、查询当前数据库的磁盘使用情况：Exec sp_spaceused

12、查询数据库服务器各数据库日志文件的大小及利用率 ：DBCC SQLPERF(LOGSPACE)

*****查看用户级进程：

select status,start_time,command,percent_complete,wait_type,text,

session_id,blocking_session_id

from sys.dm_exec_requests r

cross apply sys.dm_exec_sql_text(r.sql_handle) s

*****查看数据库状态：select * from SYS.DATABASES

查看表结构 ：sp_help table_name 需要把状态切到对应的数据库，不能用数据库.schema.表名的方式

判断查看是否存在：

【1】表IF OBJECT_ID('db_tank..TS_UnrealContestRankReward') IS NULLBEGINCREATE TABLEEND【2】存储过程IF OBJECT_ID('db_tank..TS_UnrealContestRankReward') IS NOT NULLDROP PROCEDURE UnrealContestRankRewardGOCREATE PROCEDURE ……END【3】--列IF COL_LENGTH(N'BattleTeam_ActiveRecordInfo',N'ID') IS NULLBEGINEND【4】主键IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE parent_obj=OBJECT_ID('db_tank..ServerActiveState') AND xtype='PK')BEGINEND【5】默认约束IF COL_LENGTH(N'[dbo].[Pet_TemplateInfo]', N'LowDamage') IS NOT NULLbegindeclare @DF1 varchar(100)SELECT @DF1=name FROM sys.default_constraintsWHERE parent_object_id = OBJECT_ID('Pet_TemplateInfo')AND parent_column_id = COLUMNPROPERTY(OBJECT_ID('Pet_TemplateInfo'), 'LowDamage', 'ColumnId')exec ('alter table Pet_TemplateInfo drop CONSTRAINT '+@DF1)ALTER TABLE [dbo].Pet_TemplateInfo DROP COLUMN LowDamageendalter table consortia drop constraint [DF_Consortia_fightPower]alter table consortia alter column fightPower bigint not null;alter table consortia add constraint DF_Consortia_fightPower default(0) for fightPower;刷新视图SELECT DISTINCT

'EXEC sp_refreshview ''' + name + ''''FROM db_tank.sys.objects AS soWHERE so.type = 'V'【6】默认约束alter table Consortia alter column fightpower bigint not nullALTER TABLE [dbo].Consortia ADD DEFAULT ((0)) FOR fightpowergo

【7】自增列 identity

select * from db_tank.sys.columns where object_id=object_id('sys_users_detail') AND is_identity=1

SQL性能查询分析

13.查看执行时间和cpu占用时间

Product为表名

set statistics time onselect * from dbo.Productset statistics time off

14.查看查询对I/0的操作情况

set statistics io onselect * from dbo.Productset statistics io off

如果物理读取次数和预读次说比较多，可以使用索引进行优化。

SSMS中设置：查询--->>查询选项--->>高级

被红圈套上的2个选上，去掉sql语句中的set statistics io/time on/off 试试效果。

15、查看日志文件.ldf的详情：use db_name ;DBCC LOGINFO;

16、查看数据库文件与文件组信息

--查看数据库文件与文件组信息

SELECTname as [database_name],COUNT (*) AS [DataFiles],COUNT (DISTINCT data_space_id) AS [Filegroups],SUM (size)*8/1024 AS [Size(MB)] --default KbFROM sys.master_filesWHERE [type_desc] = N'ROWS' -- filter out log files/data_space_id 0AND [database_id] > 0 -- filter out system databasesAND [FILE_ID] != 65537 -- filter out FILESTREAMGROUP BY [database_id],name;GO

17、查看数据库大小

select name, filename, convert(float, size) * (8192/1024)/1024/1024 as dbsize_G from db_tank.dbo.sysfiles union allselect name, filename, convert(float, size) * (8192/1024)/1024/1024 as dbsize_G from db_logs.dbo.sysfiles

18、查看日志记录详细信息：select * from Fn_dblog(null,null)

19、Sql Server 查看存储过程在哪些作业中被调用

过程被作业调用

SELECT *

FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)

INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )

ON STP .job_id = JOB .job_id

WHERE STP .command LIKE N’% sp_name %’

20、sql 查询某个表在哪些存储过程（SP）中使用

（1）.查询某个表被哪些存储过程（以下简称 SP）使用:

表被存储过程调用

SELECTobj.Name存储过程名,sc.TEXT存储过程内容

FROMsyscommentssc

INNERJOINsysobjectsobjONsc.Id=obj.ID

WHEREsc.TEXTLIKE'%Sys_Users_Detail.IsVIP%'

--sp_depends tab

表依赖关系，表之间关系 （参考：/gered/p/10812374.html）

（2）.查找那些过程对该表做了更新操作：

select distinct object_name(id) fromsyscomments where id in

(select object_id from sys.objects where type='P') and text like'%update tablename%'

（3）.查询当前数据库中所有SP：

select name as 存储过程fromsysobjects where type = 'P

21.查看锁情况 ：select * from sys.dm_tran_locks 注意：db_name(db_id)可以获取数据库名字/数据库名称

22.查看表是否分区及分区信息

已分区的表：

[sql]view plaincopy SELECTDISTINCTt.nameASTableName,ps.nameASPSName,fg.nameASFileGroupName,f.nameAS[FileName],f.physical_nameAS[FilePhysicalName]--,dds.destination_idASPartitionNumber--去除注释即可显示文件的分区数FROMsys.tablesAStINNERJOINsys.indexesASiON(t.object_id=i.object_id)INNERJOINsys.partition_schemesASpsON(i.data_space_id=ps.data_space_id)INNERJOINsys.destination_data_spacesASddsON(ps.data_space_id=dds.partition_scheme_id)INNERJOINsys.filegroupsASfgONdds.data_space_id=fg.data_space_idINNERJOINsys.database_filesfONf.data_space_id=fg.data_space_idWHEREt.name='tableName'

未分区的表：

[sql]view plaincopy SELECTt.[name],i.[name],i.[index_id],f.[name]FROMsys.indexesiINNERJOINsys.filegroupsfONi.data_space_id=f.data_space_idINNERJOINsys.tablesAStONi.[object_id]=t.[object_id]WHEREt.name='tablename'

23、查看慢查询语句

SELECT TOP 10 TEXT AS 'SQL Statement'

,last_execution_time AS 'Last Execution Time'

,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]

,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]

,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]

,execution_count AS "Execution Count"

,qp.query_plan AS "Query Plan"

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_elapsed_time / execution_count DESC

24、查看错误日志 EXEC [sys].[xp_readerrorlog] 清空错误日志：EXEC [sys].[sp_cycle_errorlog] 25、索引碎片及表的索引名称、索引对应的列

--查看索引碎片

select db_name(database_id) as '数据库名',object_name(t.object_id) as '表名',t.index_id as '索引id',t1.index_name as '索引名称',t1.type_desc as '索引类型',t1.column_name as '索引列名',t.partition_number as '当前索引所在分区',t.page_count as '页统计',t.avg_page_space_used_in_percent as '页使用率' ,t.record_count as '页行记录数',t.avg_record_size_in_bytes as '平均每条记录大小(B)',t.avg_fragmentation_in_percent as '索引碎片比率',t.fragment_count as '索引中的碎片数量',t.avg_fragment_size_in_pages as '一个索引中碎片的平均页数'from sys.dm_db_index_physical_stats(db_id('db_tank'),NULL,NULL,NULL,NULL) tjoin (select distinct t3.object_id,t3.index_id,object_name(t3.object_id) as 'table_name',t3.name as 'index_name',t3.type_desc, t1.name as 'column_name'from sys.columns t1 join sys.index_columns t2 on t1.column_id = t2.column_id AND t1.object_id = t2.object_idjoin sys.indexes t3 on t2.index_id = t3.index_id AND t2.object_id = t3.object_id) t1on t1.object_id = t.object_id AND t1.index_id = t.index_idwhere object_name(t.object_id) = 'sys_users_goods'--查看所有表中对应的索引名与索引列select t3.object_id,object_name(t3.object_id) as 'table_name',t3.name as 'index_name',t3.type_desc, t1.name as 'column_name'from sys.columns t1 join sys.index_columns t2 on t1.column_id = t2.column_id AND t1.object_id = t2.object_idjoin sys.indexes t3 on t2.index_id = t3.index_id AND t2.object_id = t3.object_idwhere object_name(t3.object_id) = 'sys_users_goods'

--查看表中所有索引

SELECT name,type_desc FROM sys.indexes WHERE object_id=object_id('O_Orders')

--根据索引名称查看对应的列

DBCC SHOW_STATISTICS(O_Orders,idx_order_status_2)DBCC SHOW_STATISTICS(O_Orders,IX_O_OrdersUID)

--查找碎片率大于40%的

SELECT object_name(object_id) ,index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,record_count,avg_page_space_used_in_percentFROM sys.dm_db_index_physical_stats(DB_ID('DBNAME'), OBJECT_ID(''),NULL,NULL,'Sampled') WHERE avg_fragmentation_in_percent>40

26、数据库列表、存储过程列表、存储过程内容

--数据库列表

SELECT name FROM master.dbo.sysdatabases ORDER BY name其次，这是一种让所有的用户从数据库中创建存储过程。-- 获取存储过程列表 -- Type = 'P' --> 存储过程 -- Category = 0 --> 用户创建的 SELECT * FROM sysobjects WHERE type = ' P ' AND category = 0 ORDER BY name然后我们就可以检索查询与存储过程内容如下：-- 获取存储过程内容 -- Name = Stored Procedure Name. SELECT text FROM syscomments WHERE id = ( SELECT id FROM sysobjects WHERE name = ' 存储过程名称 ')

27.获取表的所有列名

selectstuff((select','+name from syscolumns where id=object_id('yf_check_zy') for xml path('')),1,1,'') as cloumnname

28.查看表数据行数

SELECT a.name, b.rowsFROMsysobjects AS a INNER JOINsysindexes AS b ON a.id = b.idWHERE (a.type = 'u') AND (b.indid IN (0, 1))ORDER BY b.rows DESC

29.查看索引大小

--查看索引大小如果您想要表的每个索引的大小，请使用以下两个查询中的一个：SELECTi.name AS IndexName,SUM(s.used_page_count) * 8 AS IndexSizeKBFROM sys.dm_db_partition_stats AS s JOIN sys.indexesAS iON s.[object_id] = i.[object_id] AND s.index_id = i.index_idWHERE s.[object_id] = object_id('dbo.TableName')GROUP BY i.nameORDER BY i.name

---第2种方法SELECTi.name AS IndexName,SUM(page_count * 8) AS IndexSizeKBFROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS sJOIN sys.indexes AS iON s.[object_id] = i.[object_id] AND s.index_id = i.index_idGROUP BY i.nameORDER BY i.name结果通常略有不同，但在1%之内。

30.系统存储过程列出SQL Server中的数据库和可以通过网关访问的数据库。EXEC sp_databases返回当前库中可查询对象的列表EXEC sp_tables返回数据库对象、用户定义数据类型。EXEC sp_help列出指定表的主键EXEC sp_pkeys SC列出指定表的外键EXEC sp_fkeys C返回指定表的列信息EXEC sp_columns C返回指定的表或视图上的所有索引表。EXEC sp_statistics SC返回当前环境中的存储过程列表。EXEC sp_stored_procedures扩展过程（只能在master中执行）报告帐户、帐户类型、帐户的特权级别等信息EXEC xp_logininfo执行操作系统命令，并以文本行方式返回任何输出。EXEC xp_cmdshell 'dir *.exe'

31.查询数据文件与日志文件占用情况

1. 查看数据文件占用（权限要求较大）

DBCC showfilestats

2. 查看日志文件占用

dbcc sqlperf(logspace)

SELECT a.name [文件名称] ,cast(a.[size]*1.0/128 as decimal(12,1)) AS [文件设置大小(MB)] ,CAST( fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [文件所占空间(MB)] ,CAST( (fileproperty(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0 AS DECIMAL(12,1)) AS [所占空间率%] ,CASE WHEN A.growth =0 THEN '文件大小固定，不会增长' ELSE '文件将自动增长' end [增长模式] ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN '增量为固定大小' WHEN A.growth > 0 AND is_percent_growth = 1 THEN '增量将用整数百分比表示' ELSE '文件大小固定，不会增长' END AS [增量模式] ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128as decimal(12,0)) AS VARCHAR)+'MB' WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+'%' ELSE '文件大小固定，不会增长' end AS [增长值(%或MB)] ,a.physical_name AS [文件所在目录] ,a.type_desc AS [文件类型] FROM sys.database_files a INNER JOIN sys.sysfiles AS s ON a.[file_id]=s.fileid LEFT JOIN sys.dm_db_file_space_usage b ON a.[file_id]=b.[file_id] ORDER BY a.[type]

32.查看数据库大小，查看库的mdf与ndf大小

USE mastergoSELECT Name,physical_name,Size/128.0 AS [Size(MB)],FILEPROPERTY(Name,'SpaceUsed')/128.0 AS [SpaceUsed(MB)],STR(FILEPROPERTY(Name,'SpaceUsed')*1.0/Size*100,6,3) AS [SpaceUsed(%)] FROM master.sys.database_files

33.查看所有表、字段、索引详情（数据字典）

--查看表的详细信息SELECT 表名 = CASE WHEN C.column_id = 1 THEN O.nameELSE N''END ,表说明 = ISNULL(CASE WHEN C.column_id = 1 THEN PTB.[value]END, N'') ,字段序号 = C.column_id ,字段名 = C.name ,主键 = ISNULL(IDX.PrimaryKey, N'') ,标识 = CASE WHEN C.is_identity = 1 THEN N'√'ELSE N''END ,计算列 = CASE WHEN C.is_computed = 1 THEN N'√'ELSE N''END ,类型 = T.name ,长度 = C.max_length ,精度 = C.precision ,小数位数 = C.scale ,允许空 = CASE WHEN C.is_nullable = 1 THEN N'√'ELSE N''END ,默认值 = ISNULL(D.definition, N'') ,字段说明 = ISNULL(PFD.[value], N'') ,索引名 = ISNULL(IDX.IndexName, N'') ,索引排序 = ISNULL(IDX.Sort, N'') ,创建时间 = O.Create_Date ,修改时间 = O.Modify_dateFROM sys.columns CINNER JOIN sys.objects O ON C.[object_id] = O.[object_id]AND O.type = 'U'AND O.is_ms_shipped = 0INNER JOIN sys.types T ON C.user_type_id = T.user_type_idLEFT JOIN sys.default_constraints D ON C.[object_id] = D.parent_object_idAND C.column_id = D.parent_column_idAND C.default_object_id = D.[object_id]LEFT JOIN sys.extended_properties PFD ON PFD.class = 1AND C.[object_id] = PFD.major_idAND C.column_id = PFD.minor_id -- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) LEFT JOIN sys.extended_properties PTB ON PTB.class = 1AND PTB.minor_id = 0AND C.[object_id] = PTB.major_id -- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) LEFT JOIN -- 索引及主键信息 ( SELECT IDXC.[object_id] ,IDXC.column_id ,Sort = CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')WHEN 1 THEN 'DESC'WHEN 0 THEN 'ASC'ELSE ''END ,PrimaryKey = CASE WHEN IDX.is_primary_key = 1 THEN N'√'ELSE N''END ,IndexName = IDX.NameFROMsys.indexes IDXINNER JOIN sys.index_columns IDXC ON IDX.[object_id] = IDXC.[object_id]AND IDX.index_id = IDXC.index_idLEFT JOIN sys.key_constraints KC ON IDX.[object_id] = KC.[parent_object_id]AND IDX.index_id = KC.unique_index_idINNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息 ( SELECT [object_id] ,Column_id ,index_id = MIN(index_id)FROMsys.index_columnsGROUP BY [object_id] ,Column_id) IDXCUQ ON IDXC.[object_id] = IDXCUQ.[object_id]AND IDXC.Column_id = IDXCUQ.Column_idAND IDXC.index_id = IDXCUQ.index_id) IDX ON C.[object_id] = IDX.[object_id]AND C.column_id = IDX.column_id -- WHERE O.name like '%name%'-- 如果只查询指定表,加上此条件 ORDER BY O.name ,C.column_id

View Code

34.查询某个内容在哪些表中出现过

DECLARE @string VARCHAR(100)='8045013775' --要查询的数据，内容自行修改DECLARE @sql NVARCHAR(MAX)SET @sql=N'DECLARE @sql NVARCHAR(MAX),@ColNames NVARCHAR(MAX),@ColValues NVARCHAR(MAX);SET @ColNames=NULL;SET @ColValues=NULL;SELECT @ColNames=ISNULL(@ColNames+'','','''')+QUOTENAME(c.[Name]),@ColValues=ISNULL(@ColValues, ''''''''+OBJECT_NAME(c.object_id)+''''''''+'' AS [TableName]'')+'',''+ ''RTRIM(CONVERT(NVARCHAR(max),''+QUOTENAME(c.[Name])+'')) AS ''+QUOTENAME(c.[Name])FROM sys.[columns] AS c where c.object_id=object_id(N''?'');set @sql= ''SELECT * FROM (SELECT ''+ @ColValues+'' FROM ''+''?''+'' ) as t UNPIVOT(ColumnValue FOR ColumName IN (''+@ColNames+'')) u WHERE CHARINDEX('''''+@string+N''''',ColumnValue)>0'';print @sql;EXEC(@sql)'PRINT @sqlEXEC sp_MsforeachTable @command1=@sql

35.性能计数器查看

select * from sys.dm_os_performance_counters

36.always on

sys.dm_hadr_database_replica_states

sys.dm_hadr_availability_replica_cluster_nodes

sys.dm_hadr_availability_replica_cluster_states

1.检查数据库复制路由

查看 sys.dm_hadr_database_replica_states 视图，检查数据库复制路由。其中 replica_id 是指可用性副本的 GUID，group_database_id 是指可用性数据库的 GUID，如果是本地节点则 is_local=1，如果是主节点则 is_primary_replica=1。

2.查看复制状态

对于同步提交模式的节点，在 sys.dm_hadr_database_replica_states 视图中，is_commit_paticipant=1，synchronization_state=2, synchronization_state_desc=SYNCHRONIZED。

对于异步提交模式的节点，is_commit_paticipant=0，synchronization_state=1, synchronization_state_desc=SYNCHRONIZING。

3.查看集群节点与复制

SELECT CND.*, RST.is_local, RST.role_desc, RST.operational_state_desc, RST.connected_state_desc, RST.synchronization_health_desc FROM sys.dm_hadr_availability_replica_cluster_nodes CNDJOIN sys.dm_hadr_availability_replica_cluster_states CSTON CND.replica_server_name=CST.replica_server_nameJOIN sys.dm_hadr_availability_replica_states RSTON CST.replica_id=RST.replica_id

4.绑定监听端口，删除监听器，监听端口查看

--查看监听端口状态USE [master]GOSELECT * FROM sys.availability_group_listenersGO--绑定监听端口USE [master]GOALTER AVAILABILITY GROUP [HAGroup1]MODIFY LISTENER N'SQLAG1-Subnet17' (PORT=1433);GO--删除监听器USE [master]GOALTER AVAILABILITY GROUP [HAGroup1]REMOVE LISTENER N'SQLAG1-Subnet17'; GO

37.数据库restoring或数据库正在还原

restore database 数据库A with recovery或者 RESTORE DATABASE db FROM DISK='c:\1.bak' (WITH RECOVERY )

38.数据库备份详情

SELECT TOP 1000 [backup_set_id],a.[media_set_id],[expiration_date],[name],[user_name],

[software_major_version],[backup_start_date],[backup_finish_date],[type],[compatibility_level],

[backup_size],[database_name] ,[server_name], [is_password_protected],[recovery_model],[is_damaged] ,

[begins_log_chain],[compressed_backup_size], b.physical_device_name

FROM [msdb].[dbo].[backupset] a,[msdb].[dbo].[backupmediafamily] b where a.media_set_id=b.media_set_id order by backup_set_id desc

SELECT backup_set_id,

database_name,

(CASE type

WHEN 'd'THEN'完整'

WHEN 'i'THEN'差异数据库'

WHEN 'l'THEN'日志'

ELSE type

END)AS backuptype,

b.physical_device_name,

backup_start_date,

backup_finish_date,

recovery_model,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn

FROM msdb.dbo.backupset a

INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id=b.media_set_id

where backup_start_date>'-12-17 13:26:13.000' --缩小范围到这个时间左右

and database_name='db_tank'ORDER BY a.backup_finish_date

38.DBCC checkdb 的正确杂项信息

--2、使用修复命令DBCC CHECKTABLE('CW_KCK',REPAIR_REBUILD)--表示无损修复DBCC CHECKTABLE('CW_KCK',REPAIR_FAST)--表示快速无损修复DBCC CHECKTABLE('CW_KCK',REPAIR_ALLOW_DATA_LOSS)

--表示丢失数据修复

dbcc checkdb withNO_INFOMSGS

--表示不显示相关杂项信息，只显示错误与相关关键信息

39.查看数据库版本

--查看数据库大小select name, filename, convert(float, size) * (8192/1024)/1024/1024 as dbsize_G from test.dbo.sysfiles union allselect name, filename, convert(float, size) * (8192/1024)/1024/1024 as dbsize_G from test.dbo.sysfiles --查看磁盘USE [db_del]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: -- Create date: -2-1-- Description: 收集磁盘剩余空间信息-- =============================================if OBJECT_ID('sp_get_disk_free_size') IS NOT NULL DROP proc [dbo].[sp_get_disk_free_size] GOCREATE PROCEDURE [dbo].[sp_get_disk_free_size]ASBEGINSET NOCOUNT ON;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;--==========================================--创建相关表IF OBJECT_ID('server_disk_usage') IS NOT NULLDROP TABLE server_disk_usageCREATE TABLE [dbo].[server_disk_usage]([disk_num] [nvarchar](10) NOT NULL,[total_size_mb] [bigint] NOT NULL CONSTRAINT [DF_server_disk_usage_total_size_mb] DEFAULT ((0)),[free_siez_mb] [bigint] NOT NULL CONSTRAINT [DF_server_disk_usage_free_siez_mb] DEFAULT ((0)),[disk_info] [nvarchar](400) NOT NULL CONSTRAINT [DF_server_disk_usage_disk_info] DEFAULT (''),[check_time] [datetime] NOT NULL CONSTRAINT [DF_server_disk_usage_check_time] DEFAULT (getdate()),CONSTRAINT [PK_server_disk_usage] PRIMARY KEY CLUSTERED ([disk_num] ASC)) ON [PRIMARY]--==========================================--查看所有数据库使用到的磁盘剩余空间DECLARE @disk TABLE([disk_num] VARCHAR(50),[free_siez_mb] INT)INSERT INTO @diskEXEC xp_fixeddrives--更新当前磁盘的剩余空间信息UPDATE MSET M.[free_siez_mb]=D.[free_siez_mb]FROM [dbo].[server_disk_usage] AS MINNER JOIN @disk AS DON M.[disk_num]=D.[disk_num]--插入新增磁盘的剩余空间信息INSERT INTO [dbo].[server_disk_usage]([disk_num],[free_siez_mb])SELECT [disk_num],[free_siez_mb]FROM @disk AS DWHERE NOT EXISTS(SELECT 1FROM [dbo].[server_disk_usage] AS M WHERE M.[disk_num]=D.[disk_num] )ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: GGA-- Create date: -2-1-- Description: 收集磁盘总空间信息-- =============================================if OBJECT_ID('sp_get_disk_total_size') IS NOT NULL DROP proc [dbo].[sp_get_disk_total_size] GOCREATE PROCEDURE [dbo].[sp_get_disk_total_size]ASBEGINSET NOCOUNT ON;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;IF NOT EXISTS(SELECT * FROM [dbo].[server_disk_usage]WHERE [total_size_mb] = 0)BEGINRETURN;END--==========================================--开启CMDShellEXEC sp_configure 'show advanced options',1;RECONFIGURE WITH OVERRIDE;EXEC sp_configure 'xp_cmdshell',1;RECONFIGURE WITH OVERRIDE--========================================--创建临时表用来存放每个盘符的数据CREATE TABLE #tempDisks(ID INT IDENTITY(1,1),DiskSpace NVARCHAR(200))--============================================--将需要检查的磁盘放入临时表#checkDisksSELECT ROW_NUMBER()OVER(ORDER BY [disk_num]) AS RID,[disk_num]INTO #checkDisksFROM [dbo].[server_disk_usage] WHERE [total_size_mb] = 0;--============================================--循环临时表#checkDisks检查每个磁盘的总量DECLARE @disk_num NVARCHAR(20)DECLARE @total_size_mb INTDECLARE @sql NVARCHAR(200)DECLARE @max INTDECLARE @min INTSELECT @max=MAX(RID),@min=MIN(RID) FROM #checkDisksWHILE(@min<=@max)BEGINSELECT @disk_num=[disk_num] FROM #checkDisks WHERE RID=@minSET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree '+@disk_num+':'+''''PRINT @sqlINSERT INTO #tempDisksEXEC sys.sp_executesql @sqlSELECT @total_size_mb=CAST((RIGHT(DiskSpace,LEN(DiskSpace)-CHARINDEX(': ',DiskSpace)-1)) AS BIGINT)/1024/1024FROM #tempDisks WHERE id = 2SELECT @total_size_mb,@disk_numUPDATE [dbo].[server_disk_usage]SET [total_size_mb]=@total_size_mbWHERE [disk_num]=@disk_num--SELECT * FROM #tempDisksTRUNCATE TABLE #tempDisksSET @min=@min+1END--==========================================--CMDShellEXEC sp_configure 'xp_cmdshell',0;EXEC sp_configure 'show advanced options',1;RECONFIGURE WITH OVERRIDE;ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: -- Create date: -2-1-- Description: 收集磁盘总空间信息-- =============================================if OBJECT_ID('sp_get_disk_usage') IS NOT NULL DROP proc [dbo].[sp_get_disk_usage] GOCREATE PROCEDURE [dbo].[sp_get_disk_usage]ASBEGINSET NOCOUNT ON;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;EXEC [dbo].[sp_get_disk_free_size]EXEC [dbo].[sp_get_disk_total_size]SELECT [disk_num] AS Drive_Name,CAST([total_size_mb]/1024.0 AS NUMERIC(18,2)) AS Total_Space_GB,CAST(([total_size_mb]-[free_siez_mb])/1024.0 AS NUMERIC(18,2)) AS Used_Space_GB,CAST([free_siez_mb]/1024.0 AS NUMERIC(18,2)) AS Free_Space_GB,CAST([free_siez_mb]*100/[total_size_mb] AS NUMERIC(18,2)) AS Free_Space_Percent,[disk_info],[check_time]FROM [db_del].[dbo].[server_disk_usage]ENDGO--==================================--查看磁盘空间使用EXEC [dbo].[sp_get_disk_usage]--SQL Server 版本SELECT SERVERPROPERTY('servername') AS 实例名, SERVERPROPERTY('ProductVersion') AS 实例版本, SERVERPROPERTY('Edition') AS 产品版本, SERVERPROPERTY('ProductLevel') AS 版本级别, @@VERSION AS 版本信息 --最大IDIF OBJECT_ID('db_tank..v_Sys_Users_Goods') IS NULLBEGINselect max(userid) ,len(max(userid)) as q from db_tank..sys_users_detail union allselect max(itemid) ,len(max(itemid)) from db_tank..Sys_Users_Goods union allselect max(id) ,len(max(id)) from db_tank..User_MessagesENDELSE BEGINselect max(userid) ,len(max(userid)) as q from db_tank..sys_users_detail union allselect max(itemid) ,len(max(itemid)) from db_tank..v_Sys_Users_Goods union allselect max(id) ,len(max(id)) from db_tank..User_MessagesEND

40.查看堆表及数据量超过10W行的，查看没有聚集索引的

select * from (SELECT tables.NAME, (SELECT rows FROM sys.partitions WHERE object_id = tables.object_id AND index_id = 0 -- 0 is for heap -- 1 is for clustered index And rows >=100000)AS numberofrows FROM db_tank.sys.tables tables WHERE Objectproperty(tables.object_id, N'TableHasClustIndex') = 0 )t where numberofrows is not null

41.T-SQL获取表的标识列/获取所有标识列

SELECT OBJECT_SCHEMA_NAME(tables.object_id, db_id())AS SchemaName,tables.name As TableName,identity_columns.name as ColumnName,identity_columns.seed_value,identity_columns.increment_value,identity_columns.last_valueFROM sys.tables tables JOIN sys.identity_columns identity_columns ON tables.object_id=identity_columns.object_idGO

42. 在MSSQL中删除文件

-- disable xp_cmdshellEXEC sp_configure 'show advanced options',1GORECONFIGUREexec sp_configure 'xp_cmdshell',1GORECONFIGURE-- run del dos command to delete a file/folderexec xp_cmdshell 'DEL d:\SQLQuery1.sql'

-- enable xp_cmdshell

exec sp_configure 'xp_cmdshell',0 GO RECONFIGURE EXEC sp_configure 'show advanced options',1 GO RECONFIGURE

43.查看备份集信息，查看备份文件信息，查看bak信息

--查看对应数据库的文件结构(如果多次备份集追加到一个文件上，只会显示第1个)RESTORE FILELISTONLY FROM DISK = N'E:\Sql_Server_test\backup\test_data.bak' --查看备份介质的标头信息RESTORE LABELONLY FROM DISK = 'E:\Sql_Server_test\backup\test_data.bak' --查看备份集的标头信息（查看备份介质上有哪些备份集）RESTORE HEADERONLY FROM disk = N'E:\Sql_Server_test\backup\test_data.bak'

--如果在headerOnly中查出有多个备份集，则可以用一下语句还原指定备份集

RESTORE DATABASE AdventureWorks FROM DISK = 'E:\Sql_Server_test\backup\test_data.bak' WITHFILE= 3

44、查看被删除表信息

declare @database_name varchar(200),@type varchar(2),@pass_hours int,select @database_name='AdventureWorks',@pass_hours=-48declare @file_path sql_variantselect @file_path=value from fn_trace_getinfo(0) where property=2 and traceid=1SELECT max(gt.HostName) as 'LoginHostName',max(gt.ApplicationName) as 'ApplicationName',max(gt.LoginName) as 'LoginName',min(gt.StartTime) as 'MinStartTime',max(gt.StartTime) as 'MaxStartTime',gt.ObjectID,max(gt.ServerName) as 'DBServerName',max(gt.ObjectName) as 'ObjectName',max(gt.DatabaseName) as 'DatabaseName',max(sv.subclass_name) as 'subclass_name',max(e.name) as 'OperationType'FROM fn_trace_gettable(convert(varchar(2000),@file_path), DEFAULT) gt LEFT JOIN sys.trace_subclass_values sv ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype] INNER JOIN sys.trace_events e ON gt.[eventclass] = e.[trace_event_id] INNER JOIN sys.trace_categories c ON e.[category_id] = c.[category_id] WHERE StartTime>=dateadd(HOUR,@pass_hours,getdate()) and StartTime<=getdate()and [eventclass]=47 --drop talbe/indexand databasename=@database_nameand subclass_name='U'group by gt.ObjectID

45.清理缓存

DBCC MemoryStatus 查看内存状态清理缓存、清除缓存DBCC FREEPROCCACHE 清除存储过程相关的缓存DBCC FREESESSIONCACHE 会话缓存DBCC FREESYSTEMCACHE('All') 系统缓存DBCC DROPCLEANBUFFERS 所有缓存

46.数据库备份还原历史记录

备份还原的记录都在msdb里。备份记录

SELECT bs.backup_set_id,bs.database_name,bs.backup_start_date,bs.backup_finish_date,CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],CAST(DATEDIFF(second, bs.backup_start_date,bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],CASE bs.[type]WHEN 'D' THEN 'Full Backup'WHEN 'I' THEN 'Differential Backup'WHEN 'L' THEN 'TLog Backup'WHEN 'F' THEN 'File or filegroup'WHEN 'G' THEN 'Differential file'WHEN 'P' THEN 'Partial'WHEN 'Q' THEN 'Differential Partial'END AS BackupType,bmf.physical_device_name,CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,bs.server_name,bs.recovery_modelFROM msdb.dbo.backupset bsINNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_idORDER BY bs.server_name,bs.database_name,bs.backup_start_date;

GO

--如果server_name是本机，那么备份是在本机生成的；

--如果server_name是别的主机名，那么备份是被拿到本机做过数据库还原；

47、还原纪录

SELECT rs.[restore_history_id],rs.[restore_date],rs.[destination_database_name],bmf.physical_device_name,rs.[user_name],rs.[backup_set_id],CASE rs.[restore_type]WHEN 'D' THEN 'Database'WHEN 'I' THEN 'Differential'WHEN 'L' THEN 'Log'WHEN 'F' THEN 'File'WHEN 'G' THEN 'Filegroup'WHEN 'V' THEN 'Verifyonly'END AS RestoreType,rs.[replace],rs.[recovery],rs.[restart],rs.[stop_at],rs.[device_count],rs.[stop_at_mark_name],rs.[stop_before]FROM [msdb].[dbo].[restorehistory] rsINNER JOIN [msdb].[dbo].[backupset] bs--on rs.backup_set_id = bs.media_set_idON rs.backup_set_id = bs.backup_set_idINNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_idGO

--还原数据库的时候是会写backupset和backupmediafamily系统表的，用来记录还原所用到的备份文件信息。

48、查看未使用索引

SELECTind.Index_id,obj.Name as TableName,ind.Name as IndexName,ind.Type_Desc,indUsage.user_seeks,indUsage.user_scans,indUsage.user_lookups,indUsage.user_updates,indUsage.last_user_seek,indUsage.last_user_scan,'drop index [' + ind.name + '] ON [' + obj.name + ']' asDropIndexCommandFROMSys.Indexes as indJOINSys.Objects as objONind.object_id=obj.Object_IDLEFT JOINsys.dm_db_index_usage_stats indUsageONind.object_id = indUsage.object_idANDind.Index_id=indUsage.Index_idWHEREind.type_desc<>'HEAP' and obj.type<>'S'ANDobjectproperty(obj.object_id,'isusertable') = 1AND(isnull(indUsage.user_seeks,0) = 0ANDisnull(indUsage.user_scans,0) = 0ANDisnull(indUsage.user_lookups,0) = 0)ORDER BYobj.name,ind.NameGO

49.查看缺失索引

SELECTavg_total_user_cost * avg_user_impact * (user_seeks + user_scans)AS PossibleImprovement,last_user_seek,last_user_scan,statement AS Object,'CREATE INDEX [IDX_' + CONVERT(VARCHAR,GS.Group_Handle) + '_' +CONVERT(VARCHAR,D.Index_Handle) + '_'+ REPLACE(REPLACE(REPLACE([statement],']',''),'[',''),'.','') +']'+' ON '+ [statement]+ ' (' + ISNULL (equality_columns,'')+ CASE WHEN equality_columns IS NOT NULL AND inequality_columns ISNOT NULL THEN ',' ELSE '' END+ ISNULL (inequality_columns, '')+ ')'+ ISNULL (' INCLUDE (' + included_columns + ')', '')AS Create_Index_SyntaxFROMsys.dm_db_missing_index_groups AS GINNER JOINsys.dm_db_missing_index_group_stats AS GSONGS.group_handle = G.index_group_handleINNER JOINsys.dm_db_missing_index_details AS DONG.index_handle = D.index_handleOrder By PossibleImprovement DESC

------------------缺失索引-----------------------SELECT migs.group_handle, mid.* FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN sys.dm_db_missing_index_groups AS mig ON (migs.group_handle = mig.index_group_handle) INNER JOIN sys.dm_db_missing_index_details AS mid ON (mig.index_handle = mid.index_handle) WHERE migs.group_handle = 2----------------------------------无用索引----------------------SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME() AS DatbaseName , SCHEMA_NAME(O.Schema_ID) AS SchemaName , OBJECT_NAME(I.object_id) AS TableName , I.name AS IndexName INTO #TempNeverUsedIndexes FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempNeverUsedIndexes SELECT DB_NAME() AS DatbaseName , SCHEMA_NAME(O.Schema_ID) AS SchemaName , OBJECT_NAME(I.object_id) AS TableName , I.NAME AS IndexName FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id AND I.index_id = S.index_id AND DATABASE_ID = DB_ID() WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0 AND I.name IS NOT NULL AND S.object_id IS NULL' SELECT * FROM #TempNeverUsedIndexes ORDER BY DatbaseName, SchemaName, TableName, IndexName DROP TABLE #TempNeverUsedIndexes--------------------------经常被大量更新，但是却基本不适用的索引项--------------------SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , s.user_updates , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] INTO #TempUnusedIndexes FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUnusedIndexes SELECT TOP 20 DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , s.user_updates , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 AND i.name IS NOT NULL ORDER BY s.user_updates DESC' SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC DROP TABLE #TempUnusedIndexes

50.查看日志不能截断的原因（截断日志，收缩日志）

SELECT log_reuse_wait , log_reuse_wait_desc FROM sys.databases WHERE name='db01'

51.查看是否有未提交的事务

select * from master..sysprocesses where open_tran > 0 or blocked > 0

dbcc opentran

52.慢查询，慢SQL

--总耗时最长SELECT TOP 10total_worker_time / 1000 AS N'总消耗CPU 时间(ms)' ,execution_count N'运行次数' ,qs.total_worker_time / qs.execution_count / 1000 AS N'平均消耗CPU 时间(ms)' ,last_execution_time AS N'最后一次执行时间' ,max_worker_time / 1000 AS N'最大执行时间(ms)' ,SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1,( CASE WHEN qs.statement_end_offset = -1THEN DATALENGTH(qt.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset ) / 2 + 1) AS N'使用CPU的语法' ,qt.text N'完整语法'FROM sys.dm_exec_query_stats qs WITH ( NOLOCK )CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtWHERE execution_count > 1ORDER BY max_worker_time DESC--平均耗时最长SELECT TOP 10( total_elapsed_time / execution_count ) / 1000 N'平均时间ms' ,total_elapsed_time / 1000 N'总花费时间ms' ,total_worker_time / 1000 N'所用的CPU总时间ms' ,total_physical_reads N'物理读取总次数' ,total_logical_reads / execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logical_writes N'逻辑写入总次数' ,execution_count N'执行次数' ,SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1,( ( CASE statement_end_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset ) / 2 ) + 1) N'完整语法' ,creation_time N'语句编译时间' ,last_execution_time N'上次执行时间'FROM 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_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset ) / 2 ) + 1) NOT LIKE '%tch%'ORDER BY total_elapsed_time / execution_count DESC--物理读耗时最长SELECT TOP 10qs.total_physical_reads N'物理读取总次数' ,qs.execution_count N'执行次数' ,qs.total_physical_reads / qs.execution_count / 1000 AS N'平均时间ms' ,SUBSTRING(qt.text, ( qs.statement_start_offset / 2 ) + 1,( ( CASE statement_end_offsetWHEN -1 THEN DATALENGTH(qt.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset ) / 2 ) + 1) N'完整语法'FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtORDER BY qs.total_physical_reads DESC --逻辑读耗时最长SELECT TOP 10qs.total_logical_reads N'逻辑读取总次数' ,qs.execution_count N'执行次数' ,qs.total_logical_reads / qs.execution_count / 1000 AS N'平均时间ms' ,SUBSTRING(qt.text, ( qs.statement_start_offset / 2 ) + 1,( ( CASE statement_end_offsetWHEN -1 THEN DATALENGTH(qt.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset ) / 2 ) + 1) N'完整语法'FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtORDER BY qs.total_logical_reads DESC

--执行频繁的语句

with aa as (SELECT --执行次数 QS.execution_count, --查询语句 SUBSTRING(ST.text,(QS.statement_start_offset/2)+1, ((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1 ) AS statement_text, --执行文本 ST.text, --执行计划 qs.last_elapsed_time,qs.min_elapsed_time,qs.max_elapsed_time,QS.total_worker_time, QS.last_worker_time, QS.max_worker_time, QS.min_worker_time FROM sys.dm_exec_query_stats QS --关键字 CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST WHERE QS.last_execution_time > '-02-14 00:00:00' and execution_count > 500-- AND ST.text LIKE '%%' --ORDER BY --QS.execution_count DESC)select text,max(execution_count) execution_count --,last_elapsed_time,min_elapsed_time,max_elapsed_time from aawhere [text] not like '%sp_MSupd_%' and [text] not like '%sp_MSins_%' and [text] not like '%sp_MSdel_%' group by textorder by 2 desc

--IO最大

SELECTTOP20

[TotalIO]=(qs.total_logical_reads+qs.total_logical_writes)

,[AverageIO]=(qs.total_logical_reads+qs.total_logical_writes)/

qs.execution_count

,qs.execution_count

,SUBSTRING(qt.text,(qs.statement_start_offset/2)+1,

((CASEWHENqs.statement_end_offset=-1

THENLEN(CONVERT(NVARCHAR(MAX),qt.text))*2

ELSEqs.statement_end_offset

END-qs.statement_start_offset)/2)+1)AS[IndividualQuery]

,qt.textAS[ParentQuery]

,DB_NAME(qt.dbid)ASDatabaseName

,qp.query_plan

FROMsys.dm_exec_query_statsqs

CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)asqt

CROSSAPPLYsys.dm_exec_query_plan(qs.plan_handle)qp

ORDERBY[AverageIO]DESC

53.查看实例配置，查看数据库参数

select * from sys.configurations

54.杀掉所有进程，kill所有进程，杀掉所有连接，kill所有连接

declare @sql as varchar(20), @spid as intselect @spid = min(spid) from master..sysprocesses where dbid = db_id('<database_name>') and spid != @@spid while (@spid is not null)beginprint 'Killing process ' + cast(@spid as varchar) + ' ...'set @sql = 'kill ' + cast(@spid as varchar)exec (@sql)select @spid = min(spid) from master..sysprocesses where dbid = db_id('<database_name>') and spid != @@spidendprint 'Process completed...'

55、查看某个表在哪个数据库中存在

----查找当前数据库服务器中某张表存在于哪个数据库中--declare @tableName varchar(50)--这里设置要查询的表名字set @tableName='sys_users_goods'--清理临时表if object_id('tempdb..#tmpdbs') is not null Begindrop table #tmpdbsEndif object_id('tempdb..##tmpResults') is not null Begindrop table ##tmpResultsEnd--手动创建全局临时表,用于保存查询结果.下面插入时只能使用insert into ,不能使用select into ,后者会自动创建临时表create table ##tmpResults(DbName varchar(50),Name varchar(50),XType varchar(50))Select Name,ROW_NUMBER() over(order by Name) as rowid into #tmpdbs FROM Master..SysDatabases Namedeclare @dbName varchar(50)declare @rowid intdeclare @count intset @rowid=1select @count=count(*) from #tmpdbswhile @rowid <= @countbegin--print(@rowid)select @dbName=[Name] from #tmpdbs where rowid=@rowidexec ('insert into ##tmpResults Select '''+@dbName+''' as DbName,Name,xtype FROM '+@dbName+'..SysObjects Where (XType=''U'' or XType=''SN'') and Name='''+@tableName+''' ORDER BY Name')set @rowid=@rowid+1end--查看结果select * from ##tmpResults--清理临时表if object_id('tempdb..#tmpdbs') is not null Begindrop table #tmpdbsEndif object_id('tempdb..##tmpResults') is not null Begindrop table ##tmpResultsEnd

View Code

56、 sql server 监控信息

--查询当前数据库的配置信息Select configuration_id ConfigurationId,name Name,description Description,Cast(value as int) value,Cast(minimum as int) Minimum,Cast(maximum as int) Maximum,Cast(value_in_use as int) ValueInUse,is_dynamic IsDynamic,is_advanced IsAdvancedFrom sys.configurationsOrder By is_advanced, name--检查SQL SERVER 当前已创建的线程数select count(*) from sys.dm_os_workers--查询当前连接到数据库的用户信息Select s.login_name LoginName,s.host_name HostName,s.transaction_isolation_level TransactionIsolationLevel,Max(c.connect_time) LastConnectTime,Count(*) ConnectionCount,Sum(Cast(c.num_reads as BigInt)) TotalReads,Sum(Cast(c.num_writes as BigInt)) TotalWritesFrom sys.dm_exec_connections cJoin sys.dm_exec_sessions sOn c.most_recent_session_id = s.session_idGroup By s.login_name, s.host_name, s.transaction_isolation_level--查询CPU和内存利用率 及以上可用Select DateAdd(s, (timestamp - (osi.cpu_ticks / Convert(Float, (osi.cpu_ticks / osi.ms_ticks)))) / 1000, GETDATE()) AS EventTime,Record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,Record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as ProcessUtilization,Record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'int') as MemoryUtilizationFrom (Select timestamp,convert(xml, record) As RecordFrom sys.dm_os_ring_buffersWhere ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'And record Like '%<SystemHealth>%') xCross Join sys.dm_os_sys_info osiOrder By timestamp--查看每个数据库缓存大小SELECT COUNT(*) * 8 / 1024 AS 'Cached Size (MB)' ,CASE database_idWHEN 32767 THEN 'ResourceDb'ELSE DB_NAME(database_id)END AS 'Database'FROM sys.dm_os_buffer_descriptorsGROUP BY DB_NAME(database_id) ,database_idORDER BY 'Cached Size (MB)' DESC--SQL SERVER 统计IO活动信息SET STATISTICS IO ONselect top 10* from TableSET STATISTICS IO OFF--SQL SERVER 清除缓存SQL语句CHECKPOINT;GODBCC FREEPROCCACHE---清空执行计划缓存DBCC DROPCLEANBUFFERS; --清空数据缓存GO--查看当前进程的信息DBCC INPUTBUFFER(51)--查看当前数据是否启用了快照隔离DBCC USEROPTIONS;--查看摸个数据库数据表中的数据页类型--In_Row_Data: 分别为存储行内数据的--LOB_Data: 存储Lob对象,Lob对象用于存储存在数据库的二进制文件--当这个类型的列出现时,原有的列会存储一个24字节的指针，而将具体的二进制数据存在LOB页中--Row_Overflow_data:存储溢出数据的,使用Varchar,nvarchar等数据类型时，当行的大小不超过8060字节时，全部存在行内In-row data--当varchar中存储的数据过多使得整行超过8060字节时，会将额外的部分存于Row-overflow data页中，--如果update这列使得行大小减少到小于8060字节，则这行又会全部回到in-row data页--text,ntext和image类型来说，每一列只要不为null,即使占用很小的数据，也需要额外分配一个LOB页DBCC IND ( Lawyer, [dbo.tb_Contract], -1)

57、杂项查询

declare @v_id intdeclare @sql varchar(100)select @v_id=p.spidfrom msdb..sysjobs jinner join master..sysprocesses pon substring(left(cast(j.job_id as varchar(36)),8),7,2) +substring(left(cast(j.job_id as varchar(36)),8),5,2) +substring(left(cast(j.job_id as varchar(36)),8),3,2) +substring(left(cast(j.job_id as varchar(36)),8),1,2) = substring(p.program_name,32,8)where p.program_name like 'SQLAgent - TSQL JobStep (Job%'set @sql = "USE msdb ;EXEC dbo.sp_stop_job @job_name=''DB_Tank_Back_TRN'' ;"if @v_id is not nullexec(@sql)-- 停止作业USE msdb ;GOEXEC dbo.sp_stop_job N'DB_Tank_Back_TRN' ;GO-- 查看 SPselect a.name,a.[type],b.[definition]from sys.all_objects a,sys.sql_modules bwhere a.is_ms_shipped=0 and a.object_id = b.object_id and a.[type] in ('P','V','AF')order by a.[name] asc-- 查询某个表被哪些存储过程（简称 SP）使用到:select distinct object_name(id)from syscommentswhere id in (select object_idfrom sys.objectswhere type ='P') and text like '%TableName%'-- 查询某个SP被哪些作业调用：SELECT * FROM msdb.dbo.sysjobs JOB WITH( NOLOCK) INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK ) ON STP.job_id = JOB.job_id WHERE mand LIKE N'%SP_FAD_Coder_Charge_money%'-- 查询当前数据库中所有SP：select name as 存储过程 from sysobjects where type = 'P'-- 查询数据库常用属性select name,log_reuse_wait_desc,recovery_model_desc,compatibility_level from sys.databaseswhere name not IN('resource','master','tempdb','model','msdb') ---------------------------------------------------------------------------------- 查询正在执行的任务select start_time,command,percent_complete,text,session_id,blocking_session_idfrom sys.dm_exec_requests rcross apply sys.dm_exec_sql_text(r.sql_handle) swhere mand in ('BACKUP DATABASE','RESTORE DATABASE','BACKUP LOG','RESTORE LOG','DbccFilesCompact','DbccSpaceReclaim')---------------------------------------------------------------------------------- 查询正在执行的SQLSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECTr.session_id ,DB_NAME(qt.[dbid]) AS [DatabaseName] ,r.start_time,r.[status],r.blocking_session_id,SUBSTRING(qt.[text], r.statement_start_offset / 2,( CASE WHEN r.statement_end_offset = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2ELSE r.statement_end_offsetEND - r.statement_start_offset ) / 2) AS [statement] ,r.wait_type,r.wait_time,r.wait_resource,r.cpu_time ,r.total_elapsed_time / 60000 AS[elapsed_minutes],r.reads ,r.writes ,r.logical_reads,s.host_name,s.original_login_name,s.program_nameFROM sys.dm_exec_requests AS rjoin sys.dm_exec_sessions s on r.session_id = s.session_idCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qtWHERE r.session_id > 50ORDER BY 1--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 = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))* 2ELSE er.statement_end_offsetEND - er.statement_start_offset )/ 2) ,[Parent Query] = qt.text ,Program = program_name ,hostname ,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.AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.ORDER BY 1 ,2-------------------------------------------------------------------------------------------------------------- 切换不了单用户模式, 需要先把连接kill掉declare @dbname varchar(50)set @dbname='数据库名称'declare @sql varchar(50)declare cs_result cursor local for select 'kill '+cast(spid as varchar(50)) from sys.sysprocesses where db_name(dbid)=@dbnameopen cs_resultfetch next from cs_result into @sqlwhile @@fetch_status=0beginexecute(@sql)fetch next from cs_result into @sqlendclose cs_resultdeallocate cs_result--------------------------------------------------------------------------------查询数据库中指定表的表结构：--快速查看表结构SELECT CASE WHEN col.colorder = 1 THEN obj.nameELSE ''END AS 表名,col.colorder AS 序号 ,col.name AS 列名 ,ISNULL(ep.[value], '') AS 列说明 ,t.name AS 数据类型 ,col.length AS 长度 ,ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '1'ELSE ''END AS 标识 ,CASE WHEN EXISTS ( SELECT 1FROMdbo.sysindexes siINNER JOIN dbo.sysindexkeys sik ON si.id = sik.idAND si.indid = sik.indidINNER JOIN dbo.syscolumns sc ON sc.id = sik.idAND sc.colid = sik.colidINNER JOIN dbo.sysobjects so ON so.name = si.nameAND so.xtype = 'PK'WHERE sc.id = col.idAND sc.colid = col.colid ) THEN '1'ELSE ''END AS 主键 ,CASE WHEN col.isnullable = 1 THEN '1'ELSE ''END AS 允许空 ,ISNULL(comm.text, '') AS 默认值FROM dbo.syscolumns colLEFT JOIN dbo.systypes t ON col.xtype = t.xusertypeinner JOIN dbo.sysobjects obj ON col.id = obj.idAND obj.xtype = 'U'AND obj.status >= 0LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.idLEFT JOIN sys.extended_properties ep ON col.id = ep.major_idAND col.colid = ep.minor_idAND ep.name = 'MS_Description'LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_idAND epTwo.minor_id = 0AND epTwo.name = 'MS_Description'WHERE obj.name = 'PkAutoInc' --表名ORDER BY col.colorder ;-- 查询备份Select Top 20 Convert(Varchar(40), S.database_name) AS [Database Name],Convert(varchar(19),S.backup_start_date, 120) AS [Start Date],Convert(varchar(19), S.backup_finish_date, 120) AS [Finish Date],Datediff(s, S.backup_start_date, S.backup_finish_date) AS [Duration (s)],CasetypeWhen 'D' Then 'FULL'When 'L' Then 'Log'When 'I' Then 'Differential'When 'F' Then 'File Group' End As Type,Convert(Varchar(15), Convert(Decimal(5, 2), Round(S.backup_size / Square(1024), 2))) AS [Size (MB)],Convert(varchar(19), S.expiration_date, 80) AS [Expiration Date],M.physical_device_name AS [Phys. Device Name],M.logical_device_name As [Logi. Device Name],S.backup_sizeFrommsdb.dbo.backupset S Inner Joinmsdb.dbo.backupmediafamily M ON S.media_set_id =M.media_set_idwhere M.physical_device_name not like '%trn%'Order ByS.backup_finish_date Desc-------------------------------------------------------------------- sql server分页declare @page_index int,@page_size intset @page_index = 1set @page_size = 30if @page_index = 1select top(@page_size) * from v_userstops order by idelseselect * from ((select *, row_number() over(order by id) as rid from v_userstops)) as awhere rid between (@page_index - 1) * @page_size + 1 and @page_index * @page_size ------------------------------------------------------------------SELECT TOP 10substring(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 ) as statement_text ,total_worker_time / 1000 as total_worker_time_ms,execution_count,( total_worker_time / 1000 ) / execution_count as avg_worker_time_ms,total_logical_reads,total_logical_reads / execution_count as avg_logical_reads,qp.query_planFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle ) stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY total_worker_time DESC--------------------------------------------------------------------------------EXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'xp_cmdshell', 1RECONFIGUREEXEC sp_configure 'show advanced options', 0RECONFIGUREexec master.dbo.xp_fixeddrives exec xp_cmdshell 'wmic LOGICALDISK get name'exec xp_cmdshell 'wmic LOGICALDISK get freespace'exec xp_cmdshell 'wmic LOGICALDISK get size'--------------------------------------------------------------------------------生成插入语句DECLARE @fromdb VARCHAR(100)DECLARE @todb VARCHAR(100)DECLARE @tablename VARCHAR(100)DECLARE @columnnames NVARCHAR(max)DECLARE @isidentity NVARCHAR(30)DECLARE @temsql NVARCHAR(max)DECLARE @sql NVARCHAR(max)DECLARE @i intDECLARE @ii intSET @fromdb = 'db_tank'SET @todb = 'tank2'set @i = 1SELECT @ii=count(1) from sys.tables WHERE type='U'--游标DECLARE @itemCur CURSORSET @itemCur = CURSOR FORSELECT '['+[name]+']' from sys.tables WHERE type='U' order by nameOPEN @itemCurFETCH NEXT FROM @itemCur INTO @tablenameWHILE @@FETCH_STATUS=0BEGINSET @sql = ''--获取表字段SET @temsql = N'BEGINSET @columnnamesOUT =''''SELECT @columnnamesOUT = @columnnamesOUT + '',['' + name +'']''From sys.columns where object_id=OBJECT_ID(''['+@fromdb+'].dbo.'+@tablename+''')order by column_idSELECT @columnnamesOUT=substring(@columnnamesOUT,2,len(@columnnamesOUT))END'EXEC sp_executesql @temsql,N'@columnnamesOUT NVARCHAR(max) OUTPUT',@columnnamesOUT=@columnnames OUTPUTPRINT ('PRINT N''' + cast(@i as varchar(10))+ ' - ' + cast(@ii as varchar(10)) + '. alter table '+@tablename + '''')set @i = @i+1--判断是否有自增字段SET @temsql = N'BEGINSET @isidentityOUT =''''SELECT @isidentityOUT = nameFrom sys.columns where object_id=OBJECT_ID(''['+@fromdb+'].dbo.'+@tablename+''')and is_identity = 1END'EXEC sp_executesql @temsql,N'@isidentityOUT NVARCHAR(30) OUTPUT',@isidentityOUT=@isidentity OUTPUT--IDENTITY_INSERT ONIF @isidentity != ''BEGINSET @sql = 'SET IDENTITY_INSERT ['+@todb+'].[dbo].'+@tablename+' ON'END--INSERTSET @sql = @sql+'INSERT INTO ['+@todb+'].[dbo].'+@tablename+'('+@columnnames+')SELECT '+@columnnames+' FROM ['+@fromdb+'].[dbo].'+@tablename+''--IDENTITY_INSERT OFFIF @isidentity != ''BEGINSET @sql = @sql+'SET IDENTITY_INSERT ['+@todb+'].[dbo].'+@tablename+' OFF'END--返回SQLPRINT(@sql)PRINT('GO')+CHAR(13)FETCH NEXT FROM @itemCur INTO @tablenameENDCLOSE @itemCurDEALLOCATE @itemCur--------------------------------------------------------------------------------DECLARE @s VARCHAR(5000)SET @s = '1,211,33333,'SELECT n, CHARINDEX(',',@s,n), SUBSTRING(@s, n, CHARINDEX(',',@s,n) - n)FROM numsWHERE n <= LEN(@s) AND SUBSTRING(','+@s, n, 1) = ','--------------------------------------------------------------------------------Xp_delete_file take five parameters:File Type = 0 for backup files or 1 for report files.Folder Path = The folder to delete files. The path must end with a backslash "\".File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.Date = The cutoff date for what files need to be deleted.Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.来源： /questions/24582996/sql-server-xp-delete-file-parameters---------------------------------------------------------------------------------- =======更改temp目录use mastergoALTER DATABASE tempdbMODIFY FILE (NAME = tempdev, FILENAME = 'G:\tempdb.mdf');GOALTER DATABASE tempdbMODIFY FILE (NAME = templog, FILENAME = 'G:\templog.ldf');GO----------------------------------------------------------------------------------- 1. 表结构信息查询-- ========================================================================-- 表结构信息查询-- 邹建 .08(引用请保留此信息)-- ========================================================================SELECT TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),Column_id=C.column_id,ColumnName=C.name,PrimaryKey=ISNULL(IDX.PrimaryKey,N''),[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,Type=T.name,Length=C.max_length,Precision=C.precision,Scale=C.scale,NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,[Default]=ISNULL(D.definition,N''),ColumnDesc=ISNULL(PFD.[value],N''),IndexName=ISNULL(IDX.IndexName,N''),IndexSort=ISNULL(IDX.Sort,N''),Create_Date=O.Create_Date,Modify_Date=O.Modify_dateFROM sys.columns CINNER JOIN sys.objects OON C.[object_id]=O.[object_id]AND O.type='U'AND O.is_ms_shipped=0INNER JOIN sys.types TON C.user_type_id=T.user_type_idLEFT JOIN sys.default_constraints DON C.[object_id]=D.parent_object_idAND C.column_id=D.parent_column_idAND C.default_object_id=D.[object_id]LEFT JOIN sys.extended_properties PFDON PFD.class=1 AND C.[object_id]=PFD.major_id AND C.column_id=PFD.minor_id-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)LEFT JOIN sys.extended_properties PTBON PTB.class=1 AND PTB.minor_id=0 AND C.[object_id]=PTB.major_id-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)LEFT JOIN -- 索引及主键信息 (SELECT IDXC.[object_id],IDXC.column_id,Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,IndexName=IDX.NameFROM sys.indexes IDXINNER JOIN sys.index_columns IDXCON IDX.[object_id]=IDXC.[object_id]AND IDX.index_id=IDXC.index_idLEFT JOIN sys.key_constraints KCON IDX.[object_id]=KC.[parent_object_id]AND IDX.index_id=KC.unique_index_idINNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息 (SELECT [object_id], Column_id, index_id=MIN(index_id)FROM sys.index_columnsGROUP BY [object_id], Column_id) IDXCUQON IDXC.[object_id]=IDXCUQ.[object_id]AND IDXC.Column_id=IDXCUQ.Column_idAND IDXC.index_id=IDXCUQ.index_id) IDXON C.[object_id]=IDX.[object_id]AND C.column_id=IDX.column_id-- WHERE O.name=N'要查询的表' -- 如果只查询指定表,加上此条件ORDER BY O.name,C.column_id-- 2. 索引及主键信息-- ========================================================================-- 索引及主键信息-- 邹建 .08(引用请保留此信息)-- ========================================================================SELECT TableId=O.[object_id],TableName=O.Name,IndexId=ISNULL(KC.[object_id],IDX.index_id),IndexName=IDX.Name,IndexType=ISNULL(KC.type_desc,'Index'),Index_Column_id=IDXC.index_column_id,ColumnID=C.Column_id,ColumnName=C.Name,Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,[UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END,Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END,Disabled=CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END,Fill_factor=IDX.fill_factor,Padded=CASE WHEN IDX.is_padded=1 THEN N'√'ELSE N'' ENDFROM sys.indexes IDXINNER JOIN sys.index_columns IDXCON IDX.[object_id]=IDXC.[object_id]AND IDX.index_id=IDXC.index_idLEFT JOIN sys.key_constraints KCON IDX.[object_id]=KC.[parent_object_id]AND IDX.index_id=KC.unique_index_idINNER JOIN sys.objects OON O.[object_id]=IDX.[object_id]INNER JOIN sys.columns CON O.[object_id]=C.[object_id]AND O.type='U'AND O.is_ms_shipped=0AND IDXC.Column_id=C.Column_id-- INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息-- (-- SELECT [object_id], Column_id, index_id=MIN(index_id)-- FROM sys.index_columns-- GROUP BY [object_id], Column_id-- ) IDXCUQ-- ON IDXC.[object_id]=IDXCUQ.[object_id]-- AND IDXC.Column_id=IDXCUQ.Column_id--

58、查看权限信息

-- 查看 SQL 用户所属数据库角色use yourdbgoselect DbRole = g.name, MemberName = u.name, MemberSID = u.sidfrom sys.database_principals u, sys.database_principals g, sys.database_role_members mwhere g.principal_id = m.role_principal_idand u.principal_id = m.member_principal_idorder by 1, 2go-- 查看 SQL 登录帐户所属服务器角色use mastergoselect SrvRole = g.name, MemberName = u.name, MemberSID = u.sidfrom sys.server_principals u, sys.server_principals g, sys.server_role_members mwhere g.principal_id = m.role_principal_idand u.principal_id = m.member_principal_idorder by 1, 2go

-- 查看 SQL 用户被赋予的权限use yourdbgoexec sp_helprotect @username = 'user name'go

---登入名表select * from master.sys.syslogins ---登入名与服务器角色关联表select * from sys.server_role_members---服务器角色表select * from sys.server_principals----查询登入名拥有的服务器角色select SrvRole = g.name, MemberName = u.name, MemberSID = u.sidfrom sys.server_role_members m inner join sys.server_principals g on g.principal_id = m.role_principal_id inner join sys.server_principals u on u.principal_id = m.member_principal_id---数据库用户表select * from sysusers---数据库用户表角色关联表select * from sysmembers ---数据库角色表select * from sys.database_principals----查询数据库用户拥有的角色select ta.name as username,tc.name as databaserole from sysusers ta inner join sysmembers tb on ta.uid=tb.memberuidinner join sys.database_principals tc on tb.groupuid=tc.principal_id

--查询当前数据库用户关联的登入名use AdventureWorksR2select ta.name as loginname,tb.name as databaseusername from master.sys.syslogins ta inner join sysusers tb on ta.sid=tb.sid /*如果将当前数据库还原到另一台服务器实例上,刚好那台服务器上也存在person登入用户，你会发现二者的sid不一样，由于sid不一样,所以登入用户不具有当前数据库的访问权限,我们要想办法将二者关联起来。*/---关联登入名与数据库用户（将数据库用户的sid刷成登入名的sid）use AdventureWorksR2EXEC sp_change_users_login 'Update_One', 'person', 'person'Go

59.内存（

相关参考：sql server 运维时CPU，内存，操作系统等信息查询（用sql语句）

语句与内存：/gered/p/9969617.html

）

（1）数据库内存，实例内存--查看实例分配内存，适用select *,CAST(cntr_value/1024.0 as decimal(20,1)) MemoryMB from master.sys.sysperfinfo where counter_name='Total Server Memory (KB)' --查看实例分配内存，适用SELECT physical_memory_in_use_kb/1024 AS physical_memory_in_use_MB,* FROM sys.dm_os_process_memory （2）物理内存，服务器内存--适用于SQL Server 以及以上的版本:查看物理内存大小，已经使用的物理内存以及还剩下的物理内存。SELECT CEILING(total_physical_memory_kb * 1.0 / 1024 / 1024) AS [Physical Memory Size] ,CAST(available_physical_memory_kb * 1.0 / 1024 / 1024 AS DECIMAL(8, 4)) AS [Unused Physical Memory],CAST(( total_physical_memory_kb - available_physical_memory_kb ) * 1.0/ 1024 / 1024 AS DECIMAL(8, 4)) AS [Used Physical Memory],CAST(system_cache_kb*1.0 / 1024/1024 AS DECIMAL(8, 4)) AS [System Cache Size]FROM sys.dm_os_sys_memory-- 获取数据库服务器物理内存数(适用于所有版本)CREATE TABLE #TempTable([Index] VARCHAR(2000) ,[Name] VARCHAR(2000) ,[Internal_Value] VARCHAR(2000) ,[Character_Value] VARCHAR(2000));INSERT INTO #TempTableEXEC xp_msver;SELECT Internal_Value/1024 AS PhysicalMemoryFROM #TempTableWHERE Name = 'PhysicalMemory';DROP TABLE #TempTable;GO--适用于SQL Server 到 SQL Server SELECT CEILING(physical_memory_kb*1.0/1024/1024) AS [Physical Memory Size]FROM sys.dm_os_sys_info OPTION (RECOMPILE);

60.事务

--相关核心DMV

--dm_tran_locks --sp_lock

--sys.dm_tran_session_transactions：返回当前活动事务和会话的相关信息。

--sys.dm_tran_active_transactions：返回实例级别上，所有正在活动的事务信息。

--sys.dm_tran_database_transactions：返回数据库级别上的事务信息。

--查看未提交事务

select * from master..sysprocesses where open_tran > 0 or blocked > 0

dbcc opentran

---查看现在所有的事务 select '正在运行事务的会话的 ID'=session_id, --session_id与transaction_id的对应关系 '事务的 ID'=transaction_id, '正在处理事务的会话中的活动请求数'=enlist_count, '用户or系统事务'=case is_user_transaction when 1 then '事务由用户请求启动' when 0 then '系统事务' end, '本地or分布式事务'= case is_local when 0 then '分布式事务或登记的绑定会话事务' when 1 then '本地事务' end, '分布式事务类型'=case is_enlisted when 0 then '非登记的分布式事务' when 1 then '登记的分布式事务' end, '绑定会话中处于状态'=case is_enlisted when 0 then '事务在通过绑定会话的会话中处于非活动状态。' when 1 then '事务在通过绑定会话的会话中处于活动状态' end from sys.dm_tran_session_transactions --会话中的事务，识别所有打开的事务where is_user_transaction =1 ----活动事务的具体信息 select dt.transaction_id, dt.name, dt.transaction_begin_time, case dt.transaction_type when 1 then '读/写事务' when 2 then '只读事务' when 3 then '系统事务' when 4 then '分布式事务' end 'transaction type', case dt.transaction_state when 0 then '事务尚未完全初始化' when 1 then '事务已初始化但尚未启动' when 2 then '事务处于活动状态' when 3 then '事务已结束。该状态用于只读事务' when 4 then '已对分布式事务启动提交进程' when 5 then '事务处于准备就绪状态且等待解析' when 6 then '事务已提交' when 7 then '事务正在被回滚' when 8 then '事务已回滚' end 'transaction state', case dt.dtc_state when 1 then '活动' when 2 then '准备就绪' when 3 then '已提交' when 4 then '中止' when 5 then '已恢复' end dtc_state from sys.dm_tran_active_transactions dt --活动的事务 where transaction_id = 123 ---根据事务ID 和其对应的session_id 找到活动事务对应的执行语句 select dc.session_id, ds.login_name, ds.login_time, dc.connect_time, dc.client_net_address, ds.host_name, ds.program_name, case ds.status when 'sleeping' then '睡眠 - 当前没有运行任何请求 ' when 'running' then '正在运行 - 当前正在运行一个或多个请求 ' when 'Dormancy' then '休眠 – 会话因连接池而被重置，并且现在处于登录前状态' when 'Pre-connected' then '预连接 - 会话在资源调控器分类器中' end as status , ds.cpu_time as cpu_time_ms, ds.memory_usage*8 as memory_kb, ds.total_elapsed_time as total_elapsed_time_ms, case ds.transaction_isolation_level when 0 then '未指定' when 1 then '未提交读取' when 2 then '已提交读取' when 3 then '可重复' when 4 then '可序列化' when 5 then '快照' end '会话的事务隔离级别', dt.textfrom sys.dm_exec_connections dc --执行连接，最近执行的查询信息 cross apply sys.dm_exec_sql_text(dc.most_recent_sql_handle) dt join sys.dm_exec_sessions ds on dc.session_id=ds.session_id where dc.session_id = 55 复制代码[sql] view plain copy ---查看现在所有的事务 select '正在运行事务的会话的 ID'=session_id, --session_id与transaction_id的对应关系 '事务的 ID'=transaction_id, '正在处理事务的会话中的活动请求数'=enlist_count, '用户or系统事务'=case is_user_transaction when 1 then '事务由用户请求启动' when 0 then '系统事务' end, '本地or分布式事务'= case is_local when 0 then '分布式事务或登记的绑定会话事务' when 1 then '本地事务' end, '分布式事务类型'=case is_enlisted when 0 then '非登记的分布式事务' when 1 then '登记的分布式事务' end, '绑定会话中处于状态'=case is_enlisted when 0 then '事务在通过绑定会话的会话中处于非活动状态。' when 1 then '事务在通过绑定会话的会话中处于活动状态' end from sys.dm_tran_session_transactions --会话中的事务，识别所有打开的事务where is_user_transaction =1 ----活动事务的具体信息 select dt.transaction_id, dt.name, dt.transaction_begin_time, case dt.transaction_type when 1 then '读/写事务' when 2 then '只读事务' when 3 then '系统事务' when 4 then '分布式事务' end 'transaction type', case dt.transaction_state when 0 then '事务尚未完全初始化' when 1 then '事务已初始化但尚未启动' when 2 then '事务处于活动状态' when 3 then '事务已结束。该状态用于只读事务' when 4 then '已对分布式事务启动提交进程' when 5 then '事务处于准备就绪状态且等待解析' when 6 then '事务已提交' when 7 then '事务正在被回滚' when 8 then '事务已回滚' end 'transaction state', case dt.dtc_state when 1 then '活动' when 2 then '准备就绪' when 3 then '已提交' when 4 then '中止' when 5 then '已恢复' end dtc_state from sys.dm_tran_active_transactions dt --活动的事务 where transaction_id = 123 ---根据事务ID 和其对应的session_id 找到活动事务对应的执行语句 select dc.session_id, ds.login_name, ds.login_time, dc.connect_time, dc.client_net_address, ds.host_name, ds.program_name, case ds.status when 'sleeping' then '睡眠 - 当前没有运行任何请求 ' when 'running' then '正在运行 - 当前正在运行一个或多个请求 ' when 'Dormancy' then '休眠 – 会话因连接池而被重置，并且现在处于登录前状态' when 'Pre-connected' then '预连接 - 会话在资源调控器分类器中' end as status , ds.cpu_time as cpu_time_ms, ds.memory_usage*8 as memory_kb, ds.total_elapsed_time as total_elapsed_time_ms, case ds.transaction_isolation_level when 0 then '未指定' when 1 then '未提交读取' when 2 then '已提交读取' when 3 then '可重复' when 4 then '可序列化' when 5 then '快照' end '会话的事务隔离级别', dt.textfrom sys.dm_exec_connections dc --执行连接，最近执行的查询信息 cross apply sys.dm_exec_sql_text(dc.most_recent_sql_handle) dt join sys.dm_exec_sessions ds on dc.session_id=ds.session_id where dc.session_id = 55

61.开启高级选项配置

--开启高级功能

EXEC sp_configure 'show advanced options',1GORECONFIGUREGO

--查看用户会话选项

DBCC USEROPTIONS

62.数据库对比，表结构对比

--当然用 sql compare更好

/*使用说明：Old数据库为DB_V1，New数据库为[localhost].DB_V2。根据实际需要批量替换数据库名称脚本来源：/zhang502219048/p/11028767.html*/-- sysobjects插入临时表select s.name + '.' + t.name as TableName, t.* into #tempTA from DB_V1.sys.tables tinner join DB_V1.sys.schemas s on s.schema_id = t.schema_idselect s.name + '.' + t.name as TableName, t.* into #tempTB from [localhost].DB_V2.sys.tables tinner join [localhost].DB_V2.sys.schemas s on s.schema_id = t.schema_id-- syscolumns插入临时表select * into #tempCA from DB_V1.dbo.syscolumns select * into #tempCB from [localhost].DB_V2.dbo.syscolumns-- 第一个数据库表和字段 select b.TableName as 表名, a.name as 字段名, a.length as 长度, c.name as 类型into #tempAfrom #tempCA ainner join #tempTA b on b.object_id = a.idinner join systypes c on c.xusertype = a.xusertypeorder by b.name -- 第二个数据库表和字段 select b.TableName as 表名, a.name as 字段名, a.length as 长度, c.name as 类型into #tempBfrom #tempCB ainner join #tempTB b on b.object_id = a.idinner join systypes c on c.xusertype = a.xusertypeorder by b.name--删掉的字段select * from ( select * from #tempAexceptselect * from #tempB) a;--增加的字段select * from ( select * from #tempBexceptselect * from #tempA) a;