蒐羅生涯中遇見的各種 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 Login 系列
列出所有資料庫的 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)/1000 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
改變資料庫 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
查詢資料庫實體檔案的大小與未使用大小 (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