Monday, June 2, 2014

SQL Server Database Auditing Trigger

--SETTINGS WHEN CREATING THE TRIGGER
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'SchemaAuditDBDDLTrigger')
DISABLE TRIGGER [SchemaAuditDBDDLTrigger] ON DATABASE
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'SchemaAuditDBDDLTrigger')
DROP TRIGGER [SchemaAuditDBDDLTrigger] ON DATABASE
GO

USE MyAuditDatabase
GO
/****** Object:  Table [SchemaAuditDB]    Script Date: 03/24/2014 23:40:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SchemaAuditDB') AND type in (N'U'))
drop taBLE [SchemaAuditDB]
GO

CREATE TABLE [SchemaAuditDB](
[SchemaAuditDBID] [int] IDENTITY(1,1) NOT NULL,
[AuditDate] [datetime] NOT NULL,
[LoginName] [sysname] NOT NULL,
[UserName] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[TSQL] [varchar](max) NOT NULL,
[XMLEventData] [xml] NOT NULL,
 CONSTRAINT [PK_SchemaAuditDB] PRIMARY KEY CLUSTERED
(
[SchemaAuditDBID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
USE MyDatabase
go
create TRIGGER [SchemaAuditDBDDLTrigger]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN

  SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS ON
  SET NUMERIC_ROUNDABORT OFF

declare @ContextInfo varbinary(128)
select @ContextInfo = context_info from master.dbo.sysprocesses where spid=@@SPID;

--check for recursive execution  of trigger
IF @ContextInfo = 0x1
RETURN

  DECLARE
    @EventData XML,
    @Schema sysname,
    @Object sysname,
    @EventType sysname,
    @SQL VARCHAR(max)
 
  SET @EventData = EventData()

  SET @Schema = @EventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'VARCHAR(50)')
  SET @Object = @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)')
  SET @EventType = @EventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(50)')

  INSERT MyAuditDatabase.DBO.SchemaAuditDB (AuditDate, LoginName, UserName, [Event], [Schema], Object, TSQL, [XMLEventData])
  SELECT
    GetDate(),
    SUSER_SNAME(),
    @EventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'sysname'),
    @EventType, @Schema, @Object,
    @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(max)'),
    @EventData
 
END

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

--DISABLE TRIGGER [SchemaAuditDBDDLTrigger] ON DATABASE
--GO

ENABLE TRIGGER [SchemaAuditDBDDLTrigger] ON DATABASE
GO


SQL Server Auditing Trigger ( Server and All databases scope)


/****** Object:  DdlTrigger [ServerAuditDDLTrigger]    Script Date: 06/02/2014 17:03:58 ******/
IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'ServerAuditDDLTrigger')
DROP TRIGGER [ServerAuditDDLTrigger] ON ALL SERVER
GO

