【SQL Server】コピペで使えるSQL Serverを調査・解析する時に便利なクエリ一覧

【SQL Server】コピペで使えるSQL Serverを調査・解析する時に便利なクエリ一覧

今のプロジェクトではSQL Serverを使用しているのですが、データ挿入漏れしていないかの調査だったり、検証用DBのテーブルを一括削除して再生成するということが多いです。


その処理を行おうとする度に忘れて再度調べるという悪循環が起きているため、今回は備忘録として自分がコピペで使用できて役に立ったクエリ達を紹介していこうと思います。

1. 【全テーブルのDROP文を作成する】クエリ


このクエリを実行すると、すべてのテーブルのDROP文が生成できますのでコピペして貼るだけでテーブルを一括削除することができます。

SELECT 
    'DROP TABLE ',
    name,
    ';'
FROM 
    sys.objects
WHERE type='U'
ORDER BY name;


もしスキーマが存在する場合には、下記のように文章を加えればスキーマも込みの文章ができます。

SELECT 
    'DROP TABLE ',
    schema_Name(schema_id) as schemaName,
    '.',
    name,
    ';'
FROM 
    sys.objects
WHERE type='U'
ORDER BY schemaName, name;


汎用性があるので、DROP TABLESELECT TRUNCATE TABLE にすることでさまざまな文章を一括生成することができます。


下記サイトを参考にさせていただきました。

【SQL Server】全テーブルの全レコードを削除する


2. 【全テーブルのレコード件数を取得する】クエリ

対象DBにあるテーブルのレコード件数を取得してくれるクエリはこちらです。

SELECT 
    schema_name(obj.schema_id) AS schemaName,
    obj.name AS TableName, 
    ind.rows
FROM 
    sys.objects AS obj
JOIN sys.sysindexes AS ind
    ON 
        obj.object_id = ind.id 
        AND 
        ind.indid < 2 
WHERE
    obj.type = 'U'
ORDER BY 
    obj.name;

件数が多い時だと遅くなってしまう SELECT COUNT(*) FROM テーブル名 よりも高速で調べることができます。

また、下記のように条件を追加することでレコードが1件もないテーブルを取得することができます。

SELECT 
    schema_name(obj.schema_id) AS schemaName,
    obj.name AS TableName, 
    ind.rows
FROM 
    sys.objects AS obj
JOIN sys.sysindexes AS ind
    ON 
        obj.object_id = ind.id 
        AND 
        ind.indid < 2 
WHERE
    obj.type = 'U'
    AND 
    ind.rows = 0 
ORDER BY 
    obj.name;


さらにwhere文に、sys.objects.object_id = OBJECT_ID(‘テーブル名’)を加えることで、特定のテーブル名の情報だけを取得することも可能です。

obj.object_id = OBJECT_ID('テーブル名');

-- LIKE文の場合は下の文章をWHERE文に追加する。
OBJECT_ID(obj.object_id) LIKE '%テーブル名 or テーブル名の一部%'



下記サイトを参考にさせていただきました。

【SQL Server】全テーブルのデータ件数を一括取得する

3. 【テーブルに用いているCOLLATE(照合順序)の一覧を取得する】クエリ


COLLATE(照合順序)とは、文字列検索の際に「どういう判断で検索できるか」というものです。


検索したい文字(Apple)を全角半角を同一(APPLEやappleも含む)にして検索できるようにする、バイトコードに変換して完全一致のみ検索できるようにする、といったようなことが可能になります。


COLLATEはDB全体、もしくは文字データカラムに個別に設定することができます。
それぞれのCOLLATEを取得するクエリはこちらです。

/* DB全体のCOLLATEを調べるクエリ */
SELECT convert(nvarchar(128), serverproperty('collation'));

/* テーブルカラムのCOLLATEを調べるクエリ */
SELECT
    table_name,
    column_name,
    collation_name
FROM
    information_schema.columns;


もしwhere文を行うときに、COLLATEが異なっていたもの同士でLIKE検索などをしてしまった場合には下記のようにエラーが起こります。

