蒐羅生涯中遇見的各種 SQL Server 快捷專業的指令 ⚡
監督系統
sp_who / sp_who2
sp_who 基本查詢，主要用於判別 hostname, dbname 等資訊。
sp_who2 可以查詢到 SQL 連線 CPUTime / DIskIO / ProgramName 等資訊
令 stored procedure 可以被條件查詢的 fast script
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT *
FROM #sp_who2
WHERE DBName != 'master'
ORDER BY DBName ASC
DROP TABLE #sp_who2替代 sp_who2 的 T-sql
SELECT spid,
sp.[status],
loginame [Login],
hostname,
blocked BlkBy,
sd.name DBName,
cmd Command,
cpu CPUTime,
physical_io DiskIO,
last_batch LastBatch,
[program_name] ProgramName
FROM master.dbo.sysprocesses sp
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
ORDER BY spid 取得資料庫伺服器硬體資訊
搭配本機資料庫伺服器群組查詢更為方便 😁
SELECT
cpu_count,
hyperthread_ratio,
socket_count,
physical_memory_kb / 1024 / 1024 as 'RAM (GB)'
FROM sys.dm_os_sys_infoSQL Server 2008 (含) 以前的版本不支援 socket_count 以及 physical_memory_kb，必須使用替代方式：
SELECT
cpu_count,
hyperthread_ratio,
0 'socket_count',
physical_memory_in_bytes / 1024 / 1024 / 1024 as 'RAM (GB)'
FROM sys.dm_os_sys_info確認所有的 SQLServerAgent Service 有正常服務
EXEC xp_servicecontrol 'querystate', 'SQLServerAgent'其他確認方式，使用 sys.dm_server_services
SELECT servicename, startup_type_desc, status_desc, service_account FROM sys.dm_server_servicesLogin 系列
列出所有資料庫的 db_owner
DECLARE @Table TABLE
(
ServerName SYSNAME,
DbName SYSNAME,
UserName SYSNAME,
TypeOfLogIn VARCHAR(100),
PermissionLevel VARCHAR(100),
TypeOfRole VARCHAR(100)
)
INSERT @Table
EXEC sp_MSforeachdb '
use [?]
BEGIN
SELECT
ServerName=@@servername,
dbname=db_name(db_id()),
p.name AS UserName,
p.type_desc AS TypeOfLogin,
pp.name AS PermissionLevel,
pp.type_desc AS TypeOfRole
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
WHERE pp.name = ''db_owner'' and p.name != ''dbo''
END '
SELECT * FROM @Table
Create Login / Drop Login
CRETAE LOGIN loginName WITH PASSWORD = 'password';
DROP LOGIN loginName;sp_helplogins
sp_helplogins 'loginName';
sp_helpsrvrole
列出 Server Level Roles 的所有角色資訊
sp_helpsrvrolesp_helpsrvrolemember
列出所有 Server Level 具有特定 Role 的所有 Logins
sp_helpsrvrolemember 'sysadmin'sp_helpuser
列出 Database User 的 Role, LoginName, Default Schema
sp_helpusersys.syslogins
列出 Login Create Date / isEnable 等相關資訊
select *, loginname from sys.syslogins order by accdate desc客製應用 - 尋找伺服器的 登入 以及計算權限等級
select loginname,
CASE
WHEN sysadmin = 1 then 8
WHEN securityadmin = 1 then 8
ELSE sysadmin + securityadmin + serveradmin + setupadmin + processadmin + diskadmin + dbcreator + bulkadmin
END AS SENSITIVE, /* 權限等級；越高權限越大 */
createdate,
sysadmin,
securityadmin,
serveradmin,
setupadmin,
processadmin,
diskadmin,
dbcreator,
bulkadmin
from sys.syslogins
where isntname = 0 /* 排除本機帳號*/
and (isntgroup = 1 or isntgroup = 0) /* 群組*/
order by createdate desc
/* 無法找到最近一次登入時間 */檢視連線中的 Session 所使用的通訊協定及驗證方法
SELECT client_net_address, net_transport, auth_scheme, encrypt_option
FROM sys.dm_exec_connectionsDatabase
sp_helpdb
查看資料庫伺服器中的所有資料庫，包含資料庫大小、擁有者、建立日期、相容性層級等資訊，無法顯示離線的資料庫。
EXEC sp_helpdb
列出資料庫伺服器中的資料庫資訊 (sys.databases)
SELECT
d.name, -- 資料庫名稱
f.physical_name, --實體檔案路徑
d.state_desc, --資料庫狀態 線上、離線、復原模式
(cast(f.size AS FLOAT)*8)/1024 AS fileSize, --實體檔案大小 MB
d.recovery_model_desc, --復原模式 完整、批次、簡單
d.compatibility_level, --相容性等級
d.create_date , f.differential_base_time --建立日期與檔案異動日期
FROM sys.master_files as f
JOIN sys.databases AS d ON f.database_id = d.database_id
簡化查詢版本，但查詢結果不容易閱讀
sp_msforeachdb 'use ?;select DB_NAME();exec sp_helpfile'改變資料庫 Owner (sp_changedbowner)
處理錯誤訊息 「出現錯誤｢無法改變使用者’dbo’｣
use DatabaseName
EXEC sp_changedbowner 'sa'取得 Exec T-SQL 中的資料 / 取用 T-SQL 的結果
取得 Table 的相關資訊 (欄位名稱)
use DatabaseName
exec sp_columns "tableName"客製應用 - 取得資料庫所有使用的欄位型別資訊
select tb.name as TableName, t.name as ColumnType, c.* from sys.all_columns as c
join sys.types as t
on c.system_type_id = t.system_type_id
join (select name, OBJECT_ID from sys.tables) as tb
on tb.object_id = c.object_id
where t.name != 'sysname'查詢資料庫、資料表使用的硬碟空間
方式一
EXEC sp_spaceused
一次打十個💢，缺點不容易閱讀
EXECUTE sp_MSforeachdb 'EXECUTE sp_spaceused'⭐方式二、取得資料庫所有資料表的硬碟使用空間資訊
USE DatabaseName
Go
SELECT
t.NAME AS TableName,
--s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 /1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8 /1024 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 /1024 AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
查詢資料庫中每個資料表之列數與資料表大小(容量)資訊。Get size of all tables in database
查詢 Single Database, all Tables, Rows Count
SELECT
t.NAME AS TableName,
p.rows AS RowCounts
FROM
sys.tables t
INNER JOIN
sys.partitions p ON t.object_id = p.OBJECT_ID
WHERE
t.is_ms_shipped = 0 AND p.index_id < 2
ORDER BY
TableName;查詢資料庫實體檔案的大小與未使用大小 (SpaceUsed)
use DatabaseName
SELECT DB_NAME() AS DbName,
name AS FileName,
type_desc,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
所有的資料庫與其所有的資料表
使用 sp_msforeachdb 顯示資料庫伺服器下所有的資料庫及其資料表，尚需要擴充下列內容：
- Database : File Size
- Table : Rows, Size
- Database Users
SET NOCOUNT ON
DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname)
DECLARE
@SearchDb nvarchar(200)
,@SearchSchema nvarchar(200)
,@SearchTable nvarchar(200)
,@SQL nvarchar(4000)
SET @SearchDb='%'
SET @SearchSchema='%'
SET @SearchTable='%'
SET @SQL='SELECT ''?'' AS DbName, s.name AS SchemaName, t.name AS TableName
FROM [?].sys.tables t INNER JOIN [?].sys.schemas s ON t.schema_id=s.schema_id WHERE ''?'' LIKE '''
+ @SearchDb + ''' AND s.name LIKE ''' + @SearchSchema + ''' AND t.name LIKE ''' + @SearchTable + ''''
INSERT INTO @AllTables (DbName, SchemaName, TableName)
EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT * FROM @AllTables
WHERE DBNAME NOT IN ('tempdb')
ORDER BY DbName, SchemaName, TableNameIndexes
查詢資料庫中的所有索引
顯示單一資料庫的所有索引，包含名稱及碎片化程度以及分頁數量
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count,
DDIPS.index_type_desc,
P.rows
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
INNER JOIN
sys.partitions P ON I.object_id = p.OBJECT_ID
AND I.index_id = p.index_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()此外也可以用於觀察建立主鍵的資料表變化 (從 Heap 變成 Clustered Index)
確認索引的使用情形
如果一片空白，可能表示資料庫從未使用！
SELECT DB_NAME(database_id) ,i.name, s.* 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
WHERE database_id = db_id()查詢推薦建立的遺失索引
SELECT * FROM sys.dm_db_missing_index_details IDS
INNER JOIN
sys.dm_db_missing_index_groups IG
ON IDS.index_handle = IG.index_handle
INNER JOIN
sys.dm_db_missing_index_group_stats IGS
ON IG.index_group_handle = IGS.group_handleBackup Device
列出最近的備份紀錄
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.backup_finish_date desc
RESTORE HEADERONLY
檢查備份裝置 (Backup Device) / DISK 中備份組 (Backup Set) 的詳細資訊
RESTORE HEADERONLY FROM 'DEVICENAME'
RESTORE HEADERONLY FROM DISK = N''RESTORE FILELISTONLY
檢查備份裝置 / DISK 中還原檔的詳細資訊 (mdf, ldf, ndf)
RESTORE FILELISTONLY FROM 'DEVICENAME'
RESTORE FILELISTONLY FROM DISK = N'' Jobs
查詢 SQL Server Agent 所有的作業
SELECT
job.job_id,
notify_level_email,
name,
enabled,
description,
step_name,
command,
server,
database_name
FROM
msdb.dbo.sysjobs job
INNER JOIN
msdb.dbo.sysjobsteps steps
ON
job.job_id = steps.job_id參考資料
Script to retrieve SQL Server database backup history and no backups