Table Information

The below script provides a complete possible information on a table.
/*
First Resultset(Table Information)
Table Name
ObjectId
Table Type
Is replicated table or not
Row count of the table

Second Resultset(Column information)

Third Resultset(Index Information)
Name, Id, Seeks, scans, updates, lock information, type of index, constraint information, fill factor and partition number

Forth Resultset(Detail index information)
Fifth Resultset(Detail Statistics information)
Sixth Resultset(Detail relation key information)
*/

Declare @TableName Varchar(100)
Set @TableName = 'TableName'

Select @TableName,A.Object_Id,Case When A.index_id=0 Then 'Heap' Else 'Clustered Table' End ,
  Case When B.objId is null Then 'Not Replicated' Else 'Replicated' End Is_Replicated,C.row_count
  From sys.partitions A
  Left Join sys.dm_db_partition_stats C On A.object_id = C.object_id and c.index_id in(0,1)
  Left Join sysarticles B On A.object_id = B.Objid
  Where A.index_id <2 and A.object_id = Object_Id(@TableName)
  
Select column_id,name,TYPE_NAME(user_type_id),max_length,precision,scale,Case When Is_Nullable =0 Then 'No' Else 'Yes' End 
 , Is_Identity From sys.columns where object_id = OBJECT_ID(@TableName)

 
 Create Table #Temp_Index_Details
 (
  [Object_Id]  BigInt,
  [Object_Name] Varchar(100),
  Index_ID  Int,
  Index_Name  Varchar(100),
  User_Seeks  BigInt,
  User_Scans  BigInt,
  User_Lookups BigInt,
  Index_Rows  Bigint,
  User_Updates BigInt,
  Index_Lock_Attempt_Count BigInt,
  Index_Lock_Promotion_Count BigInt,
  page_lock_wait_count  BigInt,
  page_lock_wait_in_ms  BigInt,
  [FillFactor]  Int,
  Constraint_Type VarChar(100),
  Partition_Number BigInt
 ) 
 
 Insert Into #Temp_Index_Details ([Object_Id]  ,
  [Object_Name] ,
  Index_ID  ,
  Index_Name  ,
  User_Seeks  ,
  User_Scans  ,
  User_Lookups ,
  Index_Rows  ,
  User_Updates ,
  [FillFactor] ,
  Constraint_Type )
 SELECT u.object_id,OBJECT_NAME(u.object_id) , i.indid
 , i.name , u.user_seeks , u.user_scans
 , u.user_lookups , i.rowcnt , u.user_updates,i.OrigFillFactor
 , k.[type] AS [constraint type]
 FROM sys.dm_db_index_usage_stats u
 INNER JOIN sys.sysindexes i ON u.object_id = i.id AND u.index_id = i.indid
 LEFT OUTER JOIN sys.key_constraints k
 ON i.id = k.parent_object_id AND i.indid = k.unique_index_id
 WHERE u.database_id = db_id() and OBJECT_NAME(u.object_id)=@TableName
 ORDER BY OBJECT_NAME(u.object_id), i.name, u.user_updates DESC ;
 
 

 Update B Set B.Partition_Number = A.partition_number, 
    B.Index_Lock_Attempt_Count = index_lock_promotion_attempt_count,
    B.Index_Lock_Promotion_Count = A.index_lock_promotion_count ,
    B.page_lock_wait_count = A.page_lock_wait_count, 
    B.page_lock_wait_in_ms = A.page_lock_wait_in_ms
 FROM sys.dm_db_index_operational_stats
    (db_id('Cognizant20'), object_id(@TableName), NULL, NULL) A
    Inner Join #Temp_Index_Details B On A.object_id = B.Object_Id and A.index_id = B.Index_ID


 Select [Object_Name], Index_ID, Index_Name, User_Seeks, User_Scans, User_Lookups, Index_Rows, User_Updates,
   Index_Lock_Attempt_Count, Index_Lock_Promotion_Count, page_lock_wait_count, page_lock_wait_in_ms,
   [FillFactor], Constraint_Type, Partition_Number From #Temp_Index_Details

 Select A.name IndexName,A.index_id,c.name ColumnName,
   Case When System_type_id = User_Type_id Then Type_Name(System_type_id) Else Type_Name(User_Type_id) End
   ,A.type_desc,is_identity,is_included_column,is_replicated
   ,Is_Unique,Is_Primary_Key,IS_Unique_Constraint,Fill_factor,is_Padded,is_replicated,is_nullable,is_computed
   
 From sys.indexes A 
 Inner Join sys.index_columns B On A.object_id = B.object_id And A.index_id = B.index_id
 Inner Join sys.columns C On c.object_id = B.object_id  And C.column_id  = B.column_id
 Where A.Object_ID = OBJECT_ID(@TableName)
 order by A.Index_id,Is_included_Column,Key_ordinal asc
 
 --Stats information
 Select A.object_id,A.name,A.stats_id,C.column_id,C.name,A.auto_created,user_created,has_filter,STATS_DATE(A.object_id,A.stats_id) 
 From sys.stats A
 Inner Join sys.stats_columns B On A.object_id = B.object_id and A.stats_id = B.stats_id
 Inner Join sys.columns C On B.column_id = C.column_id and A.object_id = C.object_id
 where A.object_id = OBJECT_ID(@Tablename)


 
 --Primary Key and Foreign Key information
 SELECT tc.TABLE_NAME AS PrimaryKeyTable,
 tc.CONSTRAINT_NAME AS PrimaryKey,
 COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
 COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
 LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
 WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY' and OBJECT_ID(tc.TABLE_NAME) = OBJECT_ID(@Tablename)
 ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME
  
  
  Drop Table #Temp_Index_Details

