SQLServer備忘1

●テーブル情報参照: select * from sys.tables
●ログイン情報の取得: select * from syslogins
カラム名の取得: select * from syscolumns
●テーブル名の取得: select * from sys.tables
スキーマ一覧: select * from sys.schemas
●認証を無効にするには: ALTER LOGIN DISABLE
●DBファイル: sys.database_files
●テーブル名の変更: exec sp_rename 'Owner.MASTER', 'MASTER_BAK';
●主キーの変更: exec sp_rename 'Owner.MASTER_PKC','MASTER_PKC_BAK';

 

●対象テーブルのカラム名
select name from sys.all_columns where object_id =
(select object_id from sys.all_objects where name = '<対象テーブル名>')
order by column_id
スキーマの生成
CREATE SCHEMA スキーマ名 AUTHORIZATION <スキーマ所有者?>
●データベース ファイル名の変更方法
use <DB名>
ALTER DATABASE <DB名> MODIFY FILE 
( NAME = <論理名>, 
FILENAME = 'D:\Database\UserDB\xxxx_201509.mdf' );
※パス名は、変更後のパス名
●SqlConnection.ConnectionString の指定方法例(Windows統合認証の例)
Server=MSSQL1;Database=AdventureWorks;Integrated Security=true;
●dboがデフォルトスキーマになってしまう点。
ユーザにsysadmin権限を与えていると、自動的にdboで接続される。
それ以外は既定のスキーマが有効になる。
dboは消せない。

●権限チェック
SQL Server は最初にオブジェクトの所有者と、呼び出し元オブジェクト 
(つまり、チェーンにおける直前のリンク) の所有者とを比較します。
オブジェクトの所有者がどちらも同じであれば、参照されているオブジェクトに
対する権限はチェックされません。 あるオブジェクトが、所有者の
異なる別のオブジェクトにアクセスする場合、所有権の継承が途切れるため、
必ず呼び出し元のセキュリティ コンテキストが SQL Server によってチェックされる。

●DBのrename
SSMSで可能。ただし、データベース ファイル名はそのままなので、変更が必要。

◎DBリストア時にデータベースファイルを別ファイル名にするには、
●管理者専用接続(この設定はローカルのみ。リモートを許可する時は1)
sp_configure 'remote admin connections', 0;
go
reconfigure
go
●設定値確認
exec sp_configure
●ロックエスカレーションの状態確認
DBCC tracestatus(1211,-1)
SQL Serverを使用しているサービスのパスワード変更時
https://msdn.microsoft.com/ja-jp/library/ms365941(v=sql.105).aspx

 

●データベース ファイル名の変更方法
use <DB名>
ALTER DATABASE <DB名> MODIFY FILE ( NAME = <論理名>, FILENAME = '<DBファイル名>' );
●リストア後、不明なユーザの対処方法
・不明なユーザー一覧を表示する方法
USE データベース名
EXEC sp_change_users_login 'Report'
・ユーザーマッピングの修復方法
USE データベース名
EXEC sp_change_users_login 'Update_One', 'test', 'test'
(EXEC sp_change_users_login 'Update_One', '現在のDBに存在するユーザー名', 'SQL Serverログイン名') 
●別のユーザでWindows認証でログインしたい場合、以下で行う。
Shiftキーを押しながら、SSMSを起動する。ログインダイアログボックスが
表示されるので、実行したいログインユーザで認証をする。

 

管理者で起動する場合も、Shiftキーを押しながらSSMSを起動することができる。

 

●NamedPipeで接続するには

 

np:サーバー名

 

SQL Server Management Studio と sqlcmd ユーティリティは、リモートの SQL Server に対して TCP/IP で接続試行し、接続できない場合は名前付きパイプの接続試行に切り替える。

 

Windows では、TCP/IP での応答の待ち時間 (タイムアウト) は初回 3 秒、再試行回数は 2 回。
再試行時のタイムアウトは前回の倍の秒数となるため、TCP/IP での接続試行により SQL Server に接続できずタイムアウトするまでには、次の合計値の 21 秒要する。

 

初回 3 秒 + 1 回目の再試行 6 秒 + 2 回目の再試行 12 秒

以上の動作より、論理的には、SQL Server の待ち受けプロトコルTCP/IP が無効であり、名前付きパイプが有効である場合は、21 秒より長い接続タイムアウト値でないと SQL Server に接続できない。

 

●データベース プリンシパルは、データベースの スキーマ を所有しているので、削除できない。

 

[データベース]-[対象DB選択]-[セキュリティ]-[スキーマ]-[プロパティ] でスキーマの所有者を問題ないユーザに変更する。

 

