SQLServer常用命令

--1.查看数据库版本信息

select @@version

--2.查看所有数据库名称及大小

exec sp_helpdb

--3.数据库的磁盘空间使用信息

exec sp_spaceused

--4.日志文件大小及使用情况

dbcc sqlperf(logspace)

--5.表的磁盘空间使用信息

exec sp_spaceused 't1'

--6.用户和进程信息

exec sp_who
exec sp_who2

--7.活动用户和进程的信息

exec sp_who 'active'

--8.查看进程中正在执行的SQL

exec sp_who2
dbcc inputbuffer(52)

---9.日志收缩

---dbcc sqlperf(logspace)
select * from sys.databases
SELECT file_id,name from sys.master_files WHERE database_id=25; 
SELECT file_id, name FROM sys.database_files;
---
USE master
GO
ALTER DATABASE dbtest_0613 SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE dbtest_0613 SET RECOVERY SIMPLE   
GO
USE dbtest_0613 
GO
DBCC SHRINKFILE (N'dbtest_0613_log' , 1, TRUNCATEONLY)
GO
USE master
GO
ALTER DATABASE dbtest_0613 SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE dbtest_0613 SET RECOVERY FULL   
GO

---10.temp问题

select name,log_reuse_wait_desc from sys.databases
dbcc loginfo
exec sp_spaceused

SELECT Name,
       physical_name,
       Size / 128.0 / 1024 AS [ Size(GB) ],
       FILEPROPERTY(Name, 'SpaceUsed') / 128.0 / 1024 AS [ SpaceUsed(GB) ],
       STR(FILEPROPERTY(Name, 'SpaceUsed') * 1.0 / Size * 100, 6, 3) AS [ SpaceUsed(%) ]
  FROM sys.database_files

查看tempdb的使用分配情况,及spid信息 

use tempdb
go
SELECT top 10 t1.session_id,
       t1.internal_objects_alloc_page_count,
       t1.user_objects_alloc_page_count,
       t1.internal_objects_dealloc_page_count,
       t1.user_objects_dealloc_page_count,
       t3.login_name,
       t3.status,
       t3.total_elapsed_time
  from sys.dm_db_session_space_usage t1
 inner join sys.dm_exec_sessions as t3
    on t1.session_id = t3.session_id
 where (t1.internal_objects_alloc_page_count > 0 or
       t1.user_objects_alloc_page_count > 0 or
       t1.internal_objects_dealloc_page_count > 0 or
       t1.user_objects_dealloc_page_count > 0)
 order by t1.internal_objects_alloc_page_count desc

---如果SQL还在执行，可以查到消耗tempdp较大的SQL

select p.*, s.text
  from master.dbo.sysprocesses p
 cross apply sys.dm_exec_sql_text(p.sql_handle) s
 where spid = 53

---dbcc shrinkdatabase (tempdb, 'target percent')

--11.查看数据库所在机器的操作系统参数

exec master..xp_msver

--12.查看数据库启动的参数

exec sp_configure

--13.查看数据库启动时间

select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1

--14.查看数据库服务器名

select 'Server Name:'+ltrim(@@servername)

--15.查看数据库实例名

select 'Instance:'+ltrim(@@servicename)

--16.查看所有数据库用户登录信息

exec sp_helplogins

--17.查看所有数据库用户所属的角色信息

exec sp_helpsrvrolemember

--18.查看链接服务器

exec sp_helplinkedsrvlogin

--19.查看远端数据库用户登录信息

exec sp_helpremotelogin

--20.检查数据库中的所有对象的分配和机构完整性是否存在错误

dbcc checkdb

--21.查询文件组和文件

select
    df.[name],df.physical_name,df.[size],df.growth,
    f.[name][filegroup],f.is_default
from sys.database_files df join sys.filegroups f
on df.data_space_id = f.data_space_id

--26.查看数据库中所有表的条数

select  b.name as tablename ,
        a.rowcnt as datacount
from    sysindexes a ,
        sysobjects b
where   a.id = b.id
        and a.indid < 2
        and objectproperty(b.id, 'IsMSShipped') = 0

--27.得到最耗时的前10条T-SQL语句

;with maco as
(
    select top 10
        plan_handle,
        sum(total_worker_time) as total_worker_time ,
        sum(execution_count) as execution_count ,
        count(1) as sql_count
    from sys.dm_exec_query_stats group by plan_handle
    order by sum(total_worker_time) desc
)
select  t.text ,
        a.total_worker_time ,
        a.execution_count ,
        a.sql_count
from    maco a
        cross apply sys.dm_exec_sql_text(plan_handle) t

--28. 查看SQL Server的实际内存占用

select * from sys.sysperfinfo where counter_name like '%Memory%'