Index size on a table

It is often a requirement to understand the size of indexes on a table.
Here is a script shared by one of my friend at my workspace... looks very useful!!!

DECLARE @OBJECT_NAME  VARCHAR(255) = 'trn_defect_tracking_history';
DECLARE @temp         TABLE
        (
            indexID BIGINT,
            objectId BIGINT,
            index_name NVARCHAR(MAX),
            used_page_count BIGINT,
            pages BIGINT
        )
--Insert into temp table
INSERT INTO @temp
SELECT P.index_id,
       P.OBJECT_ID,
       I.name,
       SUM(used_page_count),
       SUM(
           CASE 
                WHEN (p.index_id < 2) THEN (
                         in_row_data_page_count + lob_used_page_count + 
                         row_overflow_used_page_count
                     )
                ELSE lob_used_page_count + row_overflow_used_page_count
           END
       )
FROM   sys.dm_db_partition_stats P
       INNER JOIN sys.indexes I
            ON  I.index_id = P.index_id
            AND I.OBJECT_ID = P.OBJECT_ID
WHERE  p.OBJECT_ID = OBJECT_ID(@OBJECT_NAME)
GROUP BY
       P.index_id,
       I.Name,
       P.OBJECT_ID;
SELECT index_name INDEX_NAME,
       LTRIM(
           STR(
               (
                   CASE 
                        WHEN used_page_count > pages THEN (used_page_count - pages)
                        ELSE 0
                   END
               ) * 8,
               15,
               0
           ) + ' KB'
       ) INDEX_SIZE
FROM   @temp T
GO

Identity Saturation

Identity saturation is one of a major check we need to have as a DBA for a big projects.The below script would detail the curretn identity and the percentage used for all table objects of a database.

SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +  QUOTENAME(t.name) AS TableName, 
 c.name AS ColumnName,
 TYPE_NAME(c.system_type_id) AS 'DataType',
 IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) AS CurrentIdentityValue,
 CASE c.system_type_id
  WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 9223372036854775807
  WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 2147483647
  WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 32767
  WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 255
 END AS 'PercentageUsed' 
FROM sys.columns AS c 
 INNER JOIN
 sys.tables AS t 
 ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1
ORDER BY PercentageUsed DESC

Hide/Show Tables from Object Explorer

Just thought of sharing an information about how to hide/show tables from your object explorer. The below query has to cut and paste in your query and “CNTRL+SHIFT+M” to input your parameters. One more thing… CNTRL+SHIFT+M work like a programming language giving us an opportunity to input the parameter in query analyser.