●guest

 

データベース ユーザは、データベース レベルのプリンシパル
データベース ユーザはすべて、public ロールのメンバ。
guest ユーザデータベースを作成すると、既定で guest ユーザが含まれる。guest ユーザに許可された権限は、このデータベースにユーザ アカウントのないユーザーに継承される。
guest ユーザは削除不可だが、CONNECT 権限を取り消すことで無効にすることは可。CONNECT 権限を取り消すには、master または tempdb 以外のデータベース内で REVOKE CONNECT FROM GUEST を実行。

 

●権限等の調査
use master
select * from sys.server_principals
select * from sys.database_principals
◎セッションKILL
select * from sys.dm_exec_sessions ⇒セッション情報
select * from sys.dm_exec_requests

sp_who ⇒ これだと利用しているDB名が表示される。

kill プロセスID(セッションID)
●◎データベースが (シングル ユーザー) または (制限付きユーザー) のアクセスに設定されている場合

 

[データベース]-[プロパティ]-[オプション]-[その他のオプション]
[状態] セクション [アクセスの制限] の値を [MULTI_USER] に変更

●ファイルの利用容量?
use <DB名>
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
●DBファイルのSHRINK
use <DB名>
SELECT file_id, name
FROM sys.database_files;
DBCC SHRINKFILE (1,TRUNCATEONLY);

1は、file_id
●ログの切り捨て
バックアップを取得すれば切り捨てられる。ログの圧縮で小さくはできる。

 

・ログを最後にバックアップしてからチェックポイントが発生したこと。
チェックポイントの後、少なくとも次のトランザクション ログのバックアップまで
ログはそのまま保たれる。
・ログ トランザクションを妨げる要因が他にないこと。
(長時間の処理や、レプリケーションが未完等)
・BACKUP LOG ステートメントで WITH COPY_ONLY を指定していないこと。

 

●ログの空き容量
use <DB名>
DBCC SQLPERF(LOGSPACE);
GO
●ログの切り捨てが遅れている理由
use <DB名>
select name,log_reuse_wait,log_reuse_wait_desc from sys.databases 
●メンテナンスプラン の結果ログ出力先

 

メンテナンスプランの[接続の管理…]コンボボックスの右隣に
「レポートとログ記録」というアイコンがあり、そこで出力先を指定する」

 

●getdate()の使い方例(適当なテーブルを指定しても出力できる)
select getdate(),* from sys.tables

select getdate()-10
●各プリンシパルへの権限付与状況
select p.name,e.class_desc,object_name(e.major_id),e.state_desc,e.permission_name
from sys.server_permissions as e
join sys.server_principals as p
on e.grantee_principal_id = p.principal_id
●各データベースユーザへの権限付与状況
select p.name,e.class_desc,object_name(e.major_id),e.state_desc,e.permission_name
from sys.database_permissions as e
join sys.database_principals as p
on e.grantee_principal_id = p.principal_id
DROP INDEX文の作成
use DBNAME
select 'DROP INDEX ' + name + ' ON ' + OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) + ';' from sys.indexes 
where *1 and is_unique = 1 and is_primary_key = 0 and index_id > 1
●DatabaseMailUserRole ロールにユーザーを追加するには、

 

事前に、対象ユーザをmsdbのDatabaseMailUserRole ロールにユーザマッピングし、当該ロールを付与しておく。
use msdb
exec sp_addrolemember DatabaseMailUserRole, <ユーザ名>
セキュリティを向上させるために、データベース メールでメール プロファイルへのアクセスが管理される。プロファイルはパブリックまたはプライベートにできる。msdb データベースの DatabaseMailUserRole データベース ロールのすべてのメンバが、パブリック プロファイルを利用できます。

 

DatabaseMailUserRole ロールのすべてのメンバは、このプロファイルを使用して電子メールを送信可。プライベート プロファイルは、msdb データベースのセキュリティ プリンシパル用に定義されます。指定したデータベース ユーザー、ロール、および sysadmin 固定サーバー ロールのメンバのみ、このプロファイルを使用して電子メールを送信できます。既定では、プロファイルはプライベートで、sysadmin 固定サーバー ロールのメンバのみがアクセスできます。プライベート プロファイルを使用するには、sysadmin が、プライベート プロファイルを使用するための権限をユーザーに与える必要があります。

 

また、sp_send_dbmail ストアド プロシージャの EXECUTE 権限は、DatabaseMailUserRole のメンバにのみ与えられます。ユーザーが電子メール メッセージを送信できるようにするには、システム管理者がユーザーを DatabaseMailUserRole データベース ロールに追加する必要があります。

 