USE MyAuditDatabase
GO
/****** Object:  Table [SchemaAudit]    Script Date: 03/24/2014 23:40:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'SchemaAudit') AND type in (N'U'))
DROP TABLE dbo.SchemaAudit
GO

CREATE TABLE [SchemaAudit](
[SchemaAuditID] [int] IDENTITY(1,1) NOT NULL,
[AuditDate] [datetime] NOT NULL,
[LoginName] [sysname] NOT NULL,
[UserName] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[DatabaseName] [sysname] NULL,
[ServerName] [sysname] NULL,
[TSQL] [varchar](max) NOT NULL,
[XMLEventData] [xml] NOT NULL,
 CONSTRAINT [PK_SchemaAudit] PRIMARY KEY CLUSTERED
(
[SchemaAuditID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


use master
/****** Object:  DdlTrigger [ServerAuditDDLTrigger]    Script Date: 06/02/2014 17:03:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create TRIGGER [ServerAuditDDLTrigger]
ON ALL SERVER FOR DDL_SERVER_LEVEL_EVENTS, DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN

  SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS ON
  SET NUMERIC_ROUNDABORT OFF

declare @ContextInfo varbinary(128)
select @ContextInfo = context_info from master.dbo.sysprocesses where spid=@@SPID;

--check for recursive execution  of trigger
IF @ContextInfo = 0x1
RETURN

 DECLARE
    @EventData XML,
    @Schema sysname,
    @Object sysname,
    @DatabaseName sysname,
    @ServerName sysname,
    @EventType sysname,
    @SQL VARCHAR(max)
 
  SET @EventData = EventData()

  SET @Schema = @EventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'VARCHAR(50)')
  SET @Object = @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)')
  SET @DatabaseName = @EventData.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(50)')
  SET @ServerName = @EventData.value('data(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(50)')
  SET @Object = @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)')
  SET @EventType = @EventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(50)')

  INSERT MyAuditDatabase.DBO.SchemaAudit (AuditDate, LoginName, UserName, [Event], [Schema], Object,DatabaseName,ServerName, TSQL, [XMLEventData])
  SELECT
    GetDate(),
    SUSER_SNAME(),
    @EventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'sysname'),
    @EventType, @Schema, @Object,@DatabaseName,@ServerName,
    @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(max)'),
    @EventData
 
END


GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [ServerAuditDDLTrigger] ON ALL SERVER
GO


Friday, January 31, 2014

Historical Deadlock Detection

create table #DeadlockEvents(AlertTime datetime2,DeadlockGraph  xml)

SET QUOTED_IDENTIFIER ON ;
;WITH ring_buffer ( xmlcol )
AS (
SELECT CONVERT(XML, st.target_data) as ring_buffer
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets st ON st.event_session_address = s.[address]
)
INSERT INTO dbo.#DeadlockEvents ( AlertTime, DeadlockGraph )
SELECT CAST(T2.evntdata.value('(@timestamp)[1]','varchar(24)') AS datetime2) AS [AlertTime]
,CAST(T2.evntdata.value('(data[@name="xml_report"]/value)[1]','varchar(max)') AS XML) AS deadlock_report
FROM ring_buffer
CROSS APPLY xmlcol.nodes('/RingBufferTarget/event[@name="xml_deadlock_report"]') as T2(evntdata) ;



;with cte as (
select
T2.deadlockData.value('(../victim-list/victimProcess/@id)[1]','varchar(100)')  as ProcessID,
CAST(T2.deadlockData.value('(process/@lasttranstarted)[1]','varchar(24)') AS datetime2) as lasttranstarted ,
T2.deadlockData.value('(process/inputbuf)[1]','varchar(max)') as inputbuffer1,
T2.deadlockData.value('(process/inputbuf)[2]','varchar(max)') as inputbuffer2,
T2.deadlockData.value('(process/inputbuf)[3]','varchar(max)') as inputbuffer3,
x.DeadlockGraph
from #DeadlockEvents  x
cross apply x.DeadlockGraph.nodes('/deadlock/process-list') as T2(deadlockData)
)
select * From cte
order by lasttranstarted desc

/*
find the wait resource
*/
declare @databaseName varchar(100) = db_name(12)
declare @keyValue varchar(100) = 'KEY: 12:72057594344112128 (bb6543c4f003)'--Output from deadlock graph
declare @lockres varchar(100)
declare @hobbitID bigint
select @hobbitID = convert(bigint,RTRIM(SUBSTRING(@keyValue,CHARINDEX(':',@keyValue,CHARINDEX(':',@keyValue)+1)+1,
CHARINDEX('(',@keyValue)-CHARINDEX(':',@keyValue,CHARINDEX(':',@keyValue)+1)-1)))
select @lockRes = RTRIM(SUBSTRING(@keyValue,CHARINDEX('(',@keyValue)+1,CHARINDEX(')',@keyValue)-CHARINDEX('(',@keyValue)-1))