--To Hide the object

EXEC sp_addextendedproperty 

@name = N'microsoft_database_tools_support', 

@value = '<Hide , 1 Or 0, ?>', 

@level0type ='schema', 

@level0name ='<Schema Name, sysname, dbo>',

@level1type = 'table', 

@level1name = N'<Table Name, sysname, ?>'

--To Show the object

EXEC sp_dropextendedproperty 

@name = N'microsoft_database_tools_support', 

@level0type ='schema', 

@level0name ='<Schema Name, sysname, dbo>',

@level1type = 'table', 

@level1name = N'<Table Name, sysname, ?>'

Finding out blocks/locks in your db(Mail Notification)

Blocking and locking is a main area for performance tuning and to ensure a good health of production system. Here is a script to notify DBAs about the locking occurs in the production system.It uses service brokers to notify the lock information.

As a first step, we need to set a threshold value for blocked processes. This can be set using sp_configure as below:

--Set a throshold for blocked process to 10 Sec
sp_configure 'blocked process threshold (s)',10

Once we set a threshold value for the blocking, the next step is to create a queue and service for the event to put an entry in the queue.

--Create a Queue for the event notification
CREATE QUEUE DBA_Prod_Lock_Queue
--Create a Service on queue for the event notification
CREATE SERVICE DBA_Prod_Lock_Service
ON QUEUE DBA_Prod_Lock_Queue ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] )

We need to set up a event notification to notify the event of blocking for the threshold.

--Create an event notification for the blocked process report
CREATE EVENT NOTIFICATION DBA_Prod_Notify_Locks
ON SERVER
WITH fan_in
FOR blocked_process_report
TO SERVICE 'DBA_Prod_Lock_Service', 'current database';

Verify the messages are coming to the queue whenever a lock exceeds a threshold and the event notification.

SELECT cast( message_body as xml ), *
FROM DBA_Prod_Lock_Queue
SELECT * FROM sys.server_event_notifications

The below table is a physical table in our local db to hold the event notification further analysis.

Create Table DBA_ProductionMonitor_EventLockInformation
(
SeqID BigInt Identity(1,1),
MessageBody XML,
DatabaseID Int,
Process XML,
Is_Notified Bit Default(0)
)

The below procedure recieve the messages from the queue and push into a local table to proceed with our functionality.

Create Proc usp_DBA_ProductionMonitor_ServiceProc
AS
Begin
DECLARE @msgs TABLE ( message_body xml not null,
message_sequence_number int not null );
RECEIVE message_body, message_sequence_number
FROM DBA_Prod_Lock_Queue
INTO @msgs;
Insert Into DBA_ProductionMonitor_EventLockInformation(MessageBody,DatabaseID,Process)
SELECT message_body,
DatabaseId = cast( message_body as xml ).value( '(/EVENT_INSTANCE/DatabaseID)[1]', 'int' ),
Process = cast( message_body as xml ).query( '/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process' )
FROM @msgs
ORDER BY message_sequence_number
End

The below procedure is used to extract the relevant information from the local table where the messages been pushed from queue and send a mail for lock notification.