Cannot resolve the collation conflict between "【COLLATE1つめ】" and "【COLLATE2つめ】" in the equal to operation.

このようなエラーが発生した場合にはALTER文を用いてCOLLATE変更するか、外部制約がついている場合は、テーブルを再生成して 変更します。


下記を参考にさせていただきました。

SQL Serverでの照合順序の確認方法

照合順序と Unicode のサポート

4. 【テーブルのカラム名を検索する】クエリ


特定のテーブル名やカラム名を検索できるクエリです。テーブルが多かったり初見のDBを触る時にかなり重宝します。


where文で B.name (columnName : カラム名) とすることでカラム検索を行なっています。A.name(tableName : テーブル名) とすることでテーブル検索することもできます。カラムが一番便利です。

SELECT 
    schema_name(A.schema_id) as schemaName, 
	  A.name as tableName,
    B.name as columnName
FROM
    sys.tables AS A
INNER JOIN sys.columns AS B
    ON A.object_id = B.object_id
WHERE
    B.name LIKE '%カラム名の一部 or 全部%'
ORDER BY
    schemaName
    , tableName
    , columnName;

5. 【現在接続中のクエリ一覧を確認する】クエリ

下記のサイトの1番目をコピペするだけで調べることができます。(素晴らしい記事ですのでぜひご一読ください)

SELECT TOP 100
     der.session_id as spid
    ,der.blocking_session_id as blk_spid
    ,datediff(s, der.start_time, GETDATE()) as elapsed_sec
    ,DB_NAME(der.database_id) AS db_name
    ,des.host_name
    ,des.program_name
    ,der.status -- Status of the request. (background / running / runnable / sleeping / suspended)
    ,dest.text as command_text
    ,REPLACE(REPLACE(REPLACE(SUBSTRING(dest.text, 
    (der.statement_start_offset / 2) + 1, 
    ((CASE der.statement_end_offset
    WHEN -1 THEN DATALENGTH(dest.text)
    ELSE der.statement_end_offset
    END - der.statement_start_offset) / 2) + 1),CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' ') AS current_running_stmt
    ,datediff(s, der.start_time, GETDATE()) as time_sec
    ,wait_resource --ロックされているリソース名
    ,wait_type
    ,last_wait_type --最後または現在の待機の種類の名前
    ,der.wait_time  as wait_time_ms
    ,der.open_transaction_count
    ,der.command
    ,der.percent_complete
    ,der.cpu_time
    ,(case der.transaction_isolation_level
      when 0 then 'Unspecified'
      when 1 then 'ReadUncomitted'
      when 2 then 'ReadCommitted'
      when 3 then 'Repeatable'
      when 4 then 'Serializable'
      when 5 then 'Snapshot'
    else cast(der.transaction_isolation_level as varchar) end) as transaction_isolation_level
    ,der.granted_query_memory * 8 as granted_query_memory_kb --キロバイト単位
    ,deqp.query_plan -- 実行プラン
FROM
    sys.dm_exec_requests der
JOIN sys.dm_exec_sessions des ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS dest
OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS deqp
WHERE
    des.is_user_process = 1
AND datediff(s, der.start_time, GETDATE()) >= 1 -- 例:1秒以上実行中のクエリに限定
AND dest.text like '%%' -- クエリの中身でlike検索したいときはここを編集
ORDER BY
    datediff(s, der.start_time, GETDATE()) DESC


こちらのクエリを使用して、長い時間かかっているクエリを特定することができます。

原因不明な長期間実行されないクエリやキャンセルできないクエリを探し出すのに重宝しています。

まとめ


参考にした優秀な先人達の知恵をお借りさせていただきました。本当にありがとうございます。

調査は実務を行う際には重要ですし、障害対応などにも役に立てるので、是非みなさんも活用してください。


解析で必要だなと感じたもの、便利だなと思ったものがありましたらどんどん更新していきたいと思います。

この記事が誰かの役に立てたら幸いです。

SQL Serverカテゴリの最新記事