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 'テーブル名'
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