Alter Procedure usp_DBA_ProductionMonitor_LockInformation
As
Begin
declare @body varchar(max) =''
Create Table #Temp_SeqIds (SeqId Int)
Insert into #Temp_SeqIds Select SeqID From DBA_ProductionMonitor_EventLockInformation Where Is_Notified=0
IF Exists(Select 1 From #Temp_SeqIds)
Begin
set @body = cast( (
select td =
Cast(BlockedSPID as Varchar(50))
+ ''
+ Cast(BlockedStatus as Varchar(MAX))
+ ''
+ CAST( BlockedCommand as Varchar(MAX))
+ ''
+ Cast(BlockedHostName as Varchar(50))
+ ''
+ Cast(BlockedLoginName as Varchar(MAX))
+ ''
+ CAST( BlockingSPID as Varchar(MAX))
+ ''
+ Cast(BlockingStatus as Varchar(MAX))
+ ''
+ CAST( BlockingCommand as Varchar(MAX))
+ ''
+ Cast(BlockingHostName as Varchar(50))
+ ''
+ Cast(BlockingLoginName as Varchar(MAX))
+ ''
+ CAST( DataBaseID as Varchar(MAX))
+ ''
+ CAST( ServerName as Varchar(MAX))
+ ''
+ CAST( StartTime as Varchar(MAX))
+ ''
+ CAST( EndTime as Varchar(MAX))
from (
SELECT
--Blocked Information
BlockedSPID=MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@spid)[1]', 'varchar(100)' ) ,
BlockedStatus=MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@status)[1]', 'varchar(100)' ) ,
BlockedCommand=MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process)[1]', 'varchar(max)' ) ,
BlockedHostName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@hostname)[1]', 'varchar(100)' ) ,
BlockedLoginName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@loginname)[1]', 'varchar(100)' ) ,
--Blocking Information
BlockingSPID = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@spid)[1]', 'varchar(100)' ) ,
BlockingStatus = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@status)[1]', 'varchar(100)' ) ,
BlockingCommand = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process)[1]', 'varchar(max)' ) ,
BlockingHostName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@hostname)[1]', 'varchar(100)' ) ,
BlockingLoginName = MessageBody.value( '(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@loginname)[1]', 'varchar(100)' ) ,
--General Information
DataBaseID = MessageBody.value( '(/EVENT_INSTANCE/DatabaseID)[1]', 'int' ) ,
ServerName = MessageBody.value( '(/EVENT_INSTANCE/ServerName)[1]', 'varchar(100)') ,
StartTime = MessageBody.value( '(/EVENT_INSTANCE/StartTime)[1]', 'datetime' ) ,
EndTime = MessageBody.value( '(/EVENT_INSTANCE/EndTime)[1]', 'datetime' )
FROM DBA_ProductionMonitor_EventLockInformation A
Inner Join #Temp_SeqIds B On A.SeqID = B.SeqId
) as d
for xml path( 'tr' ), type ) as varchar(max) )
set @body = ' '
+ ' '
+ ' '
+ ' '
+ ' '
+ ''
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '
Blocked SPID
Blocked Status
Blocked Command
Blocked HostName
Blocked LoginName
Blocking SPID
Blocking Status
Blocking Command
Blocking HostName
Blocking LoginName
Database ID
Server Name
Start Time
End Time
'
print @body
EXEC msdb.dbo.sp_send_dbmail @PROFILE_NAME = '',
@recipients='',
@subject = 'DBA Notification - Production Lock Occured.',
@body = @body,
@body_format = 'HTML' ;
Update A Set Is_Notified = 1 From DBA_ProductionMonitor_EventLockInformation A
Inner Join #Temp_SeqIds B On A.SeqID = B.SeqId
End

The below script to enable and disable the queue to maintain the functionality.

--Disable Queue
ALTER QUEUE [dbo].[DBA_Prod_Lock_Queue] WITH STATUS = OFF , RETENTION = OFF , ACTIVATION ( STATUS = OFF , MAX_QUEUE_READERS = 0 , EXECUTE AS OWNER )
--Enable Queue
ALTER QUEUE [dbo].[DBA_Prod_Lock_Queue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = OFF , MAX_QUEUE_READERS = 0 , EXECUTE AS OWNER )

Hope the script would be useful for all of us!!!

Calculating Working days

Here is a script for finding out the calculating the work days (excluding the saturdays and sundays). The script can be altered also to accomodate local holidays in a separate table too. Only thing is to substract the count of the table for that period.

Declare @StartDate Date,  @EndDate Date
Set @StartDate = Getdate()-20
Set @EndDate = Getdate()

