SQL Server システム ストアド プロシージャでデータベース情報取得

システム ストアド プロシージャ（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

```





また、データベースを指定することでデータベースのファイルの割り当てに関する情報も表示されます



```sp_help 
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 リファレンス (データベース エンジン)
  - https://docs.microsoft.com/ja-jp/sql/t-sql/language-reference?view=sql-server-2017

- sp_configure (Transact-SQL)
  - https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-configure-transact-sql?view=sql-server-2017

- sp_server_info (Transact-SQL)
  - https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-server-info-transact-sql?view=sql-server-2017

- sp_helpfile (Transact-SQL)
  - https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-helpfile-transact-sql?view=sql-server-2017

- sp_helpfilegroup (Transact-SQL)
  - https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-helpfilegroup-transact-sql?view=sql-server-2017

- sp_spaceused (Transact-SQL)
  - https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-spaceused-transact-sql?view=sql-server-2017