declare @objectName sysname
declare @ObjectLookupSQL as nvarchar(max) = '
SELECT @objectName = o.name
FROM '+@databaseName+'.sys.partitions p
JOIN '+@databaseName+'.sys.indexes i ON p.index_id = i.index_id AND p.[object_id] = i.[object_id]
join '+@databaseName+'.sys.objects o on o.object_id = i.object_id
WHERE hobt_id = '+convert(nvarchar(50),@hobbitID)+'
'
exec sp_executesql @ObjectLookupSQL, N'@objectName sysname OUTPUT',@objectName=@objectName OUTPUT
select @objectName
declare @finalResult nvarchar(max) = N'select %%lockres%% ,*
from '+@databaseName+'.dbo.' + @objectName + '
where %%lockres%% = ''('+@lockRes+')''
'
exec sp_executesql @finalResult

select object_name(11424)

Tuesday, January 21, 2014

Finding out which tables aren't purged in my database


if object_id('tempdb..#tmpSQL') is not null drop table #tmpSQL
if object_id('tempdb..#tmpresults') is not null drop table #tmpresults

create table #tmpSQL(iIndex int identity(1,1) ,sqlStatement nvarchar(4000))
create table #tmpresults(tableName varchar(300),columnName varchar(300),minDate datetime)

INSERT INTO #tmpSQL(sqlStatement)
select distinct '
insert  #tmpResults
select ''' + t.name + ''', ''' + s.name + '''  ,min(' + s.name + ')  from ' +  ss.name + '.' +  t.name + '(NOLOCK)
'
from sys.columns s
inner join sys.types st
on st.user_type_id = s.user_type_id
inner join sys.tables t
on t.object_id = s.object_id
inner join sys.schemas ss
on ss.schema_id = t.schema_id
where t.object_id = s.object_id
and st.name in ('date','smalldatetime','datetime')

declare @iIndex int,@iMax int

select @iIndex = 1,@iMax=COUNT(*) from #tmpSQL

declare @sqlStatement nvarchar(4000)
set @sqlStatement = ''
WHILE @iIndex < = @iMax
BEGIN
select @sqlStatement = sqlStatement from #tmpSQL where iIndex = @iIndex
print @iIndex
declare @starTime datetime,@TimeDiff int
set @starTime = getdate()
print @sqlStatement
exec sp_executesql @sqlStatement
select @TimeDiff = datediff(ss,@starTime,GETDATE())
print @TimeDiff
 set @iIndex = @iIndex+1
END




with cte as(
select distinct tablename from #tmpresults
),cteCount as(
select ta.name, SUM(pa.rows) as numOfRows from cte
inner join
 sys.tables ta
 on cte.tableName = ta.name
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 )
 select cteCount.*,t.columnName,t.minDate From cteCount
 inner join #tmpresults t
 on cteCount.name = t.tableName
where minDate is not null
 ORDER BY numOfRows DESC

Thursday, January 2, 2014

Estimating the size of a table in SQL Server 2005 and 2008

I have read this (http://msdn.microsoft.com/en-us/library/ms178085.aspx) article and decided to provide us all a great script that can be easily executed on a specific database and extract estimated table sizes , based on NumberOfRows you will provide.
The final table size will be displayed in the Clustered_Index_Size_Display.

What's on the roadmap? Provide ability for Estimating non clustered index size ,partition tables and variable-length(max) columns data types and more.


1. How to execute the stored procedure :
    EXEC stp_GetTableSizeEstimates 1000000--Number of rows possible - reflects all tables found

    SELECT *
    fROM vew_tableSizeEstimate
    order by Clustered_Index_Size desc


2. How to setup the stored procedure? Please copy paste the script below:




IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_tableSizeEstimate]') AND type in (N'U'))
create table tbl_tableSizeEstimate(
iobject_id bigint
,table_name varchar(255)
,Fixed_Data_Size FLOAT  default(0)
,Null_Bitmap FLOAT   default(0)
,Num_Cols FLOAT  default(0)
,Num_Variable_Cols bigint  default(0)
,Max_Var_Size float  default(0)
,Row_Size float   default(0)
,Rows_Per_Page FLOAT  default(0)
,Free_Rows_Per_Page FLOAT  default(0)
,Num_Leaf_Pages FLOAT  default(0)
,Leaf_space_used FLOAT  default(0)
,Num_Variable_Key_Cols bigint  default(0)
,Max_Var_Key_Size FLOAT  default(0)
,Num_Key_Cols bigint  default(0)
,Index_Null_Bitmap bigint default(0)
,Variable_Key_Size FLOAT  default(0)
,Index_Row_Size FLOAT
,Index_Rows_Per_Page bigint
,Non_Leaf_Levels bigint default(1)
,Num_Index_Pages bigint default(0)
,Index_Space_Used FLOAT  default(0)
,Clustered_Index_Size FLOAT  default(0)
,Summand float  default(0)
)