●データベースメール設定を確認する設定
sp_configure 'show advanced', 1; 
GO
RECONFIGURE;
GO
sp_configure;
GO
●データベースメールを設定するには
sp_configure 'Database Mail XPs', 1; 
GO
RECONFIGURE;
GO
●DatabaseMailUserRoleロールメンバーを確認するには
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
●データベース メールを送信するユーザーは、少なくとも 1 つのデータベース メール プロファイルにアクセスできる必要があります。ユーザー (プリンシパル) とそのユーザーがアクセスできるプロファイルを一覧するには、次のステートメントを実行します。
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
プリンシパル
プリンシパルは、SQL Server リソースを要求できるエンティティです。SQL Server の承認モデルの他のコンポーネントと同様に、プリンシパルは階層内に配置できます。プリンシパルの効力のスコープは、プリンシパルの定義のスコープ (Windows、サーバー、データベース) と、プリンシパルが分割できないアイテムであるかコレクションであるかによって異なります。分割できないプリンシパルの例には Windows ログインがあり、コレクションであるプリンシパルの例には Windows グループがあります。各プリンシパルには、1 つのセキュリティ識別子 (SID) があります。
Windows レベルのプリンシパル
- Windows ドメイン ログイン
- Windows ローカル ログイン
SQL Server レベルのプリンシパル
- SQL Server ログイン
◆データベースレベルのプリンシパル
- データベース ユーザー
- データベース ロール
- アプリケーション ロール

 

SQL Server sa ログイン
SQL Server sa ログインは、サーバー レベルのプリンシパルです。このログインは、インスタンスのインストール時に既定で作成されます。SQL Server 2005 および SQL Server 2008 では、sa の既定のデータベースは master です。これは、以前のバージョンの SQL Server の動作から変更されています。

 

◆public データベース ロール
データベース ユーザーはすべて、public データベース ロールに属しています。セキュリティ保護可能なリソースに対する特定の権限が与えられていないか権限が拒否されたユーザーは、public がそのリソースに対して許可されている権限を継承します。

 

◆INFORMATION_SCHEMA と sys
各データベースには、カタログ ビューにユーザーとして表示される 2 つのエンティティ INFORMATION_SCHEMA および sys が含まれています。SQL Server はこれらを必要とします。これらのエンティティはプリンシパルではなく、変更も削除もできません。

 

