システム ストアド プロシージャ（SP_XXX）を使用して、インスタンスやデータベースの情報を取得します。
サーバ構成オプションの取得
sp_configureで構成オプションの情報を取得できます。
1> sp_configure
2> go
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
allow polybase export 0 1 0 0
allow updates 0 1 0 0
backup checksum default 0 1 0 0
clr enabled 0 1 0 0
contained database authentication 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 3 3
external scripts enabled 0 1 0 0
filestream access level 0 2 0 0
hadoop connectivity 0 7 0 0
max text repl size (B) -1 2147483647 65536 65536
nested triggers 0 1 1 1
polybase network encryption 0 1 1 1
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote data archive 0 1 0 0
remote login timeout (s) 0 2147483647 10 10
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
server trigger recursion 0 1 1 1
show advanced options 0 1 0 0
user instances enabled 0 1 1 1
user options 0 32767 0 0
構成オプションは変更しただけではconfig_valueだけが変更され、run_valueは変更されません。RECONFIGURE または RECONFIGURE WITH OVERRIDEで更新する必要があります。
1> sp_configure 'show advanced option'
2> go
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
show advanced options 0 1 0 0
1> sp_configure 'show advanced option', '1'
2> go
構成オプション 'show advanced options' が 0 から 1 に変更されました。RECONFIGURE ステートメントを実行してインストールし てください。
1> sp_configure 'show advanced option'
2> go
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
show advanced options 0 1 1 0
1> reconfigure
2> go
1> sp_configure 'show advanced option'
2> go
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
show advanced options 0 1 1 1
1>
詳細構成オプション情報の表示するには、ShowAdvancedOptions構成オプションを 1 にすることで表示可能です。
EXEC sp_configure 'show advanced option', '1';で実行後、RECONFIGUREを実施し、sp_configureにて表示します。
1> USE master;
2> GO
データベース コンテキストが 'master' に変更されました。
1> EXEC sp_configure 'show advanced option', '1';
2> go
構成オプション 'show advanced options' が 0 から 1 に変更されました。RECONFIGURE ステートメントを実行してインストールし てください。
1> RECONFIGURE;
2> go
1> sp_configure
2> go
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
access check cache bucket count 0 65536 0 0
access check cache quota 0 2147483647 0 0
Ad Hoc Distributed Queries 0 1 0 0
affinity I/O mask -2147483648 2147483647 0 0
affinity mask -2147483648 2147483647 0 0
affinity64 I/O mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
Agent XPs 0 1 0 0
allow polybase export 0 1 0 0
allow updates 0 1 0 0
automatic soft-NUMA disabled 0 1 0 0
backup checksum default 0 1 0 0
blocked process threshold (s) 0 86400 0 0
c2 audit mode 0 1 0 0
clr enabled 0 1 0 0
clr strict security 0 1 1 1
contained database authentication 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 0 0
default full-text language 0 2147483647 1041 1041
default language 0 9999 3 3
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
external scripts enabled 0 1 0 0
filestream access level 0 2 0 0
fill factor (%) 0 100 0 0
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
hadoop connectivity 0 7 0 0
index create memory (KB) 704 2147483647 0 0
in-doubt xact resolution 0 2 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32767 0 0
max full-text crawl range 0 256 4 4
max server memory (MB) 128 2147483647 2147483647 2147483647
max text repl size (B) -1 2147483647 65536 65536
max worker threads 128 65535 0 0
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 16
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 0 0
open objects 0 2147483647 0 0
optimize for ad hoc workloads 0 1 0 0
PH timeout (s) 1 3600 60 60
polybase network encryption 0 1 1 1
precompute rank 0 1 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote data archive 0 1 0 0
remote login timeout (s) 0 2147483647 10 10
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 0 0
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
transform noise words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user instance timeout 5 65535 60 60
user instances enabled 0 1 1 1
user options 0 32767 0 0
xp_cmdshell 0 1 0 0
データベースの一覧
sp_databasesでインスタンス内に存在しているデータベースの一覧を取得します。
1> sp_databases
2> go
DATABASE_NAME DATABASE_SIZE REMARKS
----------------------------------- ------------- ------------------
AdventureWorks2017 344064 NULL
master 7296 NULL
model 16384 NULL
msdb 21760 NULL
tempdb 16384 NULL
サーバーの基本情報
sp_server_infoでサーバの基本情報を取得します。
SQL Serverのバージョン（DBMS_VER）、文字セットの順序（COLLATION_SEQ）などが確認できます。
1> sp_server_info
2> go
attribute_id attribute_name attribute_value
------------ ------------------------------------------------------------ ------------------------------------------------------------
1 DBMS_NAME Microsoft SQL Server
2 DBMS_VER Microsoft SQL Server 2017 - 14.0.1000.169
10 OWNER_TERM owner
11 TABLE_TERM table
12 MAX_OWNER_NAME_LENGTH 128
13 TABLE_LENGTH 128
14 MAX_QUAL_LENGTH 128
15 COLUMN_LENGTH 128
16 IDENTIFIER_CASE MIXED
17 TX_ISOLATION 2
18 COLLATION_SEQ charset=cp932 collation=Japanese_CI_AS
19 SAVEPOINT_SUPPORT Y
20 MULTI_RESULT_SETS Y
22 ACCESSIBLE_TABLES Y
100 USERID_LENGTH 128
101 QUALIFIER_TERM database
102 NAMED_TRANSACTIONS Y
103 SPROC_AS_LANGUAGE Y
104 ACCESSIBLE_SPROC Y
105 MAX_INDEX_COLS 16
106 RENAME_TABLE Y
107 RENAME_COLUMN Y
108 DROP_COLUMN Y
109 INCREASE_COLUMN_LENGTH Y
110 DDL_IN_TRANSACTION Y
111 DESCENDING_INDEXES Y
112 SP_RENAME Y
113 REMOTE_SPROC Y
500 SYS_SPROC_VERSION 14.00.1000
データベースに関する情報を表示
sp_helpdbにてデータベースに関する情報を表示します。
データベースで現在設定されているデータベース オプションを含むデータベースの情報が表示されます。
1> sp_helpdb
2> go
name db_size owner dbid created status compatibility_level
--------------------------------- ------------- --------------------------------------------- ------ ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------
AdventureWorks2017 336.00 MB sa 5 05 29 2018 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=869, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoClose, IsAnsiNullsEnabled, IsAnsiPaddingEnabled, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsNullConcat, IsQuotedIdentifiersEnabled 140
master 7.13 MB sa 1 04 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=869, Collation=Japanese_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 140
model 16.00 MB sa 3 04 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=869, Collation=Japanese_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 140
msdb 21.25 MB sa 4 08 22 2017 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=869, Collation=Japanese_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 140
tempdb 80.00 MB sa 2 02 13 2019 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=869, Collation=Japanese_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 140
また、データベースを指定することでデータベースのファイルの割り当てに関する情報も表示されます
1> sp_helpdb 'AdventureWorks2017'
2> go
name db_size owner dbid created status compatibility_level
----------------------- ------------- --------------- ------ ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------
AdventureWorks2017 336.00 MB sa 5 05 29 2018 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=869, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoClose, IsAnsiNullsEnabled, IsAnsiPaddingEnabled, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsNullConcat, IsQuotedIdentifiersEnabled 140
name fileid filename filegroup size maxsize growth usage
--------------------------------- ------ ---------------------------------------------------------- ------------------ ------------------ ------------------ ------------------ ---------
AdventureWorks2017 1 D:\mssql\SQLEXPRESS\AdventureWorks2017.mdf PRIMARY 270336 KB Unlimited 65536 KB data only
AdventureWorks2017_log 2 D:\mssql\SQLEXPRESS\AdventureWorks2017_log.ldf NULL 73728 KB 2147483648 KB 65536 KB log only
データベースファイルに関する情報の取得
sp_helpfileにて、現在のデータベースに関連付けられたファイルの物理名と属性を取得します。
1> use AdventureWorks2017
2> go
データベース コンテキストが 'AdventureWorks2017' に変更されました。
1> sp_helpfile;
2> go
name fileid filename filegroup size maxsize growth usage
-------------------------------------------- ------ ------------------------------------------------------------ ------------------ ------------------ ------------------ ------------------ ---------
AdventureWorks2017 1 D:\mssql\SQLEXPRESS\AdventureWorks2017.mdf PRIMARY 270336 KB Unlimited 65536 KB data only
AdventureWorks2017_log 2 D:\mssql\SQLEXPRESS\AdventureWorks2017_log.ldf NULL 73728 KB 2147483648 KB 65536 KB log only
ファイルグループの情報はsp_helpfilegroupで表示します。
1> sp_helpfilegroup
2> go
groupname groupid filecount
-------------------- ------- -----------
PRIMARY 1 1
データベースの容量に関する情報を表示
sp_spaceused でデータベースの容量に関する情報を表示します。
1> sp_spaceused
2> go
database_name database_size unallocated space
------------------- ------------------ ------------------
master 7.13 MB 1.99 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
3464 KB 1512 KB 1392 KB 560 KB
参考
-
Transact-SQL リファレンス (データベース エンジン)
-
sp_configure (Transact-SQL)
-
sp_server_info (Transact-SQL)
-
sp_helpfile (Transact-SQL)
-
sp_helpfilegroup (Transact-SQL)
-
sp_spaceused (Transact-SQL)