IF NOT  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stp_GetTableSizeEstimates]') AND type in (N'P', N'PC'))
exec sp_executesql N'CREATE PROCEDURE [dbo].[stp_GetTableSizeEstimates] as select 1'
GO


ALTER procedure stp_GetTableSizeEstimates
@Num_Rows int = 1000
as
truncate table tbl_tableSizeEstimate
insert into tbl_tableSizeEstimate(iobject_id,table_name,Fixed_Data_Size,Num_Cols,Num_Variable_Cols,Max_Var_Size)
select t.object_id, t.name
,(select CEILING(ISNULL(SUM(s.max_length), 0))
from sys.columns s
inner join sys.types st
on st.user_type_id = s.user_type_id
where t.object_id = s.object_id
and st.name not like N'%var%'
) as Fixed_Data_Size
--------------------
,(select count(1)+ case when i.is_unique=0 then 1 else 0 end from sys.columns s where t.object_id = s.object_id) as Num_Cols
,(select count(1)+ case when i.is_unique=0 and COUNT(1)>0 then 1 else 0 end from sys.columns s
inner join sys.types st
on st.user_type_id = s.user_type_id
where t.object_id = s.object_id
and st.name like N'%var%'
) as Num_Variable_Cols
,(select (CEILING(ISNULL(SUM(s.max_length), 0)) ) + 2 + case when i.is_unique=0 then 4 else 0 end
from sys.columns s
inner join sys.types st
on st.user_type_id = s.user_type_id
where t.object_id = s.object_id
and st.name like N'%var%'
) as Max_Var_Size
From sys.tables t
inner join sys.indexes i
on t.object_id = i.object_id
where i.type=1
and i.index_id=1
order by t.name


--------------------
update T set Null_Bitmap = CEILING(2 + ((T.Num_Cols + 7) / 8))
From tbl_tableSizeEstimate T
inner join sys.indexes i
on T.iobject_id = i.object_id
where i.type=1
and i.index_id=1

update x set Row_Size = CEILING(Fixed_Data_Size + case when Num_Variable_Cols >0 then 2 + (Num_Variable_Cols * 2) + Max_Var_Size else 0 end  + Null_Bitmap + 4 )
From tbl_tableSizeEstimate x

update x set Rows_Per_Page = case when FLOOR(8096 / (Row_Size + 2)) = 0 then 1 else FLOOR(8096 / (Row_Size + 2)) end
From tbl_tableSizeEstimate x

update x set Free_Rows_Per_Page = FLOOR(8096 * ((100 - case when i.fill_factor=0 then 100 else i.fill_factor end) / 100) / (x.Row_Size + 2) )
From tbl_tableSizeEstimate x
inner join sys.indexes i
on x.iobject_id = i.object_id
where i.type=1
and i.index_id=1

update x set x.Num_Leaf_Pages  = CEILING(@Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page) )
From tbl_tableSizeEstimate x

update x set x.Leaf_space_used  = 8192 * Num_Leaf_Pages
From tbl_tableSizeEstimate x