SELECT  (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

Removing multiple spaces in data

I am certain some of us would have really come across a situation that the spaces between data becomes annoying and needs to be removed on request. Many of the solutions have come across multiple blogs and sites with while loop or cursor was really made to think Is there any way???



Hmm!! On searching my tressure, after a while, found something really interested(I dont really know to whome should give the credit, ofcourse it should go the person.) . The idea was really good and quite simple.Sometimes we do take the things very complicated and dont think about a simple solution.Here it is .......

Sequence Numbers in SQL server

Have you ever thoguht of generating sequential numbers? I guess, many of us had atleast once a scenario atleast for our reporting purpose to think about sequencing the records.

Here am going to explain how do I come across with various SQL server versions. We have something like IDENTITY (I will explain little more about this later) in all of SQL versions available. It is a kind of methodology given by SQL server to generate sequencing. How about when you do have scenario to generate the
sequence while you write a procedure.Here we go!!!

SQL Server 2000

SELECT
RowNumber = IDENTITY(INT,1,1)
,c.LastName
,c.FirstName
INTO #Customer_RowID
FROM SalesLT.Customer c
ORDER BY c.LastName ASC
SELECT * FROM #Customer_RowID

SQL Server 2005 and 2008

SELECT PurchaseID, PurchaseDate, ROW_NUMBER() OVER (ORDER BY PurchaseDate) AS 'RowNumber' FROM PurchaseOrder

SQL Server -(Denali)
In addition to ROW_NUMBER, there is something called Sequence introduced in Denali Version. I have not got any personal experience with the same.Hence am just giving a clue...

Rebuild tables in a database

The below script will do a rebuilding of indexes of all tables in a database.

We can explicitly mention the below features for this procedure:
1. Excluded list of tables
2. MAXDOP can be specified.
3. Fillfactor can be specified(by default it takes from the system catalogs.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[RebuildAllIndexes]
AS
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @indexname sysname;
DECLARE @maxdop tinyint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @infomessage varchar(1000);
DECLARE @fillfactor tinyint;
DECLARE @returncode tinyint;
DECLARE @dbname sysname;
DECLARE @localerror integer;
DECLARE @ExcludeTableList varchar(100)

SET @ExcludeTableList = ''+','
SET @dbname = DB_NAME()

-- Set MAXDOP default value
IF (@maxdop IS NULL)
BEGIN
SET @maxdop = (SELECT CONVERT(smallint, value) FROM master.sys.configurations (NOLOCK) WHERE name = 'max degree of parallelism')
END
SET @returncode = 0

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
IF OBJECT_ID('tempdb..#work_to_do') IS NOT NULL
BEGIN
DROP TABLE #work_to_do
END
CREATE TABLE #work_to_do(
[objectid] [int] NULL,
[indexid] [int] NULL,
[fill_factor] [int] NOT NULL
)
-- Get indexes that meet our criteria
INSERT INTO #work_to_do
SELECT o.object_id,i.index_id, CASE i.fill_factor WHEN 0 THEN 100 ELSE i.fill_factor END AS fill_factor
FROM sys.indexes i (NOLOCK) INNER JOIN sys.objects o(nolock) ON i.object_id = o.object_id
WHERE i.index_id > 0 AND o.type ='U' AND CHARINDEX(o.name+',',@ExcludeTableList) = 0
ORDER BY O.name,i.index_id
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @fillfactor; --@partitionnum, @frag, ;
IF @@FETCH_STATUS < objectname =" QUOTENAME(o.name)," schemaname =" QUOTENAME(s.name)" schema_id =" o.schema_id" object_id =" @objectid;" schemaname =" '[sys]')" indexname =" QUOTENAME(name)" object_id =" @objectid" index_id =" @indexid;" command =" N'ALTER" command =" @command" command =" @command" fillfactor =" '" command =" @command" maxdop =" '" command =" @command" infomessage =" 'Executing" localerror =" @@ERROR"> 0
BEGIN
SET @infomessage = 'Reindex failed with error ' + CAST(@localerror AS varchar(10)) + '.'
RAISERROR (@infomessage, 11, 1) WITH LOG, NOWAIT
SET @returncode = @returncode + 1
END
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
RETURN @returncode

Split function to get values from Comma separated string

Split function is one of the main string function to split a list of items separated by a character in our daily functions.

1. Using While loop

ALTER FUNCTION [dbo].[Split]
( @List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
( Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END