◆証明書ベースの SQL Server ログイン
名前が 2 つの番号記号 (##) で囲まれたサーバー プリンシパルは、内部システムでのみ使用されます。SQL Server のインストール時に証明書から作成される以下のプリンシパルは、削除しないでください。

 

 

 

●メールを飛ばす場合(SQL Server Agentからの場合)
EXECUTE msdb.dbo.sp_notify_operator @name=N'operator1',@subject=N'【タイトル】DBバックアップ処理',@body=N'表記処理正常終了'
●権限系メモ
use <DB名>
select * from sys.objects

SELECT * FROM fn_builtin_permissions(default);

SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('SCHEMA') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('LOGIN') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('USER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('OBJECT') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('ENDPOINT') ORDER BY permission_name;
●ロールに含まれる権限を調べるには。
sp_dbfixedrolepermission で取得
●CONNECT BYの代替サンプル

 

 

のE.参照。

 

●包含権限を確認する。(ORACLEのCONNECT BY、LEVEL擬似列の代替サンプル)
スコープは以下'SERVER'を変える。

WITH n(permission_name, covering_permission_name, level) AS 
(SELECT permission_name, covering_permission_name , 0 as [LEVEL]
FROM sys.fn_builtin_permissions('')
WHERE class_desc = 'SERVER'
and len(covering_permission_name) = 0
UNION ALL
SELECT nplus1.permission_name, nplus1.covering_permission_name , [LEVEL] + 1
FROM sys.fn_builtin_permissions('') as nplus1, n
WHERE n.permission_name = nplus1.covering_permission_name
and nplus1.class_desc = 'SERVER')
SELECT RIGHT(' '+permission_name,len(permission_name)+level),covering_permission_name,level FROM n

上記スコープ('SERVER'の部分)の候補:
XML SCHEMA COLLECTION
TYPE
ASYMMETRIC KEY
APPLICATION ROLE
LOGIN
REMOTE SERVICE BINDING
CONTRACT
MESSAGE TYPE
ROUTE
OBJECT
ASSEMBLY
USER
ENDPOINT
SERVER
DATABASE
SCHEMA
SYMMETRIC KEY
CERTIFICATE
FULLTEXT STOPLIST
ROLE
FULLTEXT CATALOG
SERVICE
SQL Server カラム定義を変更すると「変更の保存が許可されていません」が表示された場合の対処法

 

基本はテーブル再作成が必要な場合、警告される模様。
http://nasunoblog.blogspot.jp/2013/10/sql-server-column-edit-error.html

 

●columnからSQL生成

 

以下は2つのINSERT-SELECTを作成する。
use <DB名>
select SQL FROM ( 
select 2 as A,0 as B,
case column_id 
when 1 THEN 'INSERT INTO ' + '<テーブル名1>' + ' ( ' + name 
when (select max(column_id) from sys.columns 
where object_id = (select object_id from sys.tables 
where name ='<テーブル名1>')) 
THEN ',' + name + ' ) ' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='<テーブル名1>') 
UNION ALL 
select 2 as A,1 as B,
case column_id 
when 1 THEN 'SELECT ' + name 
when (select max(column_id) from sys.columns 
where object_id = (select object_id from sys.tables 
where name ='テーブル名1')) 
THEN ',' + name + ' FROM BK_' + 'テーブル名1' + ';' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='テーブル名1') 
UNION ALL 
select 3 as A,0 as B,
case column_id 
when 1 THEN 'INSERT INTO ' + 'テーブル名2' + ' ( ' + name 
when (select max(column_id) from sys.columns 
where object_id = (select object_id from sys.tables 
where name ='テーブル名2')) 
THEN ',' + name + ' ) ' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='テーブル名2') 
UNION ALL 
select 3 as A,1 as B,
case column_id 
when 1 THEN 'SELECT ' + name 
when (select max(column_id) from sys.columns 
where object_id = (select object_id from sys.tables 
where name ='テーブル名2')) 
THEN ',' + name + ' FROM BK_' + 'テーブル名2' + ';' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='テーブル名2') 
) AS A order by A,B,column_id
●WITH句の利用例(入力パラメータを設定することはできない模様で、テーブル名2は正しく出力できない)
use <DB名>;
WITH TabName(oid) AS 
(select object_id as oid from sys.tables where name ='テーブル名1')
,MaxTab(m) AS 
(select max(column_id) as m from sys.columns 
where object_id = (select oid from TabName))
select SQL FROM ( 
select 2 as A,0 as B,
case column_id 
when 1 THEN 'INSERT INTO ' + 'テーブル名1' + ' ( ' + name 
when (select m from MaxTab) 
THEN ',' + name + ' ) ' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = (select oid from TabName) 
UNION ALL 
select 2 as A,1 as B,
case column_id 
when 1 THEN 'SELECT ' + name when (select m from MaxTab) 
THEN ',' + name + ' FROM BK_' + 'テーブル名1' + ';' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = (select oid from TabName) 
UNION ALL 
select 3 as A,0 as B,
case column_id 
when 1 THEN 'INSERT INTO ' + 'テーブル名2' + ' ( ' + name 
when (select max(column_id) from sys.columns 
where object_id = (select object_id from sys.tables 
where name ='テーブル名2')) 
THEN ',' + name + ' ) ' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='テーブル名2') 
UNION ALL 
select 3 as A,1 as B,
case column_id 
when 1 THEN 'SELECT ' + name 
when (select max(column_id)from sys.columns where object_id = (select object_id from sys.tables 
where name ='テーブル名2')) 
THEN ',' + name + ' FROM BK_' + 'テーブル名2' + ';' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='テーブル名2') 
) AS A order by A,B,column_id
●エクスポート・インポート
https://msdn.microsoft.com/ja-jp/library/ms140052(v=sql.105).aspx
https://technet.microsoft.com/ja-jp/library/ms175937%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
http://mtgsqlserver.blogspot.jp/2013/03/blog-post_10.html#id26

 

エクスポート
bcp AdventureWorks2008R2.Sales.Currency out Currency.dat -T -c
bcp AdventureWorks2008R2.Sales.Currency out Currency.dat -c -U<login_id> -S<server_name\instance_name>
インポート
bcp AdventureWorks2008R2.Sales.Currency2 in Currency.dat -T -c
空テーブルの作成
USE AdventureWorks2008R2;
GO
SELECT * INTO AdventureWorks2008R2.Sales.Currency2 
FROM AdventureWorks2008R2.Sales.Currency WHERE 1=2
BULK INSERTの例
https://technet.microsoft.com/ja-jp/library/ms175915(v=sql.105).aspx

 

