一、修复之前添加错的表结构
IF EXISTS(SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'dbo.SaleStock_Extend') AND NAME = 'CaiNiaoCustomArea')
BEGIN
IF OBJECT_ID(N'dbo.DF_SaleStockExtend_CaiNiaoCustomArea') IS NOT NULL
BEGIN
ALTER TABLE SaleStock_Extend DROP CONSTRAINT [DF_SaleStockExtend_CaiNiaoCustomArea]
PRINT 'ALTER TABLE SaleStock_Extend DROP CONSTRAINT DF_SaleStockExtend_CaiNiaoCustomArea'
END
IF OBJECT_ID(N'dbo.DF_SaleStockExtend_CaiNiaoCustomArea') IS NULL
BEGIN
declare @name varchar(50)
select @name =b.name from sysobjects b join syscolumns a on b.id = a.cdefault
where a.id = object_id('SaleStock_Extend')
and a.name ='CaiNiaoCustomArea'
--print @name --DF_Pro_List_Num_iid
exec('ALTER TABLE SaleStock_Extend DROP CONSTRAINT ' + @name)
print('ALTER TABLE SaleStock_Extend DROP CONSTRAINT ' + @name)
END
ALTER TABLE SaleStock_Extend DROP COLUMN CaiNiaoCustomArea
print 'ALTER TABLE SaleStock_Extend DROP COLUMN CaiNiaoCustomArea'
END
GO
二、检查数据库空间使用情况
select o.name,
SUM(p.reserved_page_count) as reserved_page_count,
SUM(p.used_page_count) as used_page_count,
SUM(
case
when(p.index_id<2) then (p.in_row_data_page_count+
p.lob_used_page_count+p.row_overflow_used_page_count)
else p.lob_used_page_count+p.row_overflow_used_page_count
end
) as DataPages,
SUM(
case
when (p.index_id<2) then row_count
else 0
end
) as rowCounts
from sys.dm_db_partition_stats p inner join sys.objects o
on p.object_id=o.object_id
group by o.name
having SUM(p.used_page_count) > 3500
三、查找有关被阻塞的请求的信息 (含用户)
SELECT s. loginame
,[Individual Query] = SUBSTRING (qr. text,qs .statement_start_offset/ 2,
(CASE WHEN qs. statement_end_offset = -1 THEN LEN( CONVERT(NVARCHAR (MAX), qr.text)) * 2
ELSE qs .statement_end_offset
END - qs.statement_start_offset )/2)
,
qs.session_id ,s. counts AS [ 进程个数], qs.status ,qs. blocking_session_id
,
qs.wait_type ,qs. wait_time ,qs .wait_resource
,
qs.transaction_id
FROM SYS.DM_EXEC_REQUESTS qs (nolock )
LEFT JOIN (
SELECT spid, MAX(loginame )AS loginame ,
COUNT(0 )AS counts FROM SYS.SYSPROCESSES (nolock ) GROUP BY spid
) s ON qs.session_id =s. spid
OUTER APPLY SYS.DM_EXEC_SQL_TEXT( qs.sql_handle ) AS qr
WHERE qs. status = N'suspended'
--and s.loginame<>''
ORDER BY qs.wait_time DESC
--查找阻塞源头v3.0
SELECT SP. spid
,CASE WHEN ST1. text IS NULL THEN ST2.text
ELSE SUBSTRING ( ST1.text ,SR. statement_start_offset/2 ,
(
CASE WHEN SR.statement_end_offset = - 1 THEN LEN(CONVERT (NVARCHAR( MAX), ST1 .text)) * 2
ELSE SR .statement_end_offset
END - SR.statement_start_offset )/2
)
END AS [T-sql]
,SP. loginame
,DB_NAME( SP.dbid ) AS [db_name]
,
SP.open_tran ,SP. hostname,
SP.program_name ,SP. waitresource,SP .*
FROM SYS.SYSPROCESSES SP (nolock )
LEFT JOIN SYS.DM_EXEC_REQUESTS SR (nolock) ON SP .spid= SR.session_id
LEFT JOIN SYS.DM_EXEC_CONNECTIONS SC (nolock ) ON SP.spid =SC. session_id
OUTER APPLY SYS.DM_EXEC_SQL_TEXT( SC.most_recent_sql_handle ) AS ST2
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SR .sql_handle) AS ST1
WHERE SP. spid
IN
(
SELECT BLOCKED FROM SYS.SYSPROCESSES (nolock) WHERE BLOCKED <>0
)
AND SP. BLOCKED=0
四、更新数据事务
BEGIN TRANSACTION
--查询更新前数据
SELECT Id,SkuId,CkNo,DjQuantity,Quantity FROM Ck_ProSku WHERE SkuId=10 AND CkNo='D1567'
SELECT Id,SkuId,CkNo,AreaId,DjQuantity,Quantity FROM Ck_AreaKuc WHERE SkuId=10 AND AreaId=1237 AND CkNo='D1567'
--更新数据
UPDATE Ck_ProSku
SET DjQuantity=DjQuantity-5,Quantity=Quantity+5
WHERE CkNo='D1567' AND SkuId=10
UPDATE Ck_AreaKuc
SET DjQuantity=DjQuantity-5,Quantity=Quantity+5
WHERE CkNo='D1567' AND AreaId=1237 AND SkuId=10
--查询更新后数据
SELECT Id,SkuId,CkNo,DjQuantity,Quantity FROM Ck_ProSku WHERE SkuId=10 AND CkNo='D1567'
SELECT Id,SkuId,CkNo,AreaId,DjQuantity,Quantity FROM Ck_AreaKuc WHERE SkuId=10 AND AreaId=1237 AND CkNo='D1567'
ROLLBACK TRANSACTION
五、表属性查询脚本
SELECT
表名
=
CASE
WHEN
C
.
column_id
=
1
THEN
O
.
name
ELSE
N''
END
,
表说明
=
ISNULL
(
CASE
WHEN
C
.
column_id
=
1
THEN
PTB
.
[value]
END
,
N''
),
字段序号
=
C
.
column_id
,
字段名
=
C
.
name
,
主键
=
ISNULL
(
IDX
.
PrimaryKey
,
N''
),
标识
=
CASE
WHEN
C
.
is_identity
=
1
THEN
N'
√
'
ELSE
N''
END
,
计算列
=
CASE
WHEN
C
.
is_computed
=
1
THEN
N'
√
'
ELSE
N''
END
,
类型
=
T
.
name
,
长度
=
C
.
max_length
,
精度
=
C
.
precision
,
小数位数
=
C
.
scale
,
允许空
=
CASE
WHEN
C
.
is_nullable
=
1
THEN
N'
√
'
ELSE
N''
END
,
默认值
=
ISNULL
(
D
.
definition
,
N''
),
字段说明
=
ISNULL
(
PFD
.
[value]
,
N''
),
索引名
=
ISNULL
(
IDX
.
IndexName
,
N''
),
索引排序
=
ISNULL
(
IDX
.
Sort
,
N''
),
创建时间
=
O
.
Create_Date
,
修改时间
=
O
.
Modify_date
FROM
sys
.
columns
C
INNER
JOIN
sys
.
objects
O
ON
C
.
[object_id]
=
O
.
[object_id]
AND
O
.
type
=
'U'
AND
O
.
is_ms_shipped
=
0
INNER
JOIN
sys
.
types
T
ON
C
.
user_type_id
=
T
.
user_type_id
LEFT
JOIN
sys
.
default_constraints
D
ON
C
.
[object_id]
=
D
.
parent_object_id
AND
C
.
column_id
=
D
.
parent_column_id
AND
C
.
default_object_id
=
D
.
[object_id]
LEFT
JOIN
sys
.
extended_properties
PFD
ON
PFD
.
class
=
1
AND
C
.
[object_id]
=
PFD
.
major_id
AND
C
.
column_id
=
PFD
.
minor_id
-- AND PFD.name='Caption' --
字段说明对应的描述名称
(
一个字段可以添加多个不同
name
的描述
)
LEFT
JOIN
sys
.
extended_properties
PTB
ON
PTB
.
class
=
1
AND
PTB
.
minor_id
=
0
AND
C
.
[object_id]
=
PTB
.
major_id
-- AND PFD.name='Caption' --
表说明对应的描述名称
(
一个表可以添加多个不同
name
的描述
)
LEFT
JOIN
--
索引及主键信息
(
SELECT
IDXC
.
[object_id]
,
IDXC
.
column_id
,
Sort
=
CASE
INDEXKEY_PROPERTY
(
IDXC
.
[object_id]
,
IDXC
.
index_id
,
IDXC
.
index_column_id
,
'IsDescending'
)
WHEN
1
THEN
'DESC'
WHEN
0
THEN
'ASC'
ELSE
''
END
,
PrimaryKey
=
CASE
WHEN
IDX
.
is_primary_key
=
1
THEN
N'
√
'
ELSE
N''
END
,
IndexName
=
IDX
.
Name
FROM
sys
.
indexes
IDX
INNER
JOIN
sys
.
index_columns
IDXC
ON
IDX
.
[object_id]
=
IDXC
.
[object_id]
AND
IDX
.
index_id
=
IDXC
.
index_id
LEFT
JOIN
sys
.
key_constraints
KC
ON
IDX
.
[object_id]
=
KC
.
[parent_object_id]
AND
IDX
.
index_id
=
KC
.
unique_index_id
INNER
JOIN
--
对于一个列包含多个索引的情况
,
只显示第个索引信息
(
SELECT
[object_id]
,
Column_id
,
index_id
=
MIN
(
index_id
)
FROM
sys
.
index_columns
GROUP
BY
[object_id]
,
Column_id
)
IDXCUQ
ON
IDXC
.
[object_id]
=
IDXCUQ
.
[object_id]
AND
IDXC
.
Column_id
=
IDXCUQ
.
Column_id
AND
IDXC
.
index_id
=
IDXCUQ
.
index_id
)
IDX
ON
C
.
[object_id]
=
IDX
.
[object_id]
AND
C
.
column_id
=
IDX
.
column_id
WHERE
O
.
name
=
'WMS_Record_Pro_List'
--
如果只查询指定表
,
加上此条件
ORDER
BY
C
.
name
,
C
.
column_id