What's this page?

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

DBオブジェクト系

DBテーブル一覧、カラム一覧

以下の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

インデックス一覧

 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内に、スクリプトが記載されています。

 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のステップ検索用

 USE msdb
 SELECT *
 FROM sysjobs j
 INNER JOIN sysjobsteps js
         ON js.job_id = j.job_id
 WHERE js.command LIKE '%JOB_%'

カラム名検索

 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のパブリケートしてるテーブル/サブスクリプション一覧

 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内のテーブル一覧とレプリケーションされたテーブルかどうかを調べる

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内のテーブル一覧と使用量一覧

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分離レベルの確認

DBCC useroptions
-- isolation level の欄です。

ユーザ登録系

DBユーザの登録

 EXEC sp_adduser @login_name, @name_in_db, @role
 -- e.g. EXEC sp_adduser 'user', 'user', 'db_owner'

ログインユーザの作成

EXEC sp_addlogin @login_name, @password
-- @see http://msdn.microsoft.com/ja-jp/library/ms173768.aspx

オブジェクトのステータス変更系

ID値リセット

 DBCC CHECKIDENT(table_name, reseed)

DEFAULT値付きカラムの削除方法

  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

構文

  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 が実行される

その他

週の始め位置をズラす

SET DATEFIRST n

デッドロック検出

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

 DBCC TRACEON(1204, -1)

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

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

 -- OFF
 DBCC TRACEOFF(1204, -1)

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

[SQLServer] 逆引きクエリ集