メモ

sqlcmd -S サーバー名 -U ユーザー名 -P パスワード -d データベース名 -Y20 -y 12
:help
:out stdout
:out hoge.txt
set showplan_all on
rollback
go

BEGIN TRANSACTION
go

select object_id, name  from sys.synonyms order by name

select object_id, name from sys.views order by name;
select object_id, type_desc, name from sys.tables order by name;

select name, database_id, create_date from sys.databases

sp_help 'テーブル名'

-- desc
select coalesce(cast(i1.key_ordinal as char(2)), '') as PK, coalesce(cast(i2.key_ordinal as char(2)), '') as UQ, y1.name, c1.max_length, c1.precision, c1.scale,
       case when c1.is_nullable = 0 then 'NOT NULL' else '' end as nullable, coalesce(cast(d1.definition as nvarchar(20)), '') as [default], c1.collation_name, c1.name
  from sys.columns as c1
  left join sys.types as y1 on c1.user_type_id = y1.user_type_id
  left join sys.key_constraints as k1 on k1.parent_object_id = c1.object_id and k1.type = 'PK'
  left join sys.index_columns as i1 on c1.object_id = i1.object_id and c1.column_id = i1.column_id and k1.parent_object_id = i1.object_id and k1.unique_index_id = i1.index_id
  left join sys.key_constraints as k2 on k2.parent_object_id = c1.object_id and k1.type = 'UQ'
  left join sys.index_columns as i2 on c1.object_id = i2.object_id and c1.column_id = i2.column_id and k1.parent_object_id = i2.object_id and k1.unique_index_id = i2.index_id
  left join sys.default_constraints as d1 on c1.object_id = d1.parent_object_id and c1.column_id = d1.parent_column_id
 where c1.object_id = object_id('テーブル名', 'U')
 order by c1.column_id

select @@version

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE


SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID


SELECT
  resource_type as オブジェクトの種類,
  (CASE 
    WHEN resource_type = N'ALLOCATION_UNIT' THEN 
      (SELECT 
        OBJECT_NAME(sp.object_id) + N':' +  si.name
        FROM 
          sys.allocation_units 
          LEFT JOIN 
            sys.partitions sp 
            ON 
              container_id = hobt_id 
          LEFT JOIN 
            sys.indexes si 
            ON 
              sp.object_id = si.object_id 
              AND sp.index_id = si.index_id
        WHERE 
          allocation_unit_id = resource_associated_entity_id)  
    WHEN  resource_type = N'OBJECT' THEN 
      OBJECT_NAME(CONVERT(bigint, resource_associated_entity_id))
    WHEN resource_type IN(N'HOBT', N'KEY', N'PAGE') THEN 
      (SELECT 
        OBJECT_NAME(sp.object_id) + N':' +  si.name
      FROM 
        sys.partitions sp 
        LEFT JOIN 
          sys.indexes si 
          ON 
            sp.object_id = si.object_id 
            AND sp.index_id = si.index_id 
      WHERE 
        hobt_id = resource_associated_entity_id) 
    ELSE    
      CONVERT(nvarchar(40), resource_associated_entity_id) 
  END) as エンティティID,
  request_mode as ロックの種類,
  request_type as 要求の種類,
  request_status as 状態
 From sys.dm_tran_locks
 WHERE [resource_type]<>'DATABASE'


次の例では、ロック タイムアウトの待ち時間を 1800 ミリ秒に設定します。
SET LOCK_TIMEOUT 1800
GO

この例では、LOCK_TIMEOUT 値が設定されていない場合の結果セットを表示します。
SELECT @@LOCK_TIMEOUT AS [Lock Timeout]
GO