Sql结构修改

 一、修复之前添加错的表结构 

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