mssqlを使うときの備忘録

## 概要
いつもはmysqlを使うが、色々なデータベースに触れる機会があり苦しめられたので、
備忘録として役に立ったことを書いていく。随時更新していく。


## 記録

### GUI 使う
普通mysqlを触る時はコマンドからすることが多いけど、mssqlなどGUIをインストールして使うのが前提？？

Azure Data Studio を使うのがおそらく前提、ないと（あっても）不便。
https://docs.microsoft.com/ja-jp/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver15

### コマンドラインから操作する

```bash

pip install mssql-cli
```
を行い、接続は

```bash

mssql-cli -S <server URL> -d <database name> -U <username> -P <password>
```

で行える。接続を切る時は

```
Ctrl+D
```

### mssql-cli のよく使うコマンドたち

#### テーブル一覧を取得する

```
\lt
```



### full text search indexを振って、消すときの注意。

https://docs.microsoft.com/ja-jp/sql/t-sql/statements/drop-fulltext-catalog-transact-sql?view=sql-server-ver15
これだけするとエラーになったので、
https://dbtut.com/index.php/2018/12/16/cannot-drop-full-text-catalog-catalog_name-because-it-contains-a-full-text-index/
ここも参照すると良かった。


```sql

SELECT name, ftcatid FROM sysobjects WHERE ftcatid > 0

EXEC sp_fulltext_table 'table_name', 'drop'

DROP FULLTEXT CATALOG fulltext_catalog_name
```


###よく使うけど全く覚えられないSQL達

### ユーザー一覧を取得
```sql
SELECT *
FROM sys.server_principals
WHERE TYPE IN ('S')
and name not like '%##%'
ORDER BY name, type_desc
```

### show current user

```sql
select CURRENT_USER  
```

### データベース一覧を取得
```sql
select name from sys.databases;
```

### データベース作成、削除

```sql
create database <database name>;
drop database <database name>;
```
### データベースの指定

```sql
use <database name>;
```

### テーブル一覧を取得する。

```sql
select * from sys.objects;
```

### create table

```sql
create table test1 (id int primary key, col1 char);
```

### Describe table に相当するもの

```sql

EXEC sp_help <table_name>
```


### テーブルのカラムに作られた統計量を参照する。

```sql

DBCC SHOW_STATISTICS ('tablename', 'columnname')
```

### index 一覧を取得する

```sql
SELECT	S.name AS SchemaName,
	O.name AS ObjectName,
	I.name AS IndexName,
	I.type_desc AS IndexTypeDesc,
	I.is_primary_key AS IsPrimaryKey,
	I.is_unique AS IsUnique,
	I.is_disabled AS IsDisabled
FROM	sys.indexes AS I
	   INNER JOIN sys.objects AS O
	      ON I.object_id = O.object_id
	   INNER JOIN sys.schemas AS S
	      ON O.schema_id = S.schema_id
WHERE	I.index_id > 0
	AND O.is_ms_shipped = 0
ORDER BY S.name,
	 O.name,
	 I.name;
```

### index を消去するときのパーミッション変更 (if it is clustered)

```sql
alter table <tablename> drop constraint <indexname>
```
### index　を消去

```sql
drop index <indexname> on <tablename>
```


### full text catalog を参照する
```sql

SELECT fulltextcatalogproperty('<catalog name>', 'ItemCount');
```

### create unique index

```sql
create unique index pk_test1_id on test1(id);
```

### full text catalog を作る

```sql
create fulltext catalog <catalog name> as default;
```

### fulltext index を振る

```sql
create fulltext index on dbo.test1(col1) key index pk_test1_id with change_tracking auto; 

```

### テーブルのコメントを取得する

```sql

select
     t.name    as TABLE_NAME
    ,ep.value  as COMMENT
from
     sys.tables              t
    ,sys.extended_properties ep
where
    t.name = 'TEST_TABLE'
    and
    t.object_id = ep.major_id
    and
    ep.minor_id = 0
;
```

### カラムのコメントを取得する

```sql

SELECT
     t.name    AS table_name
    ,c.name    AS column_name
    ,ep.value  AS commnet
FROM
     sys.tables AS t
    ,sys.columns AS c
    ,sys.extended_properties AS ep
WHERE
        t.name = '<table_name>'
    AND t.object_id = c.object_id
    AND c.object_id = ep.major_id
    AND c.column_id = ep.minor_id
```

### カラムに指定されたデフォルト値を参照する

```sql

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '<table_name>'
```

### ``` pip install pymssql``` するときに怒られる

```bash
 building '_mssql' extension
 creating build
 creating build/temp.macosx-10.13-x86_64-3.7
 creating build/temp.macosx-10.13-x86_64-3.7/src
 clang -Wno-unused-result -Wsign-compare -Wunreachable-code -fno-common -dynamic -DNDEBUG -g -fwrapv -O3 -Wall -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.14.sdk/System/Library/Frameworks/Tk.framework/Versions/8.5/Headers -I/usr/local/include -I/usr/local/include -I/usr/local/opt/openssl/include -I/usr/local/opt/sqlite/include -I/usr/local/Cellar/python/3.7.0/Frameworks/Python.framework/Versions/3.7/include/python3.7m -c src/_mssql.c -o build/temp.macosx-10.13-x86_64-3.7/src/_mssql.o -DMSDBLIB
 src/_mssql.c:599:10: fatal error: 'sqlfront.h' file not found
 #include "sqlfront.h"
      ^~~~~~~~~~~~
 1 error generated.
```

こんなエラーが出た時は、

```bash
wget http://mirrors.ibiblio.org/freetds/stable/freetds-0.91.tar.gz &&
 tar -xzvf freetds-0.91.tar.gz && cd freetds-0.91 && ./configure --enable-msdblib &&sudo make && make install && pip install pymssql==2.1.4
```

上記のコマンドで解決した。


### 上で freetds 入れてpymssqlがインストールできたのに、コネクションが確立できない。

```bash
pymssql.OperationalError: (20017, b'DB-Lib error message 20017, severity 9:\nUnexpected EOF from the server\nNet-Lib error during Operation now in progress (36)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed\n')
```

こんなエラーが出ます。

このリンクが救ってくれました。https://pyyou.wordpress.com/2009/02/01/install-pymssql-and-how-to-deal-with-db-lib-error-message-20009-severity-9/
感謝いたします。

ここにもある通り、 freetds を入れた場合、tsql というライブラリを使って　mssqlへと接続します。
したがって、tsqlを直接使って、確立できるかをまず確かめます。
このbinファイルは、自分の環境では```/usr/local/bin``` にありました。

```
./tsql -H myip -p 1433 -U myuser
```

とすると、

```bash

locale is "C/UTF-8/C/C/C/C"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20017 (severity 9):
	Unexpected EOF from the server
	OS error 36, "Operation now in progress"
Error 20002 (severity 9):
	Adaptive Server connection failed
There was a problem connecting to the server
```

確かに tsql を使って接続が確立できていないことがわかりました。
したがってpymssqlで接続ができないのも頷けます。

解決策は上のリンクで書いてある通り、

```bash
export TDSVER=7.0
```

これをすることでコネクトできました。（うーん、、、）


### pyodbc を使うときのエラー

```
[unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')
```