sql server内置存储过程、查看系统信息

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 SpaceAvailable
FROM sys.master_files df
CROSS APPLY sys.dm_os_volume_stats(df.database_id, df.file_id) ovs
where 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

Exec xp_readerrorlog 0,1,Null,Null,'20130409 12:10','20130409 12:30','Asc'
/*

(1). 存档编号（0~99）

(2). 日志类型（1为SQL Server日志，2为SQL Server Agent日志）

(3). 查询包含的字符串

(4). 查询包含的字符串

(5). LogDate开始时间

(6). LogDate结束时间

(7). 结果排序，按LogDate排序（Desc、Asc）

*/

详细参考：https://blog.csdn.net/wacthamu/article/details/24436629，https://blog.csdn.net/v1t1p9hvbd/article/details/71524155

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

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

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

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

8、查看系统进程：

（1）常见DMV

　　系统进程：select * from sys.sysprocesses

　　用户请求：select * from sys.dm_exec_requests

　　会话进程：select * from sys.dm_exec_sessions

　　等待进程：select * from sys.dm_os_wait_stats

（2）详细语句

--包含批处理中当前运行到的SQL（child_Query）
select status,start_time,command,percent_complete,wait_type,text as parent_Query,
[child_Query] = 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) ,
session_id,blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) qt   

--详细版,查看CPU消耗最多的10个语句
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 = -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 ,
            nt_domain ,
[Spid] = session_id ,
blocking_session_id
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.
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,
dr.command,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

-- 2008R2以下版本 想要看图形界面，直接复制内容，重命名为.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    cte
          AS (
              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_growth
                          WHEN 0 THEN STR(CAST((mf.growth / 128.0) AS DECIMAL(10, 2))) + ' MB'
                          WHEN 1 THEN STR(mf.growth) + '%'
                        END AS auto_grow_setting
              FROM      sys.master_files mf
                        JOIN sys.database_files df ON mf.name = df.name
              WHERE     mf.database_id = DB_ID()
             )
    SELECT  *,
            actual_size_MB - initial_size_MB AS change_in_MB_since_restart
    FROM    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 NULL
BEGIN
        CREATE TABLE
END

【2】存储过程

IF OBJECT_ID('db_tank..TS_UnrealContestRankReward') IS NOT  NULL
         DROP PROCEDURE UnrealContestRankReward
GO

CREATE PROCEDURE ……

END

【3】--列

IF COL_LENGTH(N'BattleTeam_ActiveRecordInfo',N'ID') IS NULL
BEGIN

END

【4】主键

IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE parent_obj=OBJECT_ID('db_tank..ServerActiveState') AND xtype='PK')
BEGIN

END

【5】默认约束

IF COL_LENGTH(N'[dbo].[Pet_TemplateInfo]', N'LowDamage') IS NOT NULL

begin

declare @DF1 varchar(100)

    SELECT @DF1=name  FROM sys.default_constraints

    WHERE 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 LowDamage

endalter 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 so
WHERE   so.type = 'V'

【6】默认约束

alter table Consortia alter column fightpower bigint not null
ALTER TABLE [dbo].Consortia ADD DEFAULT ((0)) FOR fightpower
go

【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 on
select * from dbo.Product
set statistics time off

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

set statistics io on
select * from dbo.Product
set statistics io off

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

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

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

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

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

--查看数据库文件与文件组信息
SELECT
    name as [database_name],
    COUNT (*) AS [DataFiles],
    COUNT (DISTINCT data_space_id) AS [Filegroups],
    SUM (size)*8/1024 AS [Size(MB)]    --default Kb
FROM sys.master_files
WHERE [type_desc] = N'ROWS' -- filter out log files/data_space_id 0
    AND [database_id] > 0  -- filter out system databases
    AND [FILE_ID] != 65537 -- filter out FILESTREAM
GROUP 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 all
select 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）使用 :

　　表被存储过程调用

SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容  
FROM syscomments sc  
INNER JOIN sysobjects obj ON sc.Id = obj.ID  
WHERE sc.TEXT LIKE '%Sys_Users_Detail.IsVIP%'

--sp_depends tab

表依赖关系，表之间关系 （参考：https://www.cnblogs.com/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.查看表是否分区及分区信息

已分区的表：

1. SELECT DISTINCT
2. t.name                            AS TableName
3. ,ps.name                          AS PSName
4. ,fg.name                          AS FileGroupName
5. ,f.name                           AS [FileName]
6. ,f.physical_name                  AS [FilePhysicalName]
7. --,dds.destination_id                AS PartitionNumber  --去除注释即可显示文件的分区数
8. FROM   sys.tables                        AS t
9. INNER JOIN sys.indexes            AS i
10. ON  (t.object_id = i.object_id)
11. INNER JOIN sys.partition_schemes  AS ps
12. ON  (i.data_space_id = ps.data_space_id)
13. INNER JOIN sys.destination_data_spaces AS dds
14. ON  (ps.data_space_id = dds.partition_scheme_id)
15. INNER JOIN sys.filegroups         AS fg
16. ON  dds.data_space_id = fg.data_space_id
17. INNER JOIN sys.database_files f
18. ON  f.data_space_id = fg.data_space_id
19. WHERE  t.name = 'tableName'

未分区的表：

1. SELECT t.[name], i.[name], i.[index_id], f.[name]
2. FROM sys.indexes i
3. INNER JOIN sys.filegroups f
4. ON i.data_space_id = f.data_space_id
5. INNER JOIN sys.tables AS t
6. ON i.[object_id] = t.[object_id]
7. WHERE t.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