'Database/Query' 카테고리의 글 목록

SELECT s.name + '.' + t.Name AS [Table Name], part.rows AS [Total Rows In Table - Modified],

CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3))

AS [Table's Total Space In GB]

FROM

SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id

INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id

AND idx.Index_id = part.Index_id

INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id

INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id

INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id

WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255

GROUP BY t.Name, s.name, part.rows

ORDER BY [Table's Total Space In GB] DESC

'Database > Query' 카테고리의 다른 글

INDEX 상세 정보 조회 (0)	2020.11.02
프로시저 파리미터 (0)	2020.10.20
DELETE 복원 프로시저 (0)	2020.10.20
EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
Find and Kill all the Blocked Process/Query (0)	2020.09.22

declare @SchemaName sysname=NULL

, @TableName sysname=NULL

, @IndexName sysname=NULL

, @dataspace sysname=NULL

set @SchemaName = 'Sales'

set @TableName = 'SalesOrderDetail'

--set @IndexName = 'AK_SalesOrderDetail_rowguid'

declare @_SchemaName varchar(100)

declare @_TableName varchar(256)

declare @_IndexName varchar(256)

declare @ColumnName varchar(256)

declare @is_unique varchar(100)

declare @IndexTypeDesc varchar(100)

declare @FileGroupName varchar(100)

declare @is_disabled varchar(100)

declare @IndexColumnId int

declare @IsDescendingKey int

declare @IsIncludedColumn int

-- getting the index sizes

SELECT schema_name(t.schema_id) [SchemaName],

OBJECT_NAME(ix.OBJECT_ID) AS TableName,

ix.name AS IndexName,

CAST( 8 * SUM(a.used_pages)/1024.0 AS DECIMAL(20,1))AS 'Indexsize(MB)'

INTO #IndexSizeTable

from sys.tables t

inner join sys.indexes ix on t.object_id=ix.object_id

inner join sys.partitions AS p ON p.OBJECT_ID = ix.OBJECT_ID AND p.index_id = ix.index_id

inner join sys.allocation_units AS a ON a.container_id = p.partition_id

WHERE ix.type>0 and t.is_ms_shipped=0

and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name)

GROUP BY schema_name(t.schema_id), ix.OBJECT_ID,ix.name

ORDER BY OBJECT_NAME(ix.OBJECT_ID),ix.name

--getting important properties of indexes

select schema_name(t.schema_id) [SchemaName], t.name TableName, ix.name IndexName,

cast( '' as varchar(max)) AS IndexKeys, casT('' as varchar(max)) AS IncludedColumns,

ix.is_unique

, ix.type_desc, ix.fill_factor as [Fill_Factor]

, ix.is_disabled , da.name as data_space,

ix.is_padded,

ix.allow_page_locks,

ix.allow_row_locks,

INDEXPROPERTY(t.object_id, ix.name, 'IsAutoStatistics') IsAutoStatistics ,

ix.ignore_dup_key

INTO #helpindex

from sys.tables t

inner join sys.indexes ix on t.object_id=ix.object_id

inner join sys.data_spaces da on da.data_space_id= ix.data_space_id

where ix.type>0 and t.is_ms_shipped=0

and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name)

and da.name=isnull(@dataspace,da.name)

order by schema_name(t.schema_id), t.name, ix.name

---getting the index keys and included columns

declare CursorIndex cursor for

select schema_name(t.schema_id) [schema_name], t.name, ix.name

from sys.tables t

inner join sys.indexes ix on t.object_id=ix.object_id

where ix.type>0 and t.is_ms_shipped=0

and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name)

order by schema_name(t.schema_id), t.name, ix.name

open CursorIndex

fetch next from CursorIndex into @_SchemaName, @_TableName, @_IndexName

while (@@fetch_status=0)

begin

declare @IndexColumns varchar(4000)

declare @IncludedColumns varchar(4000)

set @IndexColumns=''

set @IncludedColumns=''

declare CursorIndexColumn cursor for

select col.name, ixc.is_descending_key, ixc.is_included_column

from sys.tables tb

inner join sys.indexes ix on tb.object_id=ix.object_id

inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id

inner join sys.columns col on ixc.object_id =col.object_id and ixc.column_id=col.column_id

where ix.type>0 and tb.is_ms_shipped=0

and schema_name(tb.schema_id)=@_SchemaName and tb.name=@_TableName and ix.name=@_IndexName

order by ixc.index_column_id

open CursorIndexColumn

fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn

while (@@fetch_status=0)

begin

if @IsIncludedColumn=0

set @IndexColumns=@IndexColumns + @ColumnName +', '

else

set @IncludedColumns=@IncludedColumns + @ColumnName +', '

fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn

end

close CursorIndexColumn

deallocate CursorIndexColumn

set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)

set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end

UPDATE #helpindex

SET IndexKeys = @IndexColumns, IncludedColumns=@IncludedColumns

WHERE [SchemaName]=@_SchemaName and TableName=@_TableName and IndexName=@_IndexName

fetch next from CursorIndex into @_SchemaName, @_TableName, @_IndexName

end

close CursorIndex

deallocate CursorIndex

--showing the results

SELECT hi.SchemaName, hi.TableName, hi.IndexName, hi.IndexKeys, hi.IncludedColumns, ixs.[Indexsize(MB)],

hi.is_unique, hi.type_desc,hi.data_space, hi.Fill_Factor, hi.IsAutoStatistics,

hi.is_disabled, hi.is_padded, hi.allow_page_locks, hi.allow_row_locks,hi.ignore_dup_key

FROM #helpindex hi

INNER JOIN #IndexSizeTable ixs ON hi.SchemaName=ixs.SchemaName and hi.TableName=ixs.TableName and hi.IndexName=ixs.IndexName

order by hi.SchemaName, hi.TableName, hi.IndexKeys, hi.IncludedColumns

drop table #helpindex

drop table #IndexSizeTable

'Database > Query' 카테고리의 다른 글

테이블 사이즈 (0)	2020.11.13
프로시저 파리미터 (0)	2020.10.20
DELETE 복원 프로시저 (0)	2020.10.20
EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
Find and Kill all the Blocked Process/Query (0)	2020.09.22

-- borrowed from Erland Sommarskog

-- Link : http://www.sommarskog.se/query-plan-mysteries.html#dmvgettingplans

-- Remember that you are looking at the estimated plan so the actual no. of rows and actual executions wont be there ! <-- Important why a particular plan is bad.

DECLARE @dbname nvarchar(256),

@procname nvarchar(256)

SELECT @dbname = 'Northwind', -- Your DB name

@procname = 'dbo.List_orders_11' -- The SP that you want to get parameters for !

; WITH basedata AS (

SELECT qs.statement_start_offset/2 AS stmt_start,

qs.statement_end_offset/2 AS stmt_end,

est.encrypted AS isencrypted, est.text AS sqltext,

epa.value AS set_options, qp.query_plan,

charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')

AS paramstart,

charindex('</ParameterList>', qp.query_plan) AS paramend

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est

CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,

qs.statement_start_offset,

qs.statement_end_offset) qp

CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa

WHERE est.objectid = object_id (@procname)

AND est.dbid = db_id(@dbname)

AND epa.attribute = 'set_options'

), next_level AS (

SELECT stmt_start, set_options, query_plan,

CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'

WHEN stmt_start >= 0

THEN substring(sqltext, stmt_start + 1,

CASE stmt_end

WHEN 0 THEN datalength(sqltext)

ELSE stmt_end - stmt_start + 1

END)

END AS Statement,

CASE WHEN paramend > paramstart

THEN CAST (substring(query_plan, paramstart,

paramend - paramstart) AS xml)

END AS params

FROM basedata

)

SELECT set_options AS [SET], n.stmt_start AS Pos, n.Statement,

CR.c.value('@Column', 'nvarchar(128)') AS Parameter,

CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value],

CAST (query_plan AS xml) AS [Query plan]

FROM next_level n

CROSS APPLY n.params.nodes('ColumnReference') AS CR(c)

ORDER BY n.set_options, n.stmt_start, Parameter

'Database > Query' 카테고리의 다른 글

테이블 사이즈 (0)	2020.11.13
INDEX 상세 정보 조회 (0)	2020.11.02
DELETE 복원 프로시저 (0)	2020.10.20
EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
Find and Kill all the Blocked Process/Query (0)	2020.09.22

