[SQLServer] 逆引きクエリ集

# What's this page?

ちょっと便利なクエリ集。
忘れそうなので、メモ。

# DBオブジェクト系
## DBテーブル一覧、カラム一覧
以下のSQLでテーブルやカラムの一覧が取得できます。

```sql
 SELECT o.name AS 'テーブル名', c.name AS 'カラム名'
   FROM syscolumns c
 LEFT JOIN sysobjects o 
	   ON o.id = c.id 
 WHERE o.xtype = 'U' 
   AND c.name LIKE 'user_name'
 ORDER BY o.name
```

## インデックス一覧
```sql
 SELECT
 	o.name 'テーブル名',
 	i.name 'インデックス名'
 FROM sysindexes i
 INNER JOIN sys.objects o 
 		ON o.object_id = i.id
 WHERE i.name like 'INDEX_%'
```


## viewやストアドの検索用
syscomments テーブルのtext内に、スクリプトが記載されています。

```sql
 SELECT
    o.name,
    replace(replace(c.text, NCHAR(13), '\r'), NCHAR(10), '\n')
 FROM sys.objects o
 INNER JOIN syscomments c
 		ON o.object_id = c.id 
 		AND o.is_ms_shipped <> 1 -- MSSQLが自動で発行したものを除く
 		AND o.type = 'P' -- ストアドプロシージャ
 		--AND o.type = 'V' -- viewテーブル
 		AND c.text LIKE '%user_detail%'
 ORDER BY o.name
```

## JOBのステップ検索用
```sql
 USE msdb
 SELECT *
 FROM sysjobs j
 INNER JOIN sysjobsteps js
         ON js.job_id = j.job_id
 WHERE js.command LIKE '%JOB_%'
```

## カラム名検索
```sql
 SELECT DISTINCT o.name,c.name
 FROM syscolumns c
 INNER JOIN sysobjects o
         ON o.id = c.id
        AND o.xtype = 'U'
 WHERE c.name LIKE '%user_name%'
 ORDER BY o.name
```

# レプリケーション絡み
## 特定DBのパブリケートしてるテーブル/サブスクリプション一覧
```sql
 SELECT TOP 1000 p.name 'publication_name', a.name 'article_name', s.srvname, s.dest_db
 FROM [dbname].[dbo].[syspublications] p
 INNER JOIN [dbname].[dbo].[sysarticles] a
 	  ON a.pubid = p.pubid 
	  AND ( a.name LIKE 'table1%' OR a.name LIKE 'table2%' )
 INNER JOIN [dbname].[dbo].[syssubscriptions] s
 		ON s.artid = a.artid
 ORDER BY p.name, s.srvname, a.name
```



## 特定のDB内のテーブル一覧と レプリケーションされたテーブルかどうかを調べる
```sql
SELECT 
  o.name,
  CASE 
    WHEN NOT EXISTS (SELECT 0 FROM MSreplication_objects mo WHERE mo.article = o.name) THEN ''
    ELSE '○' END 'レプリケーションされたテーブル'
 FROM sys.objects o 
 WHERE o.type = 'U'
   AND o.is_ms_shipped = 0
 ORDER BY o.name
```

## 特定DB内のテーブル一覧と使用量一覧
```sql
DECLARE @table_name varchar(64)
CREATE TABLE #spaceused (
 	name nvarchar(128),
 	rows char(11),
 	reserved varchar(18),
 	data varchar(18),
 	index_size varchar(18),
 	unused varchar(18)
 );

DECLARE tableCursor CURSOR FOR
SELECT name
  FROM sysobjects o
 WHERE o.xtype='U'
ORDER BY name

OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @table_name
WHILE @@fetch_status = 0
  BEGIN 
 	insert into #spaceused EXEC dbo.sp_spaceused @table_name
 	fetch next from tableCursor into @table_name
  END
 SELECT * FROM #spaceused
 CLOSE tableCursor
 DEALLOCATE tableCursor
 DROP table #spaceused
```

## 現在のsnpashot分離レベルの確認
```sql
DBCC useroptions
-- isolation level の欄です。
```

# ユーザ登録系
## DBユーザの登録
```sql
 EXEC sp_adduser @login_name, @name_in_db, @role
 -- e.g. EXEC sp_adduser 'user', 'user', 'db_owner'
```

## ログインユーザの作成
```sql
EXEC sp_addlogin @login_name, @password
-- @see http://msdn.microsoft.com/ja-jp/library/ms173768.aspx
```


# オブジェクトのステータス変更系
## ID値リセット
```sql
 DBCC CHECKIDENT(table_name, reseed)
```

## DEFAULT値付きカラムの削除方法
```sql
  DECLARE @TABLE_NAME NVARCHAR(256)
  DECLARE @COLUMN_NAME NVARCHAR(256)
  DECLARE @TABLE_ID INTEGER 
  DECLARE @COLUMN_ID INTEGER 
  DECLARE @CONSTRAINT_NAME NVARCHAR(256)
  SET @TABLE_NAME = 'helps'
  SET @COLUMN_NAME = 'carrier'
  
  --削除したいテーブルのシステムidを取得する
  SELECT @TABLE_ID = id FROM sys.sysobjects 
  WHERE xtype = 'U' AND name = @TABLE_NAME
  
  --削除したいカラムのシステムidを取得する
  SELECT @COLUMN_ID = column_id FROM sys.columns 
  WHERE object_id = @TABLE_ID AND name = @COLUMN_NAME
  
  --削除したい制約名を取得する
  SELECT @CONSTRAINT_NAME = name FROM sys.sysobjects 
  WHERE id = 
    (SELECT constid FROM sys.sysconstraints 
      WHERE id = @TABLE_ID AND colid = @COLUMN_ID)
  
  --制約を削除する
  EXEC('ALTER TABLE '+ @TABLE_NAME + ' DROP CONSTRAINT ' + @CONSTRAINT_NAME)
  
  --カラムを削除する
  ALTER TABLE helps DROP COLUMN carrier
```

# MERGE
## 構文
```sql
  MERGE INTO table_name as t
     USING () AS tmp ( column1, column2, ...)
     ON t.column1 = tmp.column1 AND ...
  WHEN MATCHED THEN 
      [INSERT/UPDATE/DELETE]
  WHEN NOT MATCHED THEN
      [INSERT/UPDATE/DELETE]
```

## 考え方
- SELECTを最初に実行
- この際、table_name と tmp でJOINしているイメージ。（ON句が結合条件）
- SELECT で残った場合、MATCHED が実行される
- SELECT で残らなかった場合、NOT MATCHED が実行される


# その他
## 週の始め位置をズラす
```sql
SET DATEFIRST n
```

## デッドロック検出
デッドロックを検出するには、以下のコマンドを実行してトレースフラグを有効にします。

```sql
 DBCC TRACEON(1204, -1)
```

※トレースフラグ詳細↓
http://msdn.microsoft.com/ja-jp/library/ms188396.aspx

このコマンドはTRACEOFFコマンドを実行するか、SQL Server を再起動するまで有効です。
（従ってDB単位ではなく、サーバ単位で有効になります）

```sql
 -- OFF
 DBCC TRACEOFF(1204, -1)
```

トレースフラグがONの場合、SQL Server の「SQL Serverログ」よりデッドロックの起こった内情を知る事ができます。
ログの読み方↓
http://support.microsoft.com/kb/832524/ja