SQL Server 閃電般快速查詢指南 Quick Query⚡


  1. 監督系統
    1. sp_who / sp_who2
  2. Login 系列
    1. 列出所有資料庫的 db_owner
    2. Create Login / Drop Login
    3. sp_helplogins
    4. sp_helpsrvrole
    5. sp_helpsrvrolemember
    6. sp_helpuser
    7. sys.syslogins
      1. 客製應用 - 尋找伺服器的 登入 以及計算權限等級
    8. 檢視連線中的 Session 所使用的通訊協定及驗證方法
  3. Database
    1. sp_helpdb
    2. 列出資料庫伺服器中的資料庫資訊 (sys.databases)
    3. 改變資料庫 Owner (sp_changedbowner)
    4. 取得 Exec T-SQL 中的資料 / 取用 T-SQL 的結果
    5. 取得 Table 的相關資訊 (欄位名稱)
      1. 客製應用 - 取得資料庫所有使用的欄位型別資訊
    6. 查詢資料庫、資料表使用的硬碟空間
    7. 查詢資料庫實體檔案的大小與未使用大小 (SpaceUsed)
    8. 所有的資料庫與其所有的資料表
  4. Indexes
    1. 查詢資料庫中的所有索引
    2. 確認索引的使用情形
    3. 查詢推薦建立的遺失索引
  5. Backup Device
    1. 列出最近的備份紀錄
    2. RESTORE HEADERONLY
    3. RESTORE FILELISTONLY
  6. Jobs
    1. 查詢 SQL Server Agent 所有的作業
  7. 參考資料

蒐羅生涯中遇見的各種 SQL Server 快捷專業的指令 ⚡

SQL Server Logo

監督系統

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 

SQL Matters

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_helpsrvrole

sp_helpsrvrolemember

列出所有 Server Level 具有特定 Role 的所有 Logins

sp_helpsrvrolemember 'sysadmin'

sp_helpuser

列出 Database User 的 Role, LoginName, Default Schema

sp_helpuser

sys.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_connections

Database

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 的結果

取得 Execute 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, TableName

Indexes

查詢資料庫中的所有索引

顯示單一資料庫的所有索引,包含名稱及碎片化程度以及分頁數量

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_handle

Backup 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