●IDENTITY列があるテーブルへのインポート例(INSERT文)
https://msdn.microsoft.com/ja-jp/library/ms188059.aspx?f=255&MSPPError=-2147217396
SET IDENTITY_INSERT SAMPLE_1 ON
INSERT INTO SAMPLE_1
(ID,
Name,
Status,
XTime)
SELECT ID,
Name,
Status,
XTime
FROM SAMPLE_2
SET IDENTITY_INSERT SAMPLE_1 OFF
●テーブル事前確認(どのテーブルがどのスキーマか)
use <DB>
select t.name,s.name,s.schema_id from sys.tables t,sys.schemas s
where t.schema_id = s.schema_id;
●リコンパイルするには。
sp_recompile
b) 次回実行時にリコンパイルする(テーブル or ストアドプロシージャ単位) 
sp_recompile

特定のクエリが遅い場合の試行方法

 

ステートメント)
USE <データベース名>; 
GO 
EXEC sp_recompile N'<テーブル名 or ストアドプロシージャ名>'; 
GO 

 

使用例) tbl_01 テーブルを対象とするストアド プロシージャおよびトリガーが次回実行時に再コンパイルされます。 
USE mydatabase; 
GO 
EXEC sp_recompile N'dbo.tbl_01'; 
GO 
●ストアドプロシジャ一覧
SELECT name, crdate FROM sysobjects WHERE type='P' ORDER BY name;
●ストアドリコンパイルトラブルシューティング
https://support.microsoft.com/ja-jp/kb/243586

 

●カウントを取ってみる(テーブル名,件数 で表示したい場合)。
use <DB名>
select 'テーブル名1' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名1 UNION ALL 
select 'テーブル名2' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名2 UNION ALL 
select 'テーブル名3' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名3 ;
スキーマ権限で、あるスキーマ全体を見えなくできるのでは

 

 

結局、双方のレプリカサイトで同じユーザ/パスワードのアカウントが必要

 

 

●MINUSのかわりはEXCEPT
select * from [DB].[SCHEMA].テーブル名1
except
select * from [DB].[SCHEMA].テーブル名2
●useの説明

 

SQL Server ログインで SQL Server に接続すると、自動的に既定のデータベースに接続し、データベース ユーザーのセキュリティ コンテキストを取得できます。SQL Server ログイン用のデータベース ユーザーが 1 人も作成されていない場合、ログインは guest として接続されます。データベース ユーザーが、データベースに対する CONNECT 権限を持たない場合、USE ステートメントは失敗します。ログインに既定のデータベースが割り当てられていない場合、既定のデータベースとして master が設定されます。
USE は、コンパイル時と実行時の両方で実行でき、その効果は直ちに有効になります。したがって、バッチ内で USE ステートメントの後にあるステートメントは、指定したデータベースで実行されます。

 

●goの説明
GO は Transact-SQL ステートメントではなく、sqlcmd および osql ユーティリティと SQL Server Management Studio コード エディターで認識されるコマンドです。
SQL Server のユーティリティでは、GO は、現在の Transact-SQL ステートメントのバッチを SQL Server インスタンスに送信するためのシグナルとして解釈されます。現在のステートメントのバッチは、前回の GO の後に入力されたすべてのステートメントで構成されます。最初の GO の場合、現在のバッチは、アドホック セッションまたはスクリプトの開始後に入力されたすべてのステートメントで構成されます。
Transact-SQL ステートメントを GO コマンドと同じ行に入力することはできません。ただし、GO コマンドの行にコメントは入力できます。
ユーザーは、バッチの規則に従う必要があります。たとえば、バッチの最初のステートメント以降でストアド プロシージャを実行する場合は、バッチに EXECUTE キーワードを含める必要があります。ローカル (ユーザー定義) 変数のスコープはバッチ内に限られ、GO コマンドの後では参照できません。

 

SQL Server アプリケーションでは、複数の Transact-SQL ステートメントSQL Server インスタンスに送信し、バッチとして実行できます。バッチ内のステートメントは、1 つの実行プランにコンパイルされます。SQL Server のユーティリティでアドホック ステートメントを実行する場合、または SQL Server ユーティリティを介して実行する Transact-SQL ステートメントスクリプトを作成する場合、プログラマは GO を使用してバッチの終了を知らせる必要があります。
ODBC または OLE DB API に基づくアプリケーションで、GO コマンドを実行しようとすると、構文エラーになります。SQL Server のユーティリティからサーバーに GO コマンドが送信されることはありません。

 

GO は、権限を必要としないユーティリティ コマンドです。すべてのユーザーが実行できます。

 

---------------------------------------------------------------------

*1:OBJECT_SCHEMA_NAME(object_id) = '<スキーマ名>')
OR (OBJECT_SCHEMA_NAME(object_id) = 'dbo' 
and name like '<テーブル接頭辞>%'