-- Index Information
update x set x.Num_Variable_Key_Cols  =  (
select count(1)
from sys.index_columns ic
inner join sys.columns c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
inner join sys.types st
on st.user_type_id = c.user_type_id
where ic.index_id = i.index_id
and ic.object_id = i.object_id
and st.name like N'%var%'
)
From tbl_tableSizeEstimate x
inner join sys.indexes i
on i.object_id = x.iobject_id
where i.type=1
and i.index_id=1

update x set x.Num_Variable_Key_Cols  +=1
From tbl_tableSizeEstimate x
inner join sys.indexes i
on i.object_id = x.iobject_id
where i.type=1
and i.index_id=1
and i.is_unique = 0
and x.Num_Variable_Key_Cols >0

update x set x.Max_Var_Key_Size  = (select ISNULL(SUM(c.max_length), 0)
from sys.indexes i
inner join sys.index_columns ic
on ic.object_id = i.object_id
and ic.index_id = i.index_id
inner join sys.columns c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
inner join sys.types st
on st.user_type_id = c.user_type_id
where x.iobject_id = i.object_id
and st.name like N'%var%'
and i.type=1
and i.index_id=1
)
From tbl_tableSizeEstimate x

update x set x.Max_Var_Key_Size  +=4
From tbl_tableSizeEstimate x
inner join sys.indexes i
on i.object_id = x.iobject_id
where i.type=1
and i.index_id=1
and i.is_unique = 0
and x.Max_Var_Key_Size >0

update x set x.Num_Key_Cols  =
 (
select count(1)
from sys.index_columns ic
inner join sys.columns c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
where ic.index_id = i.index_id
and ic.object_id = i.object_id
)
From tbl_tableSizeEstimate x
inner join sys.indexes i
on i.object_id = x.iobject_id
where i.type=1
and i.index_id=1


update x set x.Num_Key_Cols  +=1
From tbl_tableSizeEstimate x
inner join sys.indexes i
on i.object_id = x.iobject_id
where i.type=1
and i.index_id=1
and i.is_unique = 0

/*
The bytes added to Max_Var_Key_Size are for tracking each variable-length column. This formula assumes that all variable-length columns are 100 percent full.
If you anticipate that a smaller percentage of the variable-length column storage space will be used, you can adjust the Max_Var_Key_Size value by that percentage to yield a more accurate estimate of the overall table size.
*/
update x set x.Variable_Key_Size = 2 + (Num_Variable_Key_Cols * 2) + Max_Var_Key_Size
From tbl_tableSizeEstimate x
where Max_Var_Key_Size > 0


update x set x.Index_Null_Bitmap  = 2 + ((Num_Key_Cols + 7) / 8)
From tbl_tableSizeEstimate x
inner join sys.indexes i
on x.iobject_id = i.object_id
inner join sys.index_columns ic
on ic.object_id = i.object_id
and ic.index_id = i.index_id
inner join sys.columns c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
inner join sys.types st
on st.user_type_id = c.user_type_id
where i.type=1
and i.index_id=1
and c.is_nullable = 1

/*
 (for row header overhead of an index row) + 6 (for the child page ID pointer)
*/
update x set x.Index_Row_Size  = (select ISNULL(SUM(c.max_length), 0)
from sys.indexes i
inner join sys.index_columns ic
on ic.object_id = i.object_id
and ic.index_id = i.index_id
inner join sys.columns c
on c.object_id = ic.object_id
and c.column_id = ic.column_id
inner join sys.types st
on st.user_type_id = c.user_type_id
where x.iobject_id = i.object_id
and st.name not like N'%var%'
and i.type=1
and i.index_id=1
) + Variable_Key_Size + Index_Null_Bitmap + 1 + 6
From tbl_tableSizeEstimate x

update x set x.Index_Rows_Per_Page = FLOOR(8096 / (Index_Row_Size + 2))
From tbl_tableSizeEstimate x