Create PROCEDURE Recover_Deleted_Data_Proc

@Database_Name NVARCHAR(MAX),

@SchemaName_n_TableName NVARCHAR(Max),

@Date_From DATETIME='1900/01/01',

@Date_To DATETIME ='9999/12/31'

AS

DECLARE @RowLogContents VARBINARY(8000)

DECLARE @TransactionID NVARCHAR(Max)

DECLARE @AllocUnitID BIGINT

DECLARE @AllocUnitName NVARCHAR(Max)

DECLARE @SQL NVARCHAR(Max)

DECLARE @Compatibility_Level INT

SELECT @Compatibility_Level=dtb.compatibility_level

FROM

master.sys.databases AS dtb WHERE dtb.name=@Database_Name

/*

IF ISNULL(@Compatibility_Level,0)<=80

BEGIN

RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)

RETURN

END

*/

IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_SCHEMA]+'.'+[TABLE_NAME]=@SchemaName_n_TableName)=0

BEGIN

RAISERROR('Could not found the table in the defined database',16,1)

RETURN

END

DECLARE @bitTable TABLE

(

[ID] INT,

[Bitvalue] INT

)

--Create table to set the bit position of one byte.

INSERT INTO @bitTable

SELECT 0,2 UNION ALL

SELECT 1,2 UNION ALL

SELECT 2,4 UNION ALL

SELECT 3,8 UNION ALL

SELECT 4,16 UNION ALL

SELECT 5,32 UNION ALL

SELECT 6,64 UNION ALL

SELECT 7,128

--Create table to collect the row data.

DECLARE @DeletedRecords TABLE

(

[Row ID] INT IDENTITY(1,1),

[RowLogContents] VARBINARY(8000),

[AllocUnitID] BIGINT,

[Transaction ID] NVARCHAR(Max),

[FixedLengthData] SMALLINT,

[TotalNoOfCols] SMALLINT,

[NullBitMapLength] SMALLINT,

[NullBytes] VARBINARY(8000),

[TotalNoofVarCols] SMALLINT,

[ColumnOffsetArray] VARBINARY(8000),

[VarColumnStart] SMALLINT,

[Slot ID] INT,

[NullBitMap] VARCHAR(MAX)

)

--Create a common table expression to get all the row data plus how many bytes we have for each row.

;WITH RowData AS (

SELECT

[RowLog Contents 0] AS [RowLogContents]

,[AllocUnitID] AS [AllocUnitID]

,[Transaction ID] AS [Transaction ID]

--[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)

,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData] --@FixedLengthData

-- [TotalnoOfCols] = Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)

,CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) as [TotalNoOfCols]

--[NullBitMapLength]=ceiling([Total No of Columns] /8.0)

,CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) as [NullBitMapLength]

--[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )

,SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3,

CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))) as [NullBytes]

--[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )

,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN

CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],

CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END) AS [TotalNoofVarCols]

--[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )

,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN

SUBSTRING([RowLog Contents 0]

, CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) + 2

, (CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN

CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],

CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END)

* 2) ELSE null END) AS [ColumnOffsetArray]

-- Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)

,CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)

THEN (

CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))

+ ((CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN

CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],

CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)

,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2)))) ELSE null END) * 2))

ELSE null End AS [VarColumnStart]

,[Slot ID]

FROM sys.fn_dblog(NULL, NULL)

WHERE

AllocUnitId IN

(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits

INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2

AND partitions.partition_id = allocunits.container_id)

WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))

AND Context IN ('LCX_MARK_AS_GHOST', 'LCX_HEAP') AND Operation in ('LOP_DELETE_ROWS')

And SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)

/*Use this subquery to filter the date*/

AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)

WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')

And [Transaction Name] In ('DELETE','user_transaction')

And CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)),

--Use this technique to repeate the row till the no of bytes of the row.

N1 (n) AS (SELECT 1 UNION ALL SELECT 1),

N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),

N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),

N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)

FROM N3 AS X, N3 AS Y)

INSERT INTO @DeletedRecords

SELECT RowLogContents

,[AllocUnitID]

,[Transaction ID]

,[FixedLengthData]

,[TotalNoOfCols]

,[NullBitMapLength]

,[NullBytes]

,[TotalNoofVarCols]

,[ColumnOffsetArray]

,[VarColumnStart]

,[Slot ID]

---Get the Null value against each column (1 means null zero means not null)

,[NullBitMap]=(REPLACE(STUFF((SELECT ',' +

(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(NullBytes, n, 1) % 2)) ELSE CONVERT(NVARCHAR(1),((SUBSTRING(NullBytes, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]

FROM

N4 AS Nums

Join RowData AS C ON n<=NullBitMapLength

Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',',''))

FROM RowData D

IF (SELECT COUNT(*) FROM @DeletedRecords)=0

BEGIN

RAISERROR('There is no data in the log as per the search criteria',16,1)

RETURN

END

DECLARE @ColumnNameAndData TABLE

(

[Row ID] int,

[Rowlogcontents] varbinary(Max),

[NAME] sysname,

[nullbit] smallint,

[leaf_offset] smallint,

[length] smallint,

[system_type_id] tinyint,

[bitpos] tinyint,

[xprec] tinyint,

[xscale] tinyint,

[is_null] int,

[Column value Size]int,

[Column Length] int,

[hex_Value] varbinary(max),

[Slot ID] int,

[Update] int

)

--Create common table expression and join it with the rowdata table

-- to get each column details

/*This part is for variable data columns*/

--@RowLogContents,

--(col.columnOffValue - col.columnLength) + 1,

--col.columnLength

--)

INSERT INTO @ColumnNameAndData

SELECT

[Row ID],

Rowlogcontents,

NAME ,

cols.leaf_null_bit AS nullbit,

leaf_offset,

ISNULL(syscolumns.length, cols.max_length) AS [length],

cols.system_type_id,

cols.leaf_bit_position AS bitpos,

ISNULL(syscolumns.xprec, cols.precision) AS xprec,

ISNULL(syscolumns.xscale, cols.scale) AS xscale,

SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,

(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0

THEN

(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000

THEN

CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) - POWER(2, 15)

ELSE

CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

END)

END) AS [Column value Size],

(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN

(Case

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN (Case When [System_type_id]In (35,34,99) Then 16 else 24 end)

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN (Case When [System_type_id]In (35,34,99) Then 16 else 24 end) --24

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN (CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart]))

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)

END) AS [Column Length]

,(CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE

SUBSTRING

(

Rowlogcontents,

(

(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000

THEN

CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) - POWER(2, 15)

ELSE

CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

END)

-

(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN (Case When [System_type_id]In (35,34,99) Then 16 else 24 end) --24

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN (Case When [System_type_id]In (35,34,99) Then 16 else 24 end) --24

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)

) + 1,

(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN (Case When [System_type_id] In (35,34,99) Then 16 else 24 end) --24

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN (Case When [System_type_id] In (35,34,99) Then 16 else 24 end) --24

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN ABS(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart]))

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And

ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))

- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)

)

END) AS hex_Value

,[Slot ID]

,0

FROM @DeletedRecords A

Inner Join sys.allocation_units allocunits On A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]

INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)

INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id

LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id

WHERE leaf_offset<0

UNION

/*This part is for fixed data columns*/

SELECT

[Row ID],

Rowlogcontents,

NAME ,

cols.leaf_null_bit AS nullbit,

leaf_offset,

ISNULL(syscolumns.length, cols.max_length) AS [length],

cols.system_type_id,

cols.leaf_bit_position AS bitpos,

ISNULL(syscolumns.xprec, cols.precision) AS xprec,

ISNULL(syscolumns.xscale, cols.scale) AS xscale,

SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,

(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM

sys.system_internals_partition_columns C WHERE cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5 AS [Column value Size],

syscolumns.length AS [Column Length]

,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE

SUBSTRING

(

Rowlogcontents,(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 And C.leaf_bit_position=0 THEN max_length ELSE 0 END),0) FROM

sys.system_internals_partition_columns C where cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5

,syscolumns.length) END AS hex_Value

,[Slot ID]

,0

FROM @DeletedRecords A

Inner Join sys.allocation_units allocunits ON A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]

INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)

INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id

LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id

WHERE leaf_offset>0

Order By nullbit

Declare @BitColumnByte as int

