一、基礎(chǔ)命令
SELECT @@VERSION;
查看服務(wù)器部分特殊信息
select SERVERPROPERTY(N'edition') as Edition? ? --數(shù)據(jù)版本，如企業(yè)版、開發(fā)版等
? ? ,SERVERPROPERTY(N'collation') as Collation? --數(shù)據(jù)庫字符集
? ? ,SERVERPROPERTY(N'servername') as ServerName --服務(wù)名
? ? ,@@VERSION as Version? --數(shù)據(jù)庫版本號
? ? ,@@LANGUAGE AS Language? --數(shù)據(jù)庫使用的語言，如us_english等
SELECT GETDATE() AS CurrentDateTime;
sp_configure
sp_helplogins
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
SELECT * FROM sys.dm_tcp_listener_states;
SELECT COUNT(*) AS [Connection Count] FROM sys.dm_exec_connections;
Exec master.dbo.xp_fixeddrives
Exec sp_spaceused
查看數(shù)據(jù)庫服務(wù)器各數(shù)據(jù)庫日志文件的大小及利用率
DBCC SQLPERF(LOGSPACE)
查看當前占用 cpu 資源最高的會話和其中執(zhí)行的語句
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc
查看緩存中重用次數(shù)少，占用內(nèi)存大的查詢語句（當前緩存中未釋放的）
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes? desc
看BUFFER POOL中，都緩存了哪些表(當前數(shù)據(jù)庫)的數(shù)據(jù)
select OBJECT_NAME(object_id) 表名,COUNT(*) 頁數(shù),COUNT(*)*8/1024.0 Mb? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
from? sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
where? a.allocation_unit_id=b.allocation_unit_id?
? ? ? and b.container_id=c.hobt_id? ? ? ? ? ?
? ? ? and database_id=DB_ID()? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
group by OBJECT_NAME(object_id)? ? ? ? ? ? ? ? ? ? ? ? ?
order by 2 desc
EXEC sp_helprotect;
select * from sys.dm_os_process_memory
查詢當前數(shù)據(jù)庫緩存的所有數(shù)據(jù)頁面，哪些數(shù)據(jù)表，緩存的數(shù)據(jù)頁面數(shù)量
-- 查詢當前數(shù)據(jù)庫緩存的所有數(shù)據(jù)頁面，哪些數(shù)據(jù)表，緩存的數(shù)據(jù)頁面數(shù)量
-- 從這些信息可以看出，系統(tǒng)經(jīng)常要訪問的都是哪些表，有多大？
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*)
from sys.allocation_units a,
? ? sys.dm_os_buffer_descriptors b,
? ? sys.partitions p
where a.allocation_unit_id=b.allocation_unit_id
? ? and a.container_id=p.hobt_id
? ? and b.database_id=db_id()
group by p.object_id,p.index_id
order by buffer_pages desc
查詢緩存中具體的執(zhí)行計劃，及對應(yīng)的SQL
-- 查詢緩存中具體的執(zhí)行計劃，及對應(yīng)的SQL
-- 將此結(jié)果按照數(shù)據(jù)表或SQL進行統(tǒng)計，可以作為基線，調(diào)整索引時考慮
-- 查詢結(jié)果會很大，注意將結(jié)果集輸出到表或文件中
SELECT? usecounts ,
? ? ? ? refcounts ,
? ? ? ? size_in_bytes ,
? ? ? ? cacheobjtype ,
? ? ? ? objtype ,
? ? ? ? TEXT
FROM? ? sys.dm_exec_cached_plans cp
? ? ? ? CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO
SELECT p.class_desc, OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc, u.name AS user_name
FROM sys.database_permissions p
JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE u.name = ‘test’
SELECT * FROM sys.servers;
select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid?
from sys.server_principals u, sys.server_principals g, sys.server_role_members m?
where g.principal_id = m.role_principal_id?
and u.principal_id = m.member_principal_id?
order by 1, 2?
go
select 用戶名 = u.name,管理員權(quán)限 = g.name,是否在用 = u.is_disabled,MemberSID = u.sid?
from sys.server_principals u, sys.server_principals g, sys.server_role_members m?
where g.principal_id = m.role_principal_id?
and u.principal_id = m.member_principal_id?
and g.name = 'sysadmin'
order by 1, 2
go
select name from sysobjects where xtype='u' order by name
Select Name FROM Master..SysDatabases orDER BY Name
SP_HELPSRVROLE
SP_HELPSRVROLEMEMBER 服務(wù)器角色
SP_HELPSRVROLE 服務(wù)器角色
SP_HELPROLE
SP_HELPROLEMEMBER 數(shù)據(jù)庫角色
SP_HELPROLE 數(shù)據(jù)庫角色
SP_HELPUSER
SP_HELPUSER 數(shù)據(jù)庫用戶名
select @@connections //返回 SQL Server 自上次啟動以來嘗試的連接數(shù)，無論連接是成功還是失敗
select @@max_connections
//返回 SQL Server 實例允許同時進行的最大用戶連接數(shù)。返回的數(shù)值不一定是當前配置的數(shù)值
SELECT value_in_use
FROM sys.configurations c
WHERE c.name = 'user connections'; #0表示無限制
exec sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
exec sp_configure 'user connections', 300
RECONFIGURE WITH OVERRIDE
select @@lock_timeout //返回當前會話的當前鎖定超時設(shè)置（毫秒）。
select loginame,count(1) as Nums
from sys.sysprocesses
group by loginame
order by 2 desc
select spid,ecid,status,loginame,hostname,cmd,request_id
from sys.sysprocesses where loginame='' and hostname=''
SELECT CURRENT_USER AS [Current User], SESSION_USER AS [Session User];
SELECT * FROM sys.dm_exec_requests;
SELECT
? ? DB_NAME(database_id) AS DatabaseName,
? ? SUM(size/128.0) AS SizeInMB,
? ? SUM(size/128.0)/1024 AS SizeInGB
FROM
? ? sys.master_files
GROUP BY
? ? database_id
ORDER BY
? ? SizeInMB DESC;
SELECT sys.databases.name AS [Database Name],
? ? CAST(SUM(size * 8 / 1024.0) AS DECIMAL(10,2)) AS [Size (MB)]
FROM sys.master_files
INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id
WHERE sys.databases.name = 'master'
GROUP BY sys.databases.name;
#也可以用EXEC sp_spaceused @updateusage = N'TRUE';
SELECT sys.databases.name AS [Database Name],
? ? CAST(size * 8 / 1024.0 AS DECIMAL(10,2)) AS [Log File Size (MB)]
FROM sys.master_files
INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id
WHERE sys.databases.name = 'master'
? ? AND sys.master_files.type = 1;
SELECT TABLE_NAME AS [Table/View Name], TABLE_TYPE AS [Type]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW');
sp_help 'test';
一次性清除數(shù)據(jù)庫所有表的數(shù)據(jù)（高危操作，謹慎）
CREATE PROCEDURE sp_DeleteAllData?
AS?
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'?
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'?
EXEC sp_MSForEachTable 'DELETE FROM ?'?
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'?
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'?
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'?
GO
BACKUP DATABASE test TO DISK = 'C:\backup\MyDatabase.bak';
RESTORE DATABASE MyDatabase FROM DISK = 'C:\backup\MyDatabase.bak';
BACKUP DATABASE test
TO DISK = 'C:\Backup\MyDatabase.bak'
WITH FORMAT, MEDIANAME = 'MyDatabase_Full', NAME = 'Full Backup';
BACKUP DATABASE test
TO DISK = 'C:\Backup\MyDatabase_diff.bak'
WITH DIFFERENTIAL, FORMAT, MEDIANAME = 'MyDatabase_Diff', NAME = 'Differential Backup';
BACKUP LOG test
TO DISK = 'C:\Backup\MyDatabase_log.trn'
WITH NOFORMAT, NOINIT, NAME = N'MyDatabase_LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
RESTORE DATABASE test
FROM DISK = 'C:\Backup\MyDatabase.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;
#創(chuàng)建賬戶
CREATE LOGIN test WITH PASSWORD = '123123';
#創(chuàng)建數(shù)據(jù)庫用戶并映射到登錄名
CREATE USER test FOR LOGIN test;
ALTER ROLE db_datareader ADD MEMBER test; -- 給予讀權(quán)限
ALTER ROLE db_datawriter ADD MEMBER test; -- 給予寫權(quán)限
SELECT creation_time? N'語句編譯時間'?
? ? ? ? ,last_execution_time? N'上次執(zhí)行時間'?
? ? ? ? ,total_physical_reads N'物理讀取總次數(shù)'?
? ? ? ? ,total_logical_reads/execution_count N'每次邏輯讀次數(shù)'?
? ? ? ? ,total_logical_reads? N'邏輯讀取總次數(shù)'?
? ? ? ? ,total_logical_writes N'邏輯寫入總次數(shù)'?
? ? ? ? ,execution_count? N'執(zhí)行次數(shù)'?
? ? ? ? ,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'執(zhí)行語句'?
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;
SELECT
? ? [T1].[job_id]
? ? ,[T1].[name] AS [job_name]
? ? ,[T2].[run_status]
? ? ,[T2].[run_date]
? ? ,[T2].[run_time]
? ? ,[dbo].[agent_datetime]([T2].[run_date], [T2].[run_time]) AS [run_datetime]
? ? ,[T2].[run_duration]
? ? ,DATEDIFF(SECOND, '1900-01-01', DATEADD(SECOND, 31, [dbo].[agent_datetime](19000101, [run_duration]))) AS [run_duration_s]
FROM
? ? [dbo].[sysjobs] AS T1
? ? INNER JOIN [dbo].[sysjobhistory] AS T2
? ? ? ? ON [T2].[job_id] = [T1].[job_id]
WHERE
? ? [T1].[enabled] = 1
? ? AND [T2].[step_id] = 0
? ? AND [T2].[run_duration] >= 1
? ? and [T1].[name]='PIMS_CreatePaperCraftParameterAnalysisData'
ORDER BY
? ? [T2].[job_id] ASC
? ? ,[T2].[run_date] ASC
GO
DBCC FREESYSTEMCACHE('ALL');
--查詢表死鎖信息
select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_locks
where resource_type = 'OBJECT'
dbcc opentran
--查看死鎖的詳細信息、執(zhí)行的sql語句
exec sp_who2 53
--exec sp_who 53
DBCC inputbuffer (53)
--解除死鎖
kill 53
查詢SQL Server根據(jù)CPU消耗列出前5個最差性能的查詢
-- Worst performing CPU bound queries
SELECT TOP 5
? ? st.text,
? ? qp.query_plan,
? ? qs.*
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_worker_time DESC
GO
查詢數(shù)據(jù)庫中各數(shù)據(jù)表大小
-- =============================================
-- 描? 述：更新查詢數(shù)據(jù)庫中各表的大小，結(jié)果存儲到數(shù)據(jù)表中
-- =============================================
? ? --查詢是否存在結(jié)果存儲表
? ? IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'temp_tableSpaceInfo') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
? ? BEGIN
? ? ? ? --不存在則創(chuàng)建
? ? ? ? CREATE TABLE temp_tableSpaceInfo
? ? ? ? (name NVARCHAR(128),
? ? ? ? rows char(11),
? ? ? ? reserved VARCHAR(18),
? ? ? ? data VARCHAR(18),
? ? ? ? index_size VARCHAR(18),
? ? ? ? unused VARCHAR(18))
? ? END
? ? --清空數(shù)據(jù)表
? ? DELETE FROM temp_tableSpaceInfo
? ? --定義臨時變量在遍歷時存儲表名稱
? ? DECLARE @tablename VARCHAR(255)
? ? --使用游標讀取數(shù)據(jù)庫內(nèi)所有表表名
? ? DECLARE table_list_cursor CURSOR FOR
? ? SELECT name FROM sysobjects
? ? WHERE OBJECTPROPERTY(id, N'IsTable') = 1 AND name NOT LIKE N'#%%' ORDER BY name
? ? --打開游標
? ? OPEN table_list_cursor
? ? --讀取第一條數(shù)據(jù)
? ? FETCH NEXT FROM table_list_cursor INTO @tablename
? ? --遍歷查詢到的表名
? ? WHILE @@FETCH_STATUS = 0
? ? BEGIN
? ? ? ? --檢查當前表是否為用戶表
? ? ? ? IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
? ? ? ? BEGIN
? ? ? ? ? ? --當前表則讀取其信息插入到表格中
? ? ? ? ? ? EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo EXEC sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablename
? ? ? ? END
? ? ? ? --讀取下一條數(shù)據(jù)
? ? ? ? FETCH NEXT FROM table_list_cursor INTO @tablename
? ? END
? ? --釋放游標
? ? CLOSE table_list_cursor
? ? DEALLOCATE table_list_cursor
? ? SELECT *,replace(reserved,'KB','')/1024 數(shù)據(jù)表大小M FROM temp_tableSpaceInfo order by replace(reserved,'KB','')/1024 desc
? ? drop table temp_tableSpaceInfo
-- Worst performing I/O bound queries
SELECT TOP 5
? ? st.text,
? ? qp.query_plan,
? ? qs.*
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_logical_reads DESC
GO
DBCC ShrinkFile(‘數(shù)據(jù)庫名’,? targetsize);? ? ? ? ? ? /* 收縮數(shù)據(jù)庫文件 */
DBCC ShrinkFile(‘數(shù)據(jù)庫名_log’,? targetsize);? ? ? ? /* 收縮日志文件 */
Targetsize：單位為兆，必須為整數(shù)，DBCC SHRINKFILE 嘗試將文件收縮到指定大小。
DBCC SHRINKFILE 不會將文件收縮到小于“實際使用的空間”大小，例如“分配空間”為10M，“實際使用空間”為6M，當制定targetsize為1時，則將該文件收縮到6M，不會將文件收縮到1M。
--收縮數(shù)據(jù)庫
DBCC SHRINKDATABASE(數(shù)據(jù)庫名，百分比)
百分比：即“收縮后文件中的最大可用空間”，取值范圍“大于等于0， 小于100%”，實際使用中設(shè)為0即可。
查詢數(shù)據(jù)庫表字段各項屬性信息，便于直接復(fù)制導(dǎo)出excel表
SELECT?
? ? 表名? ? ? = Case When A.colorder=1 Then D.name Else '' End,?
? ? 表說明? ? = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,?
? ? 字段序號? = A.colorder,?
? ? 字段名? ? = A.name,?
? ? 字段說明? = isnull(G.[value],''),?
? ? 標識? ? ? = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,?
? ? 主鍵? ? ? = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (?
? ? ? ? ? ? ? ? ? ? ? SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,?
? ? 類型? ? ? = B.name,?
? ? 占用字節(jié)數(shù) = A.Length,?
? ? 長度? ? ? = COLUMNPROPERTY(A.id,A.name,'PRECISION'),?
? ? 小數(shù)位數(shù)? = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),?
? ? 允許空? ? = Case When A.isnullable=1 Then '√'Else '' End,?
? ? 默認值? ? = isnull(E.Text,'')?
FROM?
? ? syscolumns A?
Left Join?
? ? systypes B?
On?
? ? A.xusertype=B.xusertype?
Inner Join?
? ? sysobjects D?
On?
? ? A.id=D.id? and D.xtype='U' and? D.name<>'dtproperties'?
Left Join?
? ? syscomments E?
on?
? ? A.cdefault=E.id?
Left Join?
sys.extended_properties? G?
on?
? ? A.id=G.major_id and A.colid=G.minor_id?
Left Join?
sys.extended_properties F?
On?
? ? D.id=F.major_id and F.minor_id=0?
? ? --where d.name='OrderInfo'? ? --如果只查詢指定表,加上此條件?
Order By?
? ? A.id,A.colorder
CREATE PROCEDURE [dbo].ClearMemory?
AS?
BEGIN
? ? --清除所有緩存
? ? DBCC DROPCLEANBUFFERS
? ? --打開高級配置
? ? exec sp_configure 'show advanced options', 1
? ? --設(shè)置最大內(nèi)存值，清除現(xiàn)有緩存空間
? ? exec sp_configure 'max server memory', 25600
? ? EXEC ('RECONFIGURE')
? ? --設(shè)置等待時間
? ? WAITFOR DELAY '00:00:01'
? ? --重新設(shè)置最大內(nèi)存值
? ? EXEC? sp_configure 'max server memory',40960
? ? EXEC ('RECONFIGURE')
? ? --關(guān)閉高級配置
? ? exec sp_configure 'show advanced options',0
END
GO
USE [master]
GO
--待確認賬號密碼
CREATE LOGIN [NDIT] WITH PASSWORD=N'1', DEFAULT_DATABASE=[PIMS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE PIMS
go
CREATE USER [NDIT] FOR LOGIN [NDIT]
GO
--大權(quán)限， 如果是指定的部分表，不執(zhí)行這個，如果是所有內(nèi)容都可以讀，用此腳本
--EXEC sp_addrolemember N'db_datareader', N'NDIT'
--GO
--指定特定表名賦予新增/更新/查詢
DECLARE @Sql NVARCHAR(max)
SET @Sql=''
--table
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.tables AS a WHERE name IN ('Tab1','Tab2');
--view
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.views AS a WHERE name IN ('view1','view2');
--procedure
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.procedures AS a WHERE name IN ('proc1','proc2');
PRINT @Sql
EXEC(@Sql)
go
--禁用登陸帳戶
alter login NDIT disable
--啟用登陸帳戶
alter login NDIT enable
--登陸帳戶改名
alter login NDIT with name=dba_tom
--登陸帳戶改密碼：
alter login NDIT with password='aabb@ccdd'
--數(shù)據(jù)庫用戶改名：
alter user NDIT with name=dba_tom
--更改數(shù)據(jù)庫用戶 defult_schema：
alter user NDIT with default_schema=sales
--刪除數(shù)據(jù)庫用戶：
drop user NDIT
--刪除 SQL Server登陸帳戶：
drop login NDIT
