概要
いつもは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
コマンドラインから操作する
pip install mssql-cli
を行い、接続は
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/
ここも参照すると良かった。
SELECT name, ftcatid FROM sysobjects WHERE ftcatid > 0
EXEC sp_fulltext_table 'table_name', 'drop'
DROP FULLTEXT CATALOG fulltext_catalog_name
よく使うけど全く覚えられないSQL達
ユーザー一覧を取得
SELECT *
FROM sys.server_principals
WHERE TYPE IN ('S')
and name not like '%##%'
ORDER BY name, type_desc
show current user
select CURRENT_USER
データベース一覧を取得
select name from sys.databases;
データベース作成、削除
create database <database name>;
drop database <database name>;
データベースの指定
use <database name>;
テーブル一覧を取得する。
select * from sys.objects;
create table
create table test1 (id int primary key, col1 char);
Describe table に相当するもの
EXEC sp_help <table_name>
テーブルのカラムに作られた統計量を参照する。
DBCC SHOW_STATISTICS ('tablename', 'columnname')
index 一覧を取得する
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)
alter table <tablename> drop constraint <indexname>
index を消去
drop index <indexname> on <tablename>
full text catalog を参照する
SELECT fulltextcatalogproperty('<catalog name>', 'ItemCount');
create unique index
create unique index pk_test1_id on test1(id);
full text catalog を作る
create fulltext catalog <catalog name> as default;
fulltext index を振る
create fulltext index on dbo.test1(col1) key index pk_test1_id with change_tracking auto;
テーブルのコメントを取得する
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
;
カラムのコメントを取得する
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
カラムに指定されたデフォルト値を参照する
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '<table_name>'
pip install pymssql するときに怒られる
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.
こんなエラーが出た時は、
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がインストールできたのに、コネクションが確立できない。
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
とすると、
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で接続ができないのも頷けます。
解決策は上のリンクで書いてある通り、
export TDSVER=7.0
これをすることでコネクトできました。（うーん、、、）
pyodbc を使うときのエラー
[unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')