/*
Round each summand up to the nearest whole number.
As a simple example, consider an index where Num_Leaf_Pages = 1000 and Index_Rows_Per_Page = 25.
The first index level above the leaf level stores 1000 index rows,
which is one index row per leaf page, and 25 index rows can fit per page.
This means that 40 pages are required to store those 1000 index rows.
The next level of the index has to store 40 rows. This means it requires 2 pages.
The final level of the index has to store 2 rows. This means it requires 1 page.
This gives 43 non-leaf index pages. When these numbers are used in the previous formulas,
the outcome is as follows:
Non-leaf_Levels = 1 + log25 (1000 / 25) = 3
*/

declare @rc table(cCount bit) insert @rc select 1

WHILE @@ROWCOUNT>0
update x set x.Non_Leaf_Levels +=1
From tbl_tableSizeEstimate x
where (Num_Leaf_Pages / Index_Rows_Per_Page) > POWER(Index_Rows_Per_Page,Non_Leaf_Levels)
update x set x.Non_Leaf_Levels +=1
From tbl_tableSizeEstimate x

update x set x.Summand = Non_Leaf_Levels
From tbl_tableSizeEstimate x

insert @rc select 1
WHILE @@ROWCOUNT>0
begin
update x  SET Num_Index_Pages += CEILING(Num_Leaf_Pages/POWER(Index_Rows_Per_Page, Summand)) ,Summand -= 1
From tbl_tableSizeEstimate x
where Summand>0

end
update x set x.Index_Space_Used = 8192 * Num_Index_Pages
From tbl_tableSizeEstimate x
update x set x.Clustered_Index_Size = Leaf_Space_Used + Index_Space_used
From tbl_tableSizeEstimate x
GO


/****** Object:  View [dbo].[vew_tableSizeEstimate]    Script Date: 01/02/2014 10:10:57 ******/
IF   EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vew_tableSizeEstimate]'))
drop VIEW [dbo].[vew_tableSizeEstimate]
GO
create  view vew_tableSizeEstimate
as
select TABLE_NAME,iobject_id,Fixed_Data_Size,
  CASE