---查询TOP_SQL

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'执行次数'  
    ,creation_time N'语句编译时间'  
    ,last_execution_time N'上次执行时间'  
    ,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'执行语句'  
    ,qp.query_plan  
FROM  sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp  
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  total_elapsed_time / execution_count DESC;

---执行效率低的SQL

SELECT creation_time N'语句编译时间',
       last_execution_time N'上次执行时间',
       total_physical_reads N'物理读取总次数',
       total_logical_reads / execution_count N'每次逻辑读次数',
       total_logical_reads N'逻辑读取总次数',
       total_logical_writes N'逻辑写入总次数',
       execution_count 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'执行语句'
  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 total_elapsed_time / execution_count DESC;

---查询正在执行的SQL

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
    ,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

---

select r.session_id, r.status, r.start_time, r.command, s.text
  from sys.dm_exec_requests r
 cross apply sys.dm_exec_sql_text(r.sql_handle) s
 where r.status = 'running';

---查看物理文件位置

select database_id,
       name,
       physical_name AS CurrentLocation,
       state_desc,
       size
  from sys.master_files
 where database_id = db_id(N'necology20180625');

---sp_helpfile 
select * from sys.databases;
select * from sys.database_files;
select DATABASEPROPERTYEX('CJC20180625','status') ---ONLINE

select * from sys.databases
GO
select * from sys.database_files
GO 
select * from sys.filegroups 
GO 
select DATABASEPROPERTYEX('cjc','status')

use cjc
GO 
sp_spaceused 
GO 
sp_helpdb 
GO 
sp_helpdb cjc
GO

---分离和附加 ---分离数据库

https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/detach-a-database?view=sql-server-2017

---此示例将分离 cjc 数据库，同时将 skipchecks 设置为 true。

---select * from sys.database_files; 
EXEC sp_detach_db 'cjc', 'true';

https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/attach-a-database?view=sql-server-2017

---附加数据库 ---此示例附加 cjc数据库的文件并将该数据库重命名为 chen。

CREATE DATABASE chen
    ON (FILENAME = 'D:\oadata\cjc.mdf'),   
    (FILENAME = 'D:\oadata\cjc_0.ldf')   
    FOR ATTACH;

---查看当前session_id

SELECT @@SPID

---其他

select * from sys.dm_tran_session_transactions;
select * from sys.dm_exec_connections;
select * from sys.dm_exec_sql_text;
select * from sys.dm_tran_active_transactions;

---查询表行数和大小 ---1 创建临时表#tabName ---if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tabName') and xtype='u') ---drop table #tabName ---go

create table #tabName(
tabname varchar(100),
rowsNum varchar(100),
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused_size varchar(100)
)

---2 表信息插入到临时表

declare @name varchar(100)
declare cur cursor for
select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
    insert into #tabName
    exec sp_spaceused @name
    --print @name
 
    fetch next from cur into @name
end
close cur
deallocate cur

---3 查询临时表

select tabname as '表名',rowsNum as '表数据行数',reserved as '保留大小',data as '数据大小',index_size as '索引大小',unused_size as '未使用大小'
from #tabName
--where tabName not like 't%'
order by cast(rowsNum as int) desc

---事物提交和回滚 ---DML语句默认自动提交和Oracle不同

BEGIN TRAN  
update test0706 set a = cast('100' as text) where id=2;
COMMIT TRAN 
---ROLLBACK TRAN

---重命名

---select * into t_0709 from sysindexes;
---select * from t_0709;
exec sp_rename 't_0709','test_0709';
---select * from test_0709;

---外键约束 外键约束

select oSub.name    AS 子表名称,
       fk.name      AS 外键名称,
       SubCol.name  AS 子表列名,
       oMain.name   AS 主表名称,
       MainCol.name AS 主表列名
  from sys.foreign_keys fk
  JOIN sys.all_objects oSub
    ON (fk.parent_object_id = oSub.object_id)
  JOIN sys.all_objects oMain
    ON (fk.referenced_object_id = oMain.object_id)
  JOIN sys.foreign_key_columns fkCols
    ON (fk.object_id = fkCols.constraint_object_id)
  JOIN sys.columns SubCol
    ON (oSub.object_id = SubCol.object_id AND
       fkCols.parent_column_id = SubCol.column_id)
  JOIN sys.columns MainCol
    ON (oMain.object_id = MainCol.object_id AND
       fkCols.referenced_column_id = MainCol.column_id);

---SA修改密码 如果忘记了sa的登录密码，可以先用windows身份认证登录进去，然后新建查询，输入命令：

EXECUTE sp_password NULL,'输入新密码','sa'

---查询

select * from 数据库名.dbo.表名

###chenjuchao 20180724###