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

2020-12-23

蒐羅生涯中遇見的各種 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 

SQL Matters

取得資料庫伺服器硬體資訊

搭配本機資料庫伺服器群組查詢更為方便 😁

SELECT
	cpu_count, 
	hyperthread_ratio,
	socket_count,
	physical_memory_kb / 1024 / 1024 as 'RAM (GB)'
FROM sys.dm_os_sys_info

SQL 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_services

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

取得 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

查詢 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, TableName

取得所有資料庫的所有資料表的欄位與型別資訊

-- Create a temporary table to store the results
IF OBJECT_ID('tempdb..#AllDatabasesTableCounts') IS NOT NULL
    DROP TABLE #AllDatabasesTableCounts;

CREATE TABLE #AllDatabasesTableCounts
(
    SERVERNAME NVARCHAR(128),
    DATABASE_NAME NVARCHAR(128),
    TABLE_SCHEMA NVARCHAR(128),
    TABLE_NAME NVARCHAR(128),
    RowCounts BIGINT,
    COLUMN_NAME NVARCHAR(128),
    DATA_TYPE NVARCHAR(128),
    CURRENT_DATETIME DATETIME
);

-- Declare variables for dynamic SQL
DECLARE @SQL AS NVARCHAR(MAX);
DECLARE @DatabaseName AS NVARCHAR(128);

-- Cursor to go through each database
DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM sys.databases 
WHERE database_id > 4 AND state = 0 -- User databases and online only
AND name not in ('DBA') -- exclude database name

-- Open cursor and fetch each database into the variable
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DatabaseName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- Dynamic SQL for each database
    SET @SQL = 'USE [' + @DatabaseName + ']; 
    WITH table_count AS 
    (
        SELECT 
            s.name AS SchemaName,
            t.name AS TableName,
            SUM(p.rows) AS RowCounts
        FROM 
            sys.tables t
        INNER JOIN 
            sys.partitions p ON t.object_id = p.object_id
        INNER JOIN 
            sys.indexes i ON p.object_id = p.object_id AND i.index_id = p.index_id
        INNER JOIN 
            sys.schemas s ON t.schema_id = s.schema_id
        WHERE 
            i.type <= 1 -- Heap or clustered index
        GROUP BY 
            s.name, t.name
    )
    INSERT INTO tempdb..#AllDatabasesTableCounts
    SELECT 
        @@SERVERNAME AS SERVERNAME,
        DB_NAME() AS DATABASE_NAME,
        c.TABLE_SCHEMA,
        c.TABLE_NAME, 
        tc.RowCounts,
        c.COLUMN_NAME,
        c.DATA_TYPE,
        GETDATE() AS CURRENT_DATETIME
    FROM 
        INFORMATION_SCHEMA.COLUMNS c
    INNER JOIN 
        table_count tc 
        ON c.TABLE_NAME = tc.TableName AND c.TABLE_SCHEMA = tc.SchemaName
    ORDER BY 
        c.TABLE_NAME, c.ORDINAL_POSITION;';

    -- Execute the dynamic SQL
    EXEC sp_executesql @SQL;

    -- Fetch the next database
    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Close and deallocate the cursor
CLOSE db_cursor;
DEALLOCATE db_cursor;

-- Select results to verify
SELECT * FROM #AllDatabasesTableCounts;

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