WHEN Fixed_Data_Size < 1024 THEN CAST(CAST(ROUND(Fixed_Data_Size, 3) AS NUMERIC(16,0)) AS VARCHAR(16)) + ' B'
WHEN Fixed_Data_Size < 1024*1024 THEN CAST(CAST(ROUND(Fixed_Data_Size/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' KB'
WHEN Fixed_Data_Size < 1024*1024*1024 THEN CAST(CAST(ROUND(Fixed_Data_Size/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' MB'
ELSE CAST(CAST(ROUND(Fixed_Data_Size/1024/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' GB'
END AS Fixed_Data_Size_Display
,Null_Bitmap
,Num_Cols
,Num_Variable_Cols
, CASE
WHEN Max_Var_Size < 1024 THEN CAST(CAST(ROUND(Max_Var_Size, 3) AS NUMERIC(16,0)) AS VARCHAR(16)) + ' B'
WHEN Max_Var_Size < 1024*1024 THEN CAST(CAST(ROUND(Max_Var_Size/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' KB'
WHEN Max_Var_Size < 1024*1024*1024 THEN CAST(CAST(ROUND(Max_Var_Size/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' MB'
ELSE CAST(CAST(ROUND(Max_Var_Size/1024/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' GB'
END AS Max_Var_Size
, CASE
WHEN Row_Size < 1024 THEN CAST(CAST(ROUND(Row_Size, 3) AS NUMERIC(16,0)) AS VARCHAR(16)) + ' B'
WHEN Row_Size < 1024*1024 THEN CAST(CAST(ROUND(Row_Size/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' KB'
WHEN Row_Size < 1024*1024*1024 THEN CAST(CAST(ROUND(Row_Size/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' MB'
ELSE CAST(CAST(ROUND(Row_Size/1024/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' GB'
END AS Row_Size
,Rows_Per_Page
,Free_Rows_Per_Page
,Num_Leaf_Pages
,Leaf_space_used
,  CASE
WHEN Leaf_space_used < 1024 THEN CAST(CAST(ROUND(Leaf_space_used, 3) AS NUMERIC(16,0)) AS VARCHAR(16)) + ' B'
WHEN Leaf_space_used < 1024*1024 THEN CAST(CAST(ROUND(Leaf_space_used/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' KB'
WHEN Leaf_space_used < 1024*1024*1024 THEN CAST(CAST(ROUND(Leaf_space_used/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' MB'
ELSE CAST(CAST(ROUND(Leaf_space_used/1024/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' GB'
END AS Leaf_space_used_display
,Num_Variable_Key_Cols
, CASE
WHEN Max_Var_Key_Size < 1024 THEN CAST(CAST(ROUND(Max_Var_Key_Size, 3) AS NUMERIC(16,0)) AS VARCHAR(16)) + ' B'
WHEN Max_Var_Key_Size < 1024*1024 THEN CAST(CAST(ROUND(Max_Var_Key_Size/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' KB'
WHEN Max_Var_Key_Size < 1024*1024*1024 THEN CAST(CAST(ROUND(Max_Var_Key_Size/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' MB'
ELSE CAST(CAST(ROUND(Max_Var_Key_Size/1024/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' GB'
END AS Max_Var_Key_Size
,Num_Key_Cols
,Index_Null_Bitmap
, CASE
WHEN Variable_Key_Size < 1024 THEN CAST(CAST(ROUND(Variable_Key_Size, 3) AS NUMERIC(16,0)) AS VARCHAR(16)) + ' B'
WHEN Variable_Key_Size < 1024*1024 THEN CAST(CAST(ROUND(Variable_Key_Size/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' KB'
WHEN Variable_Key_Size < 1024*1024*1024 THEN CAST(CAST(ROUND(Variable_Key_Size/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' MB'
ELSE CAST(CAST(ROUND(Variable_Key_Size/1024/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' GB'
END AS Variable_Key_Size
, CASE
WHEN Index_Row_Size < 1024 THEN CAST(CAST(ROUND(Index_Row_Size, 3) AS NUMERIC(16,0)) AS VARCHAR(16)) + ' B'
WHEN Index_Row_Size < 1024*1024 THEN CAST(CAST(ROUND(Index_Row_Size/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' KB'
WHEN Index_Row_Size < 1024*1024*1024 THEN CAST(CAST(ROUND(Index_Row_Size/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' MB'
ELSE CAST(CAST(ROUND(Index_Row_Size/1024/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' GB'
END AS Index_Row_Size
,Index_Rows_Per_Page
,Non_Leaf_Levels
,Num_Index_Pages
, Index_Space_Used, CASE
WHEN Index_Space_Used < 1024 THEN CAST(CAST(ROUND(Index_Space_Used, 3) AS NUMERIC(16,0)) AS VARCHAR(16)) + ' B'
WHEN Index_Space_Used < 1024*1024 THEN CAST(CAST(ROUND(Index_Space_Used/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' KB'
WHEN Index_Space_Used < 1024*1024*1024 THEN CAST(CAST(ROUND(Index_Space_Used/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' MB'
ELSE CAST(CAST(ROUND(Index_Space_Used/1024/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' GB'
END AS Index_Space_Used_Display
,Clustered_Index_Size, CASE
WHEN Clustered_Index_Size < 1024 THEN CAST(CAST(ROUND(Clustered_Index_Size, 3) AS NUMERIC(16,0)) AS VARCHAR(16)) + ' B'
WHEN Clustered_Index_Size < 1024*1024 THEN CAST(CAST(ROUND(Clustered_Index_Size/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' KB'
WHEN Clustered_Index_Size < 1024*1024*1024 THEN CAST(CAST(ROUND(Clustered_Index_Size/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' MB'
ELSE CAST(CAST(ROUND(Clustered_Index_Size/1024/1024/1024, 3) AS NUMERIC(16,3)) AS VARCHAR(16)) + ' GB'
END AS Clustered_Index_Size_Display
from tbl_tableSizeEstimate
GO