Select @BitColumnByte=CONVERT(INT, ceiling( Count(*)/8.0)) from @ColumnNameAndData Where [System_Type_id]=104

;With N1 (n) AS (SELECT 1 UNION ALL SELECT 1),

N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),

N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),

N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)

FROM N3 AS X, N3 AS Y),

CTE As(

Select RowLogContents,[nullbit]

,[BitMap]=Convert(varbinary(1),Convert(int,Substring((REPLACE(STUFF((SELECT ',' +

(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(hex_Value, n, 1) % 2)) ELSE CONVERT(NVARCHAR(1),((SUBSTRING(hex_Value, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]

from N4 AS Nums

Join @ColumnNameAndData AS C ON n<=@BitColumnByte And [System_Type_id]=104 And bitpos=0

Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',','')),bitpos+1,1)))

FROM @ColumnNameAndData D Where [System_Type_id]=104)

Update A Set [hex_Value]=[BitMap]

from @ColumnNameAndData A

Inner Join CTE B On A.[RowLogContents]=B.[RowLogContents]

And A.[nullbit]=B.[nullbit]

/**************Check for BLOB DATA TYPES******************************/

DECLARE @Fileid INT

DECLARE @Pageid INT

DECLARE @Slotid INT

DECLARE @CurrentLSN INT

DECLARE @LinkID INT

DECLARE @Context VARCHAR(50)

DECLARE @ConsolidatedPageID VARCHAR(MAX)

DECLARE @LCX_TEXT_MIX VARBINARY(MAX)

declare @temppagedata table

(

[ParentObject] sysname,

[Object] sysname,

[Field] sysname,

[Value] sysname)

declare @pagedata table

(

[Page ID] sysname,

[File IDS] int,

[Page IDS] int,

[AllocUnitId] bigint,

[ParentObject] sysname,

[Object] sysname,

[Field] sysname,

[Value] sysname)

DECLARE @ModifiedRawData TABLE

(

[ID] INT IDENTITY(1,1),

[PAGE ID] VARCHAR(MAX),

[FILE IDS] INT,

[PAGE IDS] INT,

[Slot ID] INT,

[AllocUnitId] BIGINT,

[RowLog Contents 0_var] VARCHAR(Max),

[RowLog Length] VARCHAR(50),

[RowLog Len] INT,

[RowLog Contents 0] VARBINARY(Max),

[Link ID] INT default (0),

[Update] INT

)

DECLARE Page_Data_Cursor CURSOR FOR

/*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for deleted records of BLOB data type& Get its Slot No, Page ID & AllocUnit ID*/

SELECT LTRIM(RTRIM(Replace([Description],'Deallocated',''))) AS [PAGE ID]

,[Slot ID],[AllocUnitId],NULL AS [RowLog Contents 0],NULL AS [RowLog Contents 0],Context

FROM sys.fn_dblog(NULL, NULL)

WHERE

AllocUnitId IN

(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits

INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2

AND partitions.partition_id = allocunits.container_id)

WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))

AND Operation IN ('LOP_MODIFY_ROW') AND [Context] IN ('LCX_PFS')

AND Description Like '%Deallocated%'

/*Use this subquery to filter the date*/

AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)

WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')

AND [Transaction Name]='DELETE'

AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

GROUP BY [Description],[Slot ID],[AllocUnitId],Context

UNION

SELECT [PAGE ID],[Slot ID],[AllocUnitId]

,Substring([RowLog Contents 0],15,LEN([RowLog Contents 0])) AS [RowLog Contents 0]

,CONVERT(INT,Substring([RowLog Contents 0],7,2)),Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]

FROM sys.fn_dblog(NULL, NULL)

WHERE

AllocUnitId IN

(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits

INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)

AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2

AND partitions.partition_id = allocunits.container_id)

WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))

AND Context IN ('LCX_TEXT_MIX') AND Operation in ('LOP_DELETE_ROWS')

/*Use this subquery to filter the date*/

AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)

WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')

And [Transaction Name]='DELETE'

And CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

/****************************************/

OPEN Page_Data_Cursor

FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @hex_pageid AS VARCHAR(Max)

/*Page ID contains File Number and page number It looks like 0001:00000130.

In this example 0001 is file Number & 00000130 is Page Number & These numbers are in Hex format*/

SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX(':',@ConsolidatedPageID)) -- Seperate File ID from Page ID

SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID)) ---Seperate the page ID

SELECT @Pageid=Convert(INT,cast('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )', 'varbinary(max)')) -- Convert Page ID from hex to integer

FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)

IF @Context='LCX_PFS'

BEGIN

DELETE @temppagedata

INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 1) with tableresults,no_infomsgs;');

INSERT INTO @pagedata SELECT @ConsolidatedPageID,@fileid,@pageid,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata

END

ELSE IF @Context='LCX_TEXT_MIX'

BEGIN

INSERT INTO @ModifiedRawData SELECT @ConsolidatedPageID,@fileid,@pageid,@Slotid,@AllocUnitID,NULL,0,CONVERT(INT,CONVERT(VARBINARY,REVERSE(SUBSTRING(@LCX_TEXT_MIX,11,2)))),@LCX_TEXT_MIX,@LinkID,0

END

FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context

END

CLOSE Page_Data_Cursor

DEALLOCATE Page_Data_Cursor

DECLARE @Newhexstring VARCHAR(MAX);

--The data is in multiple rows in the page, so we need to convert it into one row as a single hex value.

--This hex value is in string format

INSERT INTO @ModifiedRawData ([PAGE ID],[FILE IDS],[PAGE IDS],[Slot ID],[AllocUnitId]

,[RowLog Contents 0_var]

, [RowLog Length])

SELECT [Page ID],[FILE IDS],[PAGE IDS],Substring([ParentObject],CHARINDEX('Slot', [ParentObject])+4, (CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4))-2 ) as [Slot ID]

,[AllocUnitId]

,Substring((

SELECT

REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')

FROM @pagedata C WHERE B.[Page ID]= C.[Page ID] And Substring(B.[ParentObject],CHARINDEX('Slot', B.[ParentObject])+4, (CHARINDEX('Offset', B.[ParentObject])-(CHARINDEX('Slot', B.[ParentObject])+4)) )=Substring(C.[ParentObject],CHARINDEX('Slot', C.[ParentObject])+4, (CHARINDEX('Offset', C.[ParentObject])-(CHARINDEX('Slot', C.[ParentObject])+4)) ) And

[Object] Like '%Memory Dump%' Order By '0x'+ LEFT([Value],CHARINDEX(':',[Value])-1)

FOR XML PATH('') ),1,1,'') ,' ','')

),1,20000) AS [Value]

,

Substring((

SELECT '0x' +REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')

FROM @pagedata C WHERE B.[Page ID]= C.[Page ID] And Substring(B.[ParentObject],CHARINDEX('Slot', B.[ParentObject])+4, (CHARINDEX('Offset', B.[ParentObject])-(CHARINDEX('Slot', B.[ParentObject])+4)) )=Substring(C.[ParentObject],CHARINDEX('Slot', C.[ParentObject])+4, (CHARINDEX('Offset', C.[ParentObject])-(CHARINDEX('Slot', C.[ParentObject])+4)) ) And

[Object] Like '%Memory Dump%' Order By '0x'+ LEFT([Value],CHARINDEX(':',[Value])-1)

FOR XML PATH('') ),1,1,'') ,' ','')

),7,4) AS [Length]

From @pagedata B

Where [Object] Like '%Memory Dump%'

Group By [Page ID],[FILE IDS],[PAGE IDS],[ParentObject],[AllocUnitId]--,[Current LSN]

Order By [Slot ID]

UPDATE @ModifiedRawData SET [RowLog Len] = CONVERT(VARBINARY(8000),REVERSE(cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Length]"),0))', 'varbinary(Max)')))

FROM @ModifiedRawData Where [LINK ID]=0

UPDATE @ModifiedRawData SET [RowLog Contents 0] =cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),0))', 'varbinary(Max)')

FROM @ModifiedRawData Where [LINK ID]=0

Update B Set B.[RowLog Contents 0] =

(CASE WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN A.[RowLog Contents 0]+C.[RowLog Contents 0]

WHEN A.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN C.[RowLog Contents 0]

WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN A.[RowLog Contents 0]

END)

,B.[Update]=ISNULL(B.[Update],0)+1

from @ModifiedRawData B

LEFT Join @ModifiedRawData A On A.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],15+14,2))))

And A.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],19+14,2))))

And A.[Link ID]=B.[Link ID]

LEFT Join @ModifiedRawData C On C.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],27+14,2))))

And C.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],31+14,2))))

And C.[Link ID]=B.[Link ID]

Where (A.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)

Update B Set B.[RowLog Contents 0] =

(CASE WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN A.[RowLog Contents 0]+C.[RowLog Contents 0]

WHEN A.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN C.[RowLog Contents 0]

WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN A.[RowLog Contents 0]

END)

--,B.[Update]=ISNULL(B.[Update],0)+1

from @ModifiedRawData B

LEFT Join @ModifiedRawData A On A.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],15+14,2))))

And A.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],19+14,2))))

And A.[Link ID]<>B.[Link ID] And B.[Update]=0

LEFT Join @ModifiedRawData C On C.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],27+14,2))))

And C.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],31+14,2))))

And C.[Link ID]<>B.[Link ID] And B.[Update]=0

Where (A.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)

UPDATE @ModifiedRawData SET [RowLog Contents 0] =

(Case When [RowLog Len]>=8000 Then

Substring([RowLog Contents 0] ,15,[RowLog Len])

When [RowLog Len]<8000 Then

SUBSTRING([RowLog Contents 0],15+6,Convert(int,Convert(varbinary(max),REVERSE(Substring([RowLog Contents 0],15,6)))))

End)

FROM @ModifiedRawData Where [LINK ID]=0

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]

--,A.[Update]=A.[Update]+1

FROM @ColumnNameAndData A

INNER JOIN @ModifiedRawData B ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],17,4))))=[PAGE IDS]

AND Convert(int,Substring([hex_value],9,2)) =B.[Link ID]

Where [System_Type_Id] In (99,167,175,231,239,241,165,98) And [Link ID] <>0

UPDATE @ColumnNameAndData SET [hex_Value]=

(CASE WHEN B.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN B.[RowLog Contents 0]+C.[RowLog Contents 0]

WHEN B.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN C.[RowLog Contents 0]

WHEN B.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN B.[RowLog Contents 0]

END)

--,A.[Update]=A.[Update]+1

FROM @ColumnNameAndData A

LEFT JOIN @ModifiedRawData B ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],5,4))))=B.[PAGE IDS] And B.[Link ID] =0

LEFT JOIN @ModifiedRawData C ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],17,4))))=C.[PAGE IDS] And C.[Link ID] =0

Where [System_Type_Id] In (99,167,175,231,239,241,165,98) And (B.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]

--,A.[Update]=A.[Update]+1

FROM @ColumnNameAndData A

INNER JOIN @ModifiedRawData B ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],9,4))))=[PAGE IDS]

And Convert(int,Substring([hex_value],3,2))=[Link ID]

Where [System_Type_Id] In (35,34,99) And [Link ID] <>0

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]

--,A.[Update]=A.[Update]+10

FROM @ColumnNameAndData A

INNER JOIN @ModifiedRawData B ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],9,4))))=[PAGE IDS]

Where [System_Type_Id] In (35,34,99) And [Link ID] =0

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]

--,A.[Update]=A.[Update]+1

FROM @ColumnNameAndData A

INNER JOIN @ModifiedRawData B ON

Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],15,4))))=[PAGE IDS]

Where [System_Type_Id] In (35,34,99) And [Link ID] =0

Update @ColumnNameAndData set [hex_value]= 0xFFFE + Substring([hex_value],9,LEN([hex_value]))

--,[Update]=[Update]+1

Where [system_type_id]=241

CREATE TABLE [#temp_Data]

(

[FieldName] VARCHAR(MAX),

[FieldValue] NVARCHAR(MAX),

[Rowlogcontents] VARBINARY(8000),

[Row ID] int

)

INSERT INTO #temp_Data

SELECT NAME,

CASE

WHEN system_type_id IN (231, 239) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) --NVARCHAR ,NCHAR

WHEN system_type_id IN (167,175) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value))) --VARCHAR,CHAR

WHEN system_type_id IN (35) THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value))) --Text

WHEN system_type_id IN (99) THEN LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) --nText

WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) --TINY INTEGER

WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) --SMALL INTEGER

WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGER

WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGER

WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100) --DATETIME

WHEN system_type_id =58 Then CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) --SMALL DATETIME

WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX),CONVERT(NUMERIC(38,20), CONVERT(VARBINARY,CONVERT(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) --- NUMERIC

WHEN system_type_id =106 THEN CONVERT(VARCHAR(MAX), CONVERT(DECIMAL(38,20), CONVERT(VARBINARY,Convert(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) --- DECIMAL

WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(8000),Reverse(hex_Value))),2) --MONEY,SMALLMONEY

WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2)) -- BIT

WHEN system_type_id =62 THEN RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value)))) --- FLOAT

When system_type_id =59 THEN Left(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8) --Real

WHEN system_type_id In (165,173) THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') -- BINARY,VARBINARY

WHEN system_type_id =34 THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') --IMAGE

WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER

WHEN system_type_id =231 THEN CONVERT(VARCHAR(MAX),CONVERT(sysname,hex_Value)) --SYSNAME

WHEN system_type_id =241 THEN CONVERT(VARCHAR(MAX),CONVERT(xml,hex_Value)) --XML

WHEN system_type_id =189 THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') --TIMESTAMP

WHEN system_type_id=98 THEN (CASE

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(Substring(hex_Value,3,Len(hex_Value)))))) -- INTEGER

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=108 THEN CONVERT(VARCHAR(MAX),CONVERT(numeric(38,20),CONVERT(VARBINARY(1),Substring(hex_Value,3,1)) +CONVERT(VARBINARY(1),Substring(hex_Value,4,1))+CONVERT(VARBINARY(1),0) + Substring(hex_Value,5,Len(hex_Value)))) --- NUMERIC

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=167 THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),Substring(hex_Value,9,Len(hex_Value))))) --VARCHAR,CHAR

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,Substring((hex_Value),3,20))) --UNIQUEIDENTIFIER

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=61 THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (Substring(hex_Value,3,LEN(hex_Value)) ))),100) --DATETIME

WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=165 THEN '0x'+ SUBSTRING((CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)'),11,LEN(hex_Value)) -- BINARY,VARBINARY

END)

END AS FieldValue

,[Rowlogcontents]

,[Row ID]

FROM @ColumnNameAndData ORDER BY nullbit

--Create the column name in the same order to do pivot table.

DECLARE @FieldName VARCHAR(max)

SET @FieldName = STUFF(

(

SELECT ',' + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) FROM syscolumns WHERE id=object_id('' + @SchemaName_n_TableName + '')

FOR XML PATH('')), 1, 1, '')

--Finally did pivot table and get the data back in the same format.

SET @sql = 'SELECT ' + @FieldName + ' FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN (' + @FieldName + ')) AS pvt'

EXEC sp_executesql @sql

'Database > Query' 카테고리의 다른 글

INDEX 상세 정보 조회 (0)	2020.11.02
프로시저 파리미터 (0)	2020.10.20
EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
Find and Kill all the Blocked Process/Query (0)	2020.09.22
오래된 커서 (0)	2020.09.11

1. EXEC와 동적(adhoc) 쿼리

자세한 내용은 MSDN의 EXECUTE 페이지를 참고하자.

(내용 겁나 길다 ㅋ)

EXECUTE 문은 SQL 문장을 실행시키는 역할을 한다.

EXEC와 동일하기에, 보통 EXEC로 많이들 사용한다.

(시스템/유저정의/CLR) 저장 프로시져를 직접 호출할 수 있으며, 문자열 쿼리를 실행시킬 수 있다.

다음 두 개의 예제는 차례대로 저장 프로시져와 문자열 쿼리를 EXEC로 실행하는 내용을 포함하고 있다.

-- #1. 저장 프로시져 호출 예제
USE AdventureWorks2012
GO
-- uspGetExployeeManagers 라는 저장 프로시져에 인자 6을 넘기며 실행시킨다
-- usp (user stored procedure)
EXEC dbo.uspGetEmployeeManagers 6
GO
-- #2. 직접 문자열 쿼리 실행 예제
USE Son
GO
DECLARE @SQLStmt VARCHAR(80) =
'SELECT * FROM UserTable WHERE ID = ''LHJ'''
EXEC(@SQLStmt)

이와 같이 EXEC 문을 사용하여, 쿼리문 내에서 다른 쿼리문을 실행시킬 수 있다.

이렇게 EXEC()를 이용해서 쿼리문을 실행하는 것을 동적(adhoc) 쿼리라고 한다.

2. 동적 쿼리를 가급적 쓰지 말아야 하는 이유

저장 프로시져는 실행 계획이 플랜 캐쉬에 캐싱된다.

즉, 한번 컴파일되면 이것이 캐쉬에 저장되고, 재사용이 되면 될수록 재컴파일이 필요없기에 효율이 올라간다.

그렇다면 저장 프로시져는 언제까지 실행 계획이 캐시에 유지될까?

저장 프로시져가 수정되거나
인덱스 통계가 재빌드되기 전까지

하지만, 동적 쿼리는 시스템에 캐싱되지 않아 호출할 때마다 컴파일을 해야하고,

이것이 결국 서버의 성능을 떨어트리게 된다.

동적 쿼리라고 해서 무조건 실행 계획에 캐슁되지 않는 것은 아니지만,

매개 변수가 하나라도 바뀌면 이것은 새로 컴파일되고 새로 캐슁된다. (EXEC의 경우 그렇다)

즉, 조금이라도 쿼리문이 바뀌면 이것은 새로 컴파일 -> 기존 캐시 미사용 -> 성능 하락이 발생하는 것이다.

동적 쿼리를 쓰는 방법은 다음 두 가지가 있다.

EXEC
EXEC sp_executesql

EXEC()는 위에서 얘기한 매개변수가 하나라도 바뀌면 재컴파일됨이 확실하다.

하지만, EXEC sp_executesql은 EXEC()와는 다르며, 조금 더 융통성 있고 효율적이라고 할 수 있다.

(자세한 내용은 MSDN의 sp_executesql 사용 페이지를 참고하자)

우선 EXEC에 비해 EXEC sp_executesql이 가지는 장점은 아래와 같다.

시스템 프로시져가 아니라 확장형 프로시져이다.
쿼리 문에 매개변수(입력/출력)를 정의할 수 있다.
매개변수 사용으로 인하여 쿼리 최적화 프로그램이 컴파일된 실행플랜을 재사용할 확률이 높아진다.

EXEC sp_executesql은 매개변수를 정의할 수 있고 이를 파라미터화 할 수 있기 때문에

쿼리문이 고정된 채 매개변수만 바뀌는 경우라면, 첫번째 실행에서 실행 계획을 캐싱, 재사용하게 된다.

이로 인해 EXEC()에 비해 많은 성능상 이득을 취할 수 있게 된다.

따라서, 어쩔 수 없이 동적 쿼리를 사용해야 한다면, EXEC() 보다 EXEC sp_executesql을 사용하도록 하자.

그리고 다음은 EXEC sp_executesql의 제약 사항이다.

현재 데이터베이스가 변경되지 않는다.
로컬 변수에 접근할 수 없다.

위 내용에 대해 아래 예제가 이해하는 데 도움을 줄 것이다.

-- 다음의 일괄처리에서 EXEC sp_executesql은 로컬 변수에 접근하지 못한다.
DECLARE @CharVariable CHAR(3) = 'abc'
EXEC sp_executesql N'PRINT @CharVariable';
GO
USE master;
GO
-- sp_executesql이 끝나고 나면 Database context가 리셋된다
EXECUTE sp_executesql N'USE Son'
GO
-- 아래 구문은 실패하는데, 이미 Database context가 master로 리셋되었기 때문이다.
SELECT * FROM Son.UserTable;
GO

3. EXEC()와 EXEC sp_executesql의 성능 비교

(다음 예제는 "http://sungedb.tistory.com/99" 에서 퍼왔습니다)

위에서 EXEC()는 매개변수만 바뀌어도 새로 컴파일되서 재사용이 불가능하다고 하였고,

EXEC sp_executesql은 매개변수가 바뀌어도 구문이 변하지 않으면, 재사용이 가능하다고 하였다.

이제 아래 예제를 통해 그 차이를 확실히 느껴보자.

1. 우선 테스트에 사용될 DB를 생성하자.

------------------------------------------------------------
-- 테스트 테이블
------------------------------------------------------------
CREATE TABLE TAB_A
(
PK_COL INT NOT NULL,
COL1 INT NOT NULL,
COL2 INT NOT NULL
);
ALTER TABLE TAB_A ADD CONSTRAINT PK_TAB_A PRIMARY KEY (PK_COL);
GO
CREATE TABLE TAB_B
(
PK_COL INT NOT NULL,
COL1 INT NOT NULL,
COL2 INT NOT NULL
);
-- 조인에 사용될 제약 조건
ALTER TABLE TAB_B ADD CONSTRAINT PK_TAB_B PRIMARY KEY (PK_COL);
ALTER TABLE TAB_B ADD CONSTRAINT FK_TAB_B FOREIGN KEY (PK_COL);
REFERENCES dbo.TAB_A(PK_COL);
GO

2. 이제 테스트 쿼리를 작성해 보자.

-- 측정하기 전에 플랜캐쉬를 초기화하자
DBCC FREEPROCCACHE;
DECLARE @SQLStmt NVARCHAR(MAX);
DECLARE @PARAM_DEF NVARCHAR(MAX);
SET @SQLStmt =
N'SELECT A.COL1, A.COL2, B.COL1, B.COL2
FROM dbo.TAB_A A
INNER JOIN dbo.TAB_B B
ON B.PK_COL = A.PK_COL
WHERE A.COL1 = @VAL;';
SET @PARAM_DEF = N'@VAL INT';
DECLARE @I INT;
SET @I = 0;
-- 같은 쿼리가 캐싱되는지 확인하기 위해서 값만 바꿔가면서 반복 실행한다.
WHILE (@I < 10)
BEGIN
-- EXEC sp_executesql
EXEC sp_executesql @SQLStmt, @PARAM_DEF, @VAL = @I; -- 값만 바꿔서 넘겨준다.
DECLARE @PARAM NVARCHAR(MAX);
SET @PARAM = CAST(@I AS NVARCHAR(MAX));
EXEC (N'SELECT A.COL1, A.COL2, B.COL1, B.COL2
FROM dbo.TAB_A A
INNER JOIN dbo.TAB_B B
ON B.PK_COL = A.PK_COL
WHERE A.COL1 = ' + @PARAM + N';'); -- 값이 바뀌면서 쿼리문 전체가 바뀐다.
SET @I = @I + 1;
END
-- 아래는 실행 계획 캐쉬의 정보를 보여준다.
SELECT cp.objtype, cp.cacheobjtype, cp.size_in_bytes, cp.refcounts, cp.usecounts, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.objtype IN ('Proc', 'Prepared', 'Adhoc')
AND st.text NOT LIKE '%SELECT cp.objtype%'
AND st.text NOT LIKE '%@BatchID%'
AND st.text NOT LIKE '%@_msparam%'
AND st.text NOT LIKE '%database%'
AND st.text NOT LIKE '%objtype%'

위 예제에서 실행 계획 캐쉬 정보는 아래와 같다.

EXEC()는 매개변수가 바뀔 때마다 새로 컴파일, 재사용이 되지 않아 use counts가 1 인 것을 확인할 수 있고,

EXEC sp_executesql은 모두 재사용되어, use_counts가 10 인 것을 확인할 수 있다.

참고 1)

DBCC는 DataBase Console Command의 약자로 DB를 매니지먼트하는데 사용된다.

자세한 내용은 MSDN의 DBCC 페이지를 살펴보자. 공부할 게 많은 페이지이다.

참고 2)

마지막 예제의 실행 계획 캐쉬의 정보를 보는 SELECT 문은 가급적 재사용 하자.

'Database > Query' 카테고리의 다른 글

프로시저 파리미터 (0)	2020.10.20
DELETE 복원 프로시저 (0)	2020.10.20
Find and Kill all the Blocked Process/Query (0)	2020.09.22
오래된 커서 (0)	2020.09.11
테이블 명세서 (0)	2020.08.29

–Find All the Blocked Processes

SELECT

spid,

status,

loginame=SUBSTRING(loginame,1,12),

hostname=SUBSTRING(hostname,1, 12),

blk = CONVERT(char(3), blocked),

dbname=SUBSTRING(DB_NAME(dbid),1, 10),

cmd,

waittype

FROM master.dbo.sysprocesses

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

–Kill all the Blocked Processes of a Database

DECLARE @DatabaseName nvarchar(50)

Set the Database Name

SET @DatabaseName = N’Datbase_Name’

Select the current Daatbase

SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)

SET @SQL = ”

SELECT @SQL = @SQL + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’

FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

and spid IN (SELECT blocked FROM master.dbo.sysprocesses)

–You can see the kill Processes ID

–SELECT @SQL

–Kill the Processes

EXEC(@SQL)

Or You can use the following script

— Script to kill all blocked processes

declare @max_count int, @count int, @sqlstring varchar(100)

declare @spid_table table (spid int NOT NULL)

INSERT @spid_table

select spid

from master.dbo.sysprocesses

where spid in (select blocked from master.dbo.sysprocesses where blocked <> 0) and blocked = 0

select @max_count = MAX(spid) FROM @spid_table

select top 1 @count = spid from @spid_table

while @count <= @max_count

begin

select @sqlstring = ‘kill ‘ + CONVERT(varchar(4), @count)

exec(@sqlstring)

print @sqlstring

IF @count = @max_count

begin

break

end

ELSE

BEGIN

select top 1 @count = spid FROM @spid_table where spid > @count

end

Similarly DBA can kill all the Processes of a Database as

— Kill all the Processes of a Database

DECLARE @DatabaseName nvarchar(50)

–Set the Database Name

SET @DatabaseName = N’Datbase_Name’

Select the current Daatbase

SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)

SET @SQL = ”

SELECT @SQL = @SQL + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’

FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

–You can see the kill Processes ID

–SELECT @SQL

–Kill the Processes

EXEC(@SQL)

—Identify the blocking query

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

-- Script to view all current processes / sessions on the server

select * from master.dbo.sysprocesses

'Database > Query' 카테고리의 다른 글

DELETE 복원 프로시저 (0)	2020.10.20
EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
오래된 커서 (0)	2020.09.11
테이블 명세서 (0)	2020.08.29
sp_who3 (0)	2020.08.29

SELECT creation_time, cursor_id, name, c.session_id, login_name   
FROM sys.dm_exec_cursors(0) AS c   
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id   
WHERE DATEDIFF(HH, c.creation_time, GETDATE()) >= 1;  -- 시간
GO

'Database > Query' 카테고리의 다른 글

EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
Find and Kill all the Blocked Process/Query (0)	2020.09.22
테이블 명세서 (0)	2020.08.29
sp_who3 (0)	2020.08.29
특정 문자열 포함하는 프로시저 (0)	2020.08.28

/****** Object:  StoredProcedure [dbo].[sp_table_Spec]    Script Date: 04/25/2014 08:16:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCedure [dbo].[sp_table_Spec]
as
-- exec sp_table_Spec

Declare @i Int, @maxi Int
Declare @j Int, @maxj Int
Declare @sr int
Declare @Output varchar(4000)
Declare @last varchar(155), @current varchar(255), @typ varchar(255), @description varchar(4000), @tablename varchar(4000)

create Table #Tables  (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000), [tablename] varchar(4000))
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(4000))
create Table #Fk(id int identity(1,1), Name varchar(155), col Varchar(155), refObj varchar(155), refCol varchar(155))
create Table #Constraint(id int identity(1,1), Name varchar(155), col Varchar(155), definition varchar(1000))
create Table #Indexes(id int identity(1,1), Name varchar(155), Type Varchar(25), cols varchar(1000))

print '
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=ks_c_5601-1987">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 14">
<link rel=File-List href="통합%20문서3.files/filelist.xml">
<style id="Styles">
<!--table
	{mso-displayed-decimal-separator:"\.";
	mso-displayed-thousand-separator:"\,";}
.font712580
	{color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;}
.xl1520912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:11.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl6320912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl6420912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:1.0pt solid windowtext;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl6520912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl6620912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl6720912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:1.0pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl6820912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl6920912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl7020912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:1.0pt solid windowtext;
	border-bottom:none;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7120912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:none;
	border-bottom:none;
	border-left:1.0pt solid windowtext;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7220912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:1.0pt solid windowtext;
	border-left:1.0pt solid windowtext;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl7320912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:1.0pt solid windowtext;
	border-left:none;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl7420912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:1.0pt solid windowtext;
	border-bottom:1.0pt solid windowtext;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7520912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:1.0pt solid windowtext;
	border-right:none;
	border-bottom:.5pt solid windowtext;
	border-left:.5pt solid windowtext;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7620912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:1.0pt solid windowtext;
	border-right:none;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7720912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:1.0pt solid windowtext;
	border-right:1.0pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7820912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:none;
	border-bottom:1.0pt solid windowtext;
	border-left:.5pt solid windowtext;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7920912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:none;
	border-bottom:1.0pt solid windowtext;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl8020912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:1.0pt solid windowtext;
	border-bottom:1.0pt solid windowtext;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl8120912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:1.0pt solid windowtext;
	background:#D9D9D9;
	mso-pattern:black none;
	white-space:normal;}
.xl8220912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:#D9D9D9;
	mso-pattern:black none;
	white-space:normal;}
.xl8320912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:1.0pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:#D9D9D9;
	mso-pattern:black none;
	white-space:normal;}
.xl8420912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:1.0pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:#D9D9D9;
	mso-pattern:black none;
	white-space:nowrap;}
.xl8520912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:1.0pt solid windowtext;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:1.0pt solid windowtext;
	background:#DAEEF3;
	mso-pattern:black none;
	white-space:normal;}
.xl8620912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:1.0pt solid windowtext;
	border-left:1.0pt solid windowtext;
	background:#DAEEF3;
	mso-pattern:black none;
	white-space:normal;}
ruby
	{ruby-align:left;}
rt
	{color:windowtext;
	font-size:8.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-char-type:none;}
-->
</style>
</head>

<body>
<div id="div" align=center x:publishsource="Excel">
'


set nocount on




--create Table #Tables  (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000), [tablename] varchar(4000))

insert into #Tables (Object_id, Name, Type, [description],  [tablename])

Select o.object_id,  '[' + s.name + '].[' + o.name + ']', 
    case when type = 'V' then 'View' when type = 'U' then 'Table' end,  
    cast(p.value as varchar(4000)),
    CONVERT(varchar(4000),p.value)
    from sys.objects o 
       left outer join sys.schemas s on s.schema_id = o.schema_id 
       left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description' and p.name = '테이블명'
--            where type in ('U', 'V') 
    where type in ('U')
--안쓰는 테이블 제외 
					and left(o.name, 2) in ('bi','co','ei','in','mk','mp','qc','sc')
--					and left(o.name, 2) in ('ac','fx')
--					and right(o.name, 5) not in ('excel','ackup') and right(o.name, 3) not in ('bak','web') 
--					and left(o.name, 2) not in ('tr','wy','as','ls','ds','dy','tb') and left(o.name, 5) not in ('cowrk','biacd')
    order by type, s.name, o.name
    
Set @maxi = @@rowcount


set @i = 1
While(@i <= @maxi)
begin
   --table header
   select @tablename = name, @description = [description] from #Tables where id = @i
      
   print '
<table border=0 cellpadding=0 cellspacing=0 width=838 style="border-collapse:
 collapse;table-layout:fixed;width:629pt">
 <col width=24 style="mso-width-source:userset;mso-width-alt:768;width:18pt">
 <col width=99 style="mso-width-source:userset;mso-width-alt:3168;width:74pt">
 <col width=194 style="mso-width-source:userset;mso-width-alt:6208;width:146pt">
 <col width=115 style="mso-width-source:userset;mso-width-alt:3680;width:86pt">
 <col width=118 style="mso-width-source:userset;mso-width-alt:3776;width:89pt">
 <col width=288 style="mso-width-source:userset;mso-width-alt:9216;width:216pt">
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 width=24 style="height:15.95pt;width:18pt"></td>
  <td class=xl6320912 width=99 style="width:74pt"></td>
  <td class=xl6320912 width=194 style="width:146pt"></td>
  <td class=xl6320912 width=115 style="width:86pt"></td>
  <td class=xl6320912 width=118 style="width:89pt"></td>
  <td class=xl6320912 width=288 style="width:216pt"></td>
 </tr>
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl8520912 width=99 style="width:74pt">Table ID</td>
  <td colspan=4 class=xl7520912 style="border-right:1.0pt solid black;
  border-left:none">'+ isnull(@tablename, '') +'</td>
 </tr>
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl8620912 width=99 style="width:74pt">Description</td>
  <td colspan=4 class=xl7820912 style="border-right:1.0pt solid black;
  border-left:none">' + isnull(@description, '') + '</td>
 </tr>
'
   --table columns
	truncate table #Columns 
   
  insert into #Columns  (Name, Type, Nullable, [description])
  --FOR 2005   
  Select c.name, 
           type_name(user_type_id) + (
           case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
              then '(' + cast(max_length as varchar) + ')' 
            when type_name(user_type_id) = 'decimal'  
                 then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar)   + ')' 
           else ''
           end            
           ), 
           case when is_nullable = 1 then 'Y' else 'N'  end,
           cast(p.value as varchar(4000))
  from sys.columns c
        inner join #Tables t on t.object_id = c.object_id
        left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id  = c.column_id and p.name = 'MS_Description' 
  where t.id = @i
  order by c.column_id
    
   Set @maxj =   @@rowcount
   set @j = 1
   
   
print '   
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl7120912>Table Columns</td>
  <td class=xl6320912></td>
  <td class=xl6320912></td>
  <td class=xl6320912></td>
  <td class=xl7020912>　</td>
 </tr>
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl8120912 width=99 style="width:74pt">Sr.</td>
  <td class=xl8220912 width=194 style="width:146pt">Name</td>
  <td class=xl8220912 width=115 style="width:86pt">Datatype</td>
  <td class=xl8220912 width=118 style="width:89pt">Nullable</td>
  <td class=xl8320912 width=288 style="width:216pt">Description</td>
 </tr>
'

   While(@j <= @maxj)
   begin
      select	@Output = '
				 <tr height=21 style="mso-height-source:userset;height:15.95pt">
				  <td height=21 class=xl1520912 style="height:15.95pt"></td>
				  <td class=xl6420912 width=99 style="width:74pt">' + Cast((@j) as varchar) + '</td>
				  <td class=xl6520912 width=194 style="width:146pt">' + isnull(name,'')  + '</td>
				  <td class=xl6520912 width=115 style="width:86pt">' +  upper(isnull(Type,'')) + '</td>
				  <td class=xl6620912 width=118 style="width:89pt">' + isnull(Nullable,'N') + '</td>
				  <td class=xl6720912 width=288 style="width:216pt">' + isnull([description],'') + '</td>
				 </tr>'
         from #Columns  where id = @j      
      print    @Output    
      Set @j = @j + 1;
   end    
   --Indexes 
   truncate table #Indexes
   
   print ' <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl7120912>Indexs</td>
  <td class=xl6320912></td>
  <td class=xl6320912></td>
  <td class=xl6320912></td>
  <td class=xl7020912>　</td>
 </tr>'  
      insert into #Indexes  (Name, type, cols)
         select i.name, case when i.type = 0 then 'Heap' when i.type = 1 then 'Clustered' else 'Nonclustered' end,  col_name(i.object_id, c.column_id)
            from sys.indexes i 
               inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id 
               inner join #Tables t on t.object_id = i.object_id
            where t.id = @i
            order by i.name, c.column_id
  

   Set @maxj =   @@rowcount
   
   set @j = 1
   set @sr = 1
   if (@maxj >0)
   begin

      print ' 
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl8120912 width=99 style="width:74pt">Sr.</td>
  <td class=xl8220912 width=194 style="width:146pt">Name</td>
  <td class=xl8220912 width=115 style="width:86pt">Type</td>
  <td class=xl8220912 width=118 style="width:89pt">Columns</td>
  <td class=xl8420912>　</td>
 </tr>' 
      set @Output = ''
      set @last = ''
      set @current = ''
      While(@j <= @maxj)
      begin
         select @current = isnull(name,'') from #Indexes  where id = @j
                
         if @last <> @current  and @last <> ''
            begin   
            print
			'
			 <tr height=21 style="mso-height-source:userset;height:15.95pt">
			  <td height=21 class=xl1520912 style="height:15.95pt"></td>
			  <td class=xl6420912 width=99 style="width:74pt">' + Cast((@sr) as varchar) + '</td>
			  <td class=xl6520912 width=194 style="width:146pt">' + @last + '</td>
			  <td class=xl6520912 width=115 style="width:86pt">' + @typ + '</td>
			  <td class=xl6620912 width=118 style="width:89pt">' + @Output  + '</td>
			  <td class=xl6720912>　</td>
			 </tr>'
            set @Output  = ''
            set @sr = @sr + 1
            end
         
            
         select @Output = @Output + cols + '<br />' , @typ = type
               from #Indexes  where id = @j
         
         set @last = @current    
         Set @j = @j + 1;
      end
      if @Output <> ''
            begin   
            print '
			  <tr height=21 style="mso-height-source:userset;height:15.95pt">
			  <td height=21 class=xl1520912 style="height:15.95pt"></td>
			  <td class=xl6420912 width=99 style="width:74pt">' + Cast((@sr) as varchar) + '</td>
			  <td class=xl6520912 width=194 style="width:146pt">' + @last + '</td>
			  <td class=xl6520912 width=115 style="width:86pt">' + @typ + '</td>
			  <td class=xl6620912 width=118 style="width:89pt">' + @Output  + '</td>
			  <td class=xl6720912>　</td>
			 </tr>'
            end
   end

    Set @i = @i + 1;
    print '
    <tr height=21 style="mso-height-source:userset;height:15.95pt">
	  <td height=21 class=xl1520912 style="height:15.95pt"></td>
	  <td class=xl6820912 width=99 style="width:74pt" style="border-top:1.0pt solid black;">　</td>
	  <td class=xl6920912 width=194 style="width:146pt" style="border-top:1.0pt solid black;">　</td>
	  <td class=xl6920912 width=115 style="width:86pt" style="border-top:1.0pt solid black;">　</td>
	  <td class=xl6920912 width=118 style="width:89pt" style="border-top:1.0pt solid black;">　</td>
	  <td class=xl6320912 style="border-top:1.0pt solid black;"></td>
	 </tr>
	 <tr height=21 style="mso-height-source:userset;height:15.95pt">
	  <td height=21 class=xl1520912 style="height:15.95pt"></td>
	  <td class=xl6820912 width=99 style="width:74pt">　</td>
	  <td class=xl6920912 width=194 style="width:146pt">　</td>
	  <td class=xl6920912 width=115 style="width:86pt">　</td>
	  <td class=xl6920912 width=118 style="width:89pt">　</td>
	  <td class=xl6320912></td>
	 </tr>'
end

print'
</table>
</div>
</body>
</html>
'
drop table #Tables
drop table #Columns
drop table #FK
drop table #Constraint
drop table #Indexes 
set nocount off

'Database > Query' 카테고리의 다른 글

Find and Kill all the Blocked Process/Query (0)	2020.09.22
오래된 커서 (0)	2020.09.11
sp_who3 (0)	2020.08.29
특정 문자열 포함하는 프로시저 (0)	2020.08.28
Full Scan, Index Scan Query (0)	2020.08.28

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_who3')
DROP PROCEDURE sp_who3
GO
CREATE PROCEDURE sp_who3 @x NVARCHAR(128) = NULL 
WITH RECOMPILE
AS
/****************************************************************************************** 
   This is a current activity query used to identify what processes are currently running 
   on the processors.  Use to first view the current system load and to identify a session 
   of interest such as blocking, waiting and granted memory.  You should execute the query 
   several times to identify if a query is increasing it's I/O, CPU time or memory granted.
   
   *Revision History
   - 31-Jul-2011 (Rodrigo): Initial development
   - 12-Apr-2012 (Rodrigo): Enhanced sql_text, object_name outputs;
								  Added NOLOCK hints and RECOMPILE option;
								  Added BlkBy column;
								  Removed dead-code.
   - 03-Nov-2014 (Rodrigo): Added program_name and open_transaction_count	column
   - 10-Nov-2014 (Rodrigo): Added granted_memory_GB
   - 03-Nov-2015 (Rodrigo): Added parameters to show memory and cpu information
   - 12-Nov-2015 (Rodrigo): Added query to get IO info
   - 17-Nov-2015 (Rodrigo): Changed the logic and addedd new parameters
   - 18-Nov-2015 (Rodrigo): Added help content
*******************************************************************************************/
BEGIN
	SET NOCOUNT ON;
	IF @x IS NULL
		BEGIN
			SELECT r.session_id, se.host_name, se.login_name, Db_name(r.database_id) AS dbname, r.status, r.command,
				   CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
					+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
					+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
				   r.blocking_session_id AS BlkBy, r.open_transaction_count AS NoOfOpenTran, r.wait_type,
				   CAST(ROUND((r.granted_query_memory / 128.0)  / 1024,2) AS NUMERIC(10,2))AS granted_memory_GB,
				   object_name = OBJECT_SCHEMA_NAME(s.objectid,s.dbid) + '.' + OBJECT_NAME(s.objectid, s.dbid),
 				   program_name = se.program_name, p.query_plan AS query_plan,
				   sql_text = SUBSTRING	(s.text,r.statement_start_offset/2,
						(CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), s.text)) * 2
							ELSE r.statement_end_offset	END - r.statement_start_offset)/2),
					r.cpu_time,	start_time, percent_complete,		
					CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
					+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
					+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
					dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
			FROM   sys.dm_exec_requests r WITH (NOLOCK) 
			JOIN sys.dm_exec_sessions se WITH (NOLOCK)
				ON r.session_id = se.session_id 
			OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s 
			OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p 
			WHERE  r.session_id <> @@SPID AND se.is_user_process = 1;
		END
	ELSE IF @x = '1'  OR @x = 'memory'
		BEGIN
			-- who is consuming the memory
			SELECT session_id, granted_memory_kb FROM sys.dm_exec_query_memory_grants WITH (NOLOCK) ORDER BY 1 DESC;
		END
	ELSE IF @x = '2'  OR @x = 'cpu'
		BEGIN
			-- who has cached plans that consumed the most cumulative CPU (top 10)
			SELECT TOP 10 DatabaseName = DB_Name(t.dbid),
							sql_text = SUBSTRING (t.text, qs.statement_start_offset/2,
										(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), t.text)) * 2
										ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),
							ObjectName = OBJECT_SCHEMA_NAME(t.objectid,t.dbid) + '.' + OBJECT_NAME(t.objectid, t.dbid),
							qs.execution_count AS [Executions], qs.total_worker_time AS [Total CPU Time],
							qs.total_physical_reads AS [Disk Reads (worst reads)],	qs.total_elapsed_time AS [Duration], 
							qs.total_worker_time/qs.execution_count AS [Avg CPU Time],qs.plan_generation_num,
								qs.creation_time AS [Data Cached], qp.query_plan
			FROM sys.dm_exec_query_stats qs WITH(NOLOCK) 
			CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
			CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
			ORDER BY DatabaseName, qs.total_worker_time DESC;
		END
	ELSE IF @x = '3'  OR @x = 'count'
		BEGIN
			-- who is connected and how many sessions it has 
			SELECT login_name, [program_name],No_of_Connections = COUNT(session_id)
			FROM sys.dm_exec_sessions WITH (NOLOCK)
			WHERE session_id > 50 GROUP BY login_name, [program_name] ORDER BY COUNT(session_id) DESC
		END
	ELSE IF @x = '4'  OR @x = 'idle'
		BEGIN
			-- who is idle that have open transactions
			SELECT s.session_id, login_name, login_time, host_name, host_process_id, status FROM sys.dm_exec_sessions AS s WITH (NOLOCK)
			WHERE EXISTS (SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id)
			AND NOT EXISTS (SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id)
		END
	ELSE IF @x = '5' OR @x = 'tempdb'
		BEGIN
			-- who is running tasks that use tempdb (top 5)
			SELECT TOP 5 session_id, request_id,  user_objects_alloc_page_count + internal_objects_alloc_page_count as task_alloc
			FROM    tempdb.sys.dm_db_task_space_usage  WITH (NOLOCK)
			WHERE   session_id > 50 ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC
		END
	ELSE IF @x = '6' OR @x = 'block'
		BEGIN
			-- who is blocking
			SELECT DB_NAME(lok.resource_database_id) as db_name,lok.resource_description,lok.request_type,lok.request_status,lok.request_owner_type
			,wat.session_id as wait_session_id,wat.wait_duration_ms,wat.wait_type,wat.blocking_session_id
			FROM  sys.dm_tran_locks lok WITH (NOLOCK) JOIN sys.dm_os_waiting_tasks wat WITH (NOLOCK) ON lok.lock_owner_address = wat.resource_address 
		END
	ELSE IF @x = '0' OR @x = 'help'
		BEGIN
			DECLARE @text NVARCHAR(4000);
			DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10);
			SET @text = N'Synopsis:' + @NewLineChar +
						N'Who is currently running on my system?'  + @NewLineChar +
						N'-------------------------------------------------------------------------------------------------------------------------------------'  + @NewLineChar +
						N'Description:'  + @NewLineChar +
						N'The first area to look at on a system running SQL Server is the utilization of hardware resources, the core of which are memory,' + @NewLineChar +
						N'storage, CPU and long blockings. Use sp_who3 to first view the current system load and to identify a session of interest.' + @NewLineChar +
						N'You should execute the query several times to identify which session id is most consuming teh system resources.' + @NewLineChar +
						N'-------------------------------------------------------------------------------------------------------------------------------------' + @NewLineChar +
						N'Parameters:'  + @NewLineChar +
						N'sp_who3 null				- who is active;' + @NewLineChar +
						N'sp_who3 1 or ''memory''  	- who is consuming the memory;' + @NewLineChar +
						N'sp_who3 2 or ''cpu''  	- who has cached plans that consumed the most cumulative CPU (top 10);'+ @NewLineChar +
						N'sp_who3 3 or ''count''  	- who is connected and how many sessions it has;'+ @NewLineChar +
						N'sp_who3 4 or ''idle'' 	- who is idle that has open transactions;'+ @NewLineChar +
						N'sp_who3 5 or ''tempdb'' 	- who is running tasks that use tempdb (top 5); and,'+ @NewLineChar +
						N'sp_who3 6 or ''block'' 	- who is blocking.'
			PRINT @text;
		END
END;
GO

'Database > Query' 카테고리의 다른 글

오래된 커서 (0)	2020.09.11
테이블 명세서 (0)	2020.08.29
특정 문자열 포함하는 프로시저 (0)	2020.08.28
Full Scan, Index Scan Query (0)	2020.08.28
모든 인덱스 다시 리빌드, 통계 업데이트 (0)	2020.08.28

DECLARE @SEARCH_TEXT NVARCHAR(MAX) = 'WEB_ORDER_REGISTER_ADDRESS';
 
SELECT B.NAME       AS NAME     -- 이름
     , B.TYPE_DESC  AS TYPE     -- 타입 구분
     , A.DEFINITION AS CONTENTS -- 내용
  FROM SYS.SQL_MODULES A WITH (NOLOCK)
  LEFT JOIN SYS.OBJECTS B WITH (NOLOCK) ON A.OBJECT_ID = B.OBJECT_ID
 WHERE DEFINITION LIKE '%' + @SEARCH_TEXT + '%'      
 ORDER BY TYPE, NAME

저작자표시

'Database > Query' 카테고리의 다른 글

테이블 명세서 (0)	2020.08.29
sp_who3 (0)	2020.08.29
Full Scan, Index Scan Query (0)	2020.08.28
모든 인덱스 다시 리빌드, 통계 업데이트 (0)	2020.08.28
INDEX 사용량 (0)	2020.08.28

SQL

Database/Query

테이블 사이즈

'Database > Query' 카테고리의 다른 글

INDEX 상세 정보 조회

'Database > Query' 카테고리의 다른 글

프로시저 파리미터

'Database > Query' 카테고리의 다른 글

DELETE 복원 프로시저

'Database > Query' 카테고리의 다른 글

EXEC와 동적(adhoc) 쿼리

'Database > Query' 카테고리의 다른 글

Find and Kill all the Blocked Process/Query

'Database > Query' 카테고리의 다른 글

오래된 커서

'Database > Query' 카테고리의 다른 글

테이블 명세서

'Database > Query' 카테고리의 다른 글

sp_who3

'Database > Query' 카테고리의 다른 글

특정 문자열 포함하는 프로시저

'Database > Query' 카테고리의 다른 글

+ Recent posts

티스토리툴바