SQL Server Developer Zone

Week Number in a Month

2015-09-28T21:54:00.001-07:00

Here are 2 different ways, both are assuming the week starts on monday

If you want weeks to be whole, so they belong to the month in which they start: So saturday 2012-09-01 and sunday 2012-09-02 is week 4 and monday 2012-09-03 is week 1 use this:

declare @date datetime = '2012-09-01'
select datepart(day, datediff(day, 0, @date)/7 * 7)/7 + 1

If your weeks cut on monthchange so saturday 2012-09-01 and sunday 2012-09-02 is week 1 and monday 2012-09-03 is week 2 use this:

declare @date datetime = '2012-09-01'
select datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, @date), 0)), 0), @date - 1) + 1

ALLOW_SNAPSHOT_ISOLATION & SET READ_COMMITTED_SNAPSHOT ON

2015-09-22T03:37:00.002-07:00

Kim Tripp (one of the programmers of SQL Server and a integral part of SQLSkills) goes through exactly what you stated in the MCM videos on Snapshot Isolation. Fast fwd to 41:45 in the video to get to the part where she answers your question.

If you use ALLOW_SNAPSHOT_ISOLATION make sure you use SET TRANSACTION ISOLATION LEVEL SNAPSHOT in your code, otherwise you will not get any of the benefits.

If you set SET READ_COMMITTED_SNAPSHOT ON, then there is no need to modify any code. MS SQL Server automatically applies snapshot isolation for that table.

I haven't tested to see what happens if you ask for a different isolation level in your code, I suspect it will overwrite this option but test it first.

A quick look at performance overhead using Snapshot Isolation.

Good article on how snapshot isolation can change the expected behavior of your app. It shows examples of how a update statement and a select statement might return totally different and unexpected results.

Database Configuration Option

2015-09-14T22:20:00.002-07:00

AUTO CLOSE OPTION SHOULD BE OFF

Auto Close deallocates all objects from memory for a specified database.

Query:
SELECT name, is_auto_close_on FROM Sys.Databases

Alter database DB set AUTO_CLOSE OFF;

AUTO SHRINK OPTION SHOULD BE OFF

Auto Shrink automatically shrinks DB every 30 minutes , but can cause severe fragmentation.

Query:
SELECT name, is_auto_shrink_on FROM Sys.Databases

Alter database DB set AUTO_SHRINK OFF;

RECOVERY MODELS

Query:
SELECT name, recovery_model_desc FROM Sys.Databases

Alter database DB set recovery SIMPLE;

There are 3 types of Recovery Model:
1. SIMPLE - No Log Backups, Full Only.
2. BULK_LOGGED - Logs and Differentials, But Not PiT*.
3. FULL - Adds PiT*.
* PiT - Point in Time.

STATISTICS

Statistics used by SQL Server to decide which index to use and needs to be maintained to be effective. (Statistics rebuild after 500 records and then table growth by 20%).

There are 2 types of Statistics:

1. Synchronous - forces query to wait when stats are out of date.
2. Asynchronous - allow query to continue with old stats and build new ones in the background.

So, Asynchronous Statistics should be ON.

Query:
SELECT name, is_auto_update_stats_on,is_auto_update_stats_async_on FROM Sys.Databases

Alter database DB set AUTO_UPDATE_STATISTICS_ASYNC ON;

Script all Primary Keys, Unique Constraints and Foreign Keys in a SQL Server database using T-SQL

2015-09-14T21:42:00.001-07:00

SQL Server Primary Key and Unique Constraint Creation Script

The following script is for the creation of all Primary Keys and Unique Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS.
declare @SchemaName varchar(100)
declare @TableName varchar(256)
declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique_constraint varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int 
declare @IsIncludedColumn int
declare @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)
declare @is_primary_key varchar(100)

declare CursorIndex cursor for
 select schema_name(t.schema_id) [schema_name], t.name, ix.name,
 case when ix.is_unique_constraint = 1 then ' UNIQUE ' else '' END 
    ,case when ix.is_primary_key = 1 then ' PRIMARY KEY ' else '' END 
 , ix.type_desc,
  case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end
 + case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end
 + case when ix.allow_row_locks=1 then  'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end
 + case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end
 + case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end
 + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions
 , FILEGROUP_NAME(ix.data_space_id) FileGroupName
 from sys.tables t 
 inner join sys.indexes ix on t.object_id=ix.object_id
 where ix.type>0 and  (ix.is_primary_key=1 or ix.is_unique_constraint=1) --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
 order by schema_name(t.schema_id), t.name, ix.name
open CursorIndex
fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName
while (@@fetch_status=0)
begin
 declare @IndexColumns varchar(max)
 declare @IncludedColumns varchar(max)
 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 (ix.is_primary_key=1 or ix.is_unique_constraint=1)
 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  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
  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
--  print @IndexColumns
--  print @IncludedColumns

set @TSQLScripCreationIndex =''
set @TSQLScripDisableIndex =''
set  @TSQLScripCreationIndex='ALTER TABLE '+  QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ ' ADD CONSTRAINT ' +  QUOTENAME(@IndexName) + @is_unique_constraint + @is_primary_key + +@IndexTypeDesc +  '('+@IndexColumns+') '+ 
 case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'  

print @TSQLScripCreationIndex
print @TSQLScripDisableIndex

fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName

end
close CursorIndex
deallocate CursorIndex

Script to Drop all SQL Server Primary Key and Unique Constraints

The following script is to drop all Primary Keys and Unique Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE DROP SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS.
DECLARE @SchemaName VARCHAR(256)
DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @TSQLDropIndex VARCHAR(MAX)

DECLARE CursorIndexes CURSOR FOR
SELECT  schema_name(t.schema_id), t.name,  i.name 
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id= i.object_id
WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
and (is_primary_key=1 or is_unique_constraint=1)

OPEN CursorIndexes
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
WHILE @@fetch_status = 0
BEGIN
  SET @TSQLDropIndex = 'ALTER TABLE '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' +QUOTENAME(@IndexName)
  PRINT @TSQLDropIndex
  FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
END

CLOSE CursorIndexes
DEALLOCATE CursorIndexes

SQL Server Foreign Key Constraint Creation Script

The following script is for the creation of all Foreign Keys Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS
declare @ForeignKeyID int
declare @ForeignKeyName varchar(4000)
declare @ParentTableName varchar(4000)
declare @ParentColumn varchar(4000)
declare @ReferencedTable varchar(4000)
declare @ReferencedColumn varchar(4000)
declare @StrParentColumn varchar(max)
declare @StrReferencedColumn varchar(max)
declare @ParentTableSchema varchar(4000)
declare @ReferencedTableSchema varchar(4000)
declare @TSQLCreationFK varchar(max)
--Written by Percy Reyes www.percyreyes.com
declare CursorFK cursor for select object_id--, name, object_name( parent_object_id) 
from sys.foreign_keys
open CursorFK
fetch next from CursorFK into @ForeignKeyID
while (@@FETCH_STATUS=0)
begin
 set @StrParentColumn=''
 set @StrReferencedColumn=''
 declare CursorFKDetails cursor for
  select  fk.name ForeignKeyName, schema_name(t1.schema_id) ParentTableSchema,
  object_name(fkc.parent_object_id) ParentTable, c1.name ParentColumn,schema_name(t2.schema_id) ReferencedTableSchema,
   object_name(fkc.referenced_object_id) ReferencedTable,c2.name ReferencedColumn
  from --sys.tables t inner join 
  sys.foreign_keys fk 
  inner join sys.foreign_key_columns fkc on fk.object_id=fkc.constraint_object_id
  inner join sys.columns c1 on c1.object_id=fkc.parent_object_id and c1.column_id=fkc.parent_column_id 
  inner join sys.columns c2 on c2.object_id=fkc.referenced_object_id and c2.column_id=fkc.referenced_column_id 
  inner join sys.tables t1 on t1.object_id=fkc.parent_object_id 
  inner join sys.tables t2 on t2.object_id=fkc.referenced_object_id 
  where fk.object_id=@ForeignKeyID
 open CursorFKDetails
 fetch next from CursorFKDetails into  @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn
 while (@@FETCH_STATUS=0)
 begin    
  set @StrParentColumn=@StrParentColumn + ', ' + quotename(@ParentColumn)
  set @StrReferencedColumn=@StrReferencedColumn + ', ' + quotename(@ReferencedColumn)
  
     fetch next from CursorFKDetails into  @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn
 end
 close CursorFKDetails
 deallocate CursorFKDetails

 set @StrParentColumn=substring(@StrParentColumn,2,len(@StrParentColumn)-1)
 set @StrReferencedColumn=substring(@StrReferencedColumn,2,len(@StrReferencedColumn)-1)
 set @TSQLCreationFK='ALTER TABLE '+quotename(@ParentTableSchema)+'.'+quotename(@ParentTableName)+' WITH CHECK ADD CONSTRAINT '+quotename(@ForeignKeyName)
 + ' FOREIGN KEY('+ltrim(@StrParentColumn)+') '+ char(13) +'REFERENCES '+quotename(@ReferencedTableSchema)+'.'+quotename(@ReferencedTable)+' ('+ltrim(@StrReferencedColumn)+') ' + char(13)+'GO'
 
 print @TSQLCreationFK

fetch next from CursorFK into @ForeignKeyID 
end
close CursorFK
deallocate CursorFK

Script to Drop all SQL Server Foreign Key Constraints

The following script is to drop all Foreign Key Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE DROP SCRIPT OF ALL FOREIGN KEY CONSTRAINTS
declare @ForeignKeyName varchar(4000)
declare @ParentTableName varchar(4000)
declare @ParentTableSchema varchar(4000)

declare @TSQLDropFK varchar(max)

declare CursorFK cursor for select fk.name ForeignKeyName, schema_name(t.schema_id) ParentTableSchema, t.name ParentTableName
from sys.foreign_keys fk  inner join sys.tables t on fk.parent_object_id=t.object_id
open CursorFK
fetch next from CursorFK into  @ForeignKeyName, @ParentTableSchema, @ParentTableName
while (@@FETCH_STATUS=0)
begin
 set @TSQLDropFK ='ALTER TABLE '+quotename(@ParentTableSchema)+'.'+quotename(@ParentTableName)+' DROP CONSTRAINT '+quotename(@ForeignKeyName)+ char(13) + 'GO'
 
 print @TSQLDropFK

fetch next from CursorFK into  @ForeignKeyName, @ParentTableSchema, @ParentTableName
end
close CursorFK
deallocate CursorFK

Query Tuning Techniques Every SQL Programmer Should Know

2015-09-03T02:20:00.001-07:00

CHECK CONSTRAINTS - UNIQUE, PRIMARY KEY, FOREIGN KEY OR DEFAULT

2015-03-18T23:01:00.001-07:00

--Returns 1 row for each CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and/or DEFAULT
SELECT 
    OBJECT_NAME(OBJECT_ID) AS NameofConstraint
        ,SCHEMA_NAME(schema_id) AS SchemaName
        ,OBJECT_NAME(parent_object_id) AS TableName
        ,type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
        AND OBJECT_NAME(OBJECT_ID)='XYZ'

Drop and Create All Constraints of Database

2015-02-16T20:28:00.000-08:00

SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'DROP CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [-- Drop Constraints]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
WHERE (TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE'))
OR (TC.CONSTRAINT_TYPE = 'FOREIGN KEY')
ORDER BY FC.TABLE_NAME DESC,
INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') ASC

SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'ADD CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+
' ' + TC.CONSTRAINT_TYPE +
CASE INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered')
WHEN 1 THEN ' CLUSTERED'
ELSE ' NONCLUSTERED'
END + CHAR(13)+CHAR(10)+
' (' +
MAX(CASE KCU.ORDINAL_POSITION WHEN 1 THEN '[' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+
'WITH FILLFACTOR = ' +
CONVERT(varchar(3), --Replace 0 FILLFACTOR with 100
ISNULL(NULLIF(
INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IndexFillFactor'),
0), 100)
) + CHAR(13)+CHAR(10)+
'ON [' + sfg.groupname + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [-- Create PK/UNIQUE Constraints]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
LEFT JOIN sysindexes s ON s.name = TC.CONSTRAINT_NAME
AND s.id = object_id(TC.TABLE_NAME)
LEFT JOIN sysfilegroups sfg ON sfg.groupid = s.groupid
WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')

GROUP BY
TC.TABLE_SCHEMA,
TC.TABLE_NAME,
TC.CONSTRAINT_NAME,
TC.CONSTRAINT_TYPE,
sfg.groupname
ORDER BY INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') DESC

SELECT 'ALTER TABLE [' + FC.TABLE_SCHEMA + '].[' + FC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'ADD CONSTRAINT [' + FC.CONSTRAINT_NAME + '] ' + FC.CONSTRAINT_TYPE + CHAR(13)+CHAR(10)+
' (' +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+
'REFERENCES [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
' (' +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [-- Create FK Constraints]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_PK ON TC.TABLE_NAME = KCU_PK.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU_PK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.UNIQUE_CONSTRAINT_NAME = TC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_FK ON FC.TABLE_NAME = KCU_FK.TABLE_NAME
AND FC.CONSTRAINT_NAME = KCU_FK.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')

GROUP BY
FC.TABLE_SCHEMA,
FC.TABLE_NAME,
FC.CONSTRAINT_NAME,
FC.CONSTRAINT_TYPE,
TC.TABLE_SCHEMA,
TC.TABLE_NAME

Identify the cause of SQL Server blocking

2014-12-12T02:34:00.000-08:00

SQL Server has a rich set of dynamic management views (DMVs) that helps you to quickly identify locking and blocking in SQL Server. That is why, writing such a query is quite simple in SQL Server. I used the following dynamic management views (DMVs) for my query.

sys.dm_os_waiting_tasks - Returns information about blocked and blocking processes.
sys.dm_exec_sessions - Returns information about authenticated sessions on SQL Server.
sys.dm_exec_requests - Returns the detailed information about the requests currently executing on SQL Server.
sys.dm_tran_locks - Returns the information about the current locks and the processes blocking them.
sys.dm_exec_query_plan - Returns the showplan for the query in XML format.
sys.dm_exec_sql_text - Returns the text of T-SQL batch.

The following is the query, which I have written using these dynamic management views (DMVs) that will help you to quickly identify the SPIDs and other information about the processes that are causing the blocking on SQL Server instance. This query returns the comprehensive information about the blocking and waiting processes, which is useful for troubleshooting SQL Server locking and blocking issues. This query is also a good way to analyze detailed information about locks, and help you to identify the cause of a large number of blocks.

WITH [Blocking]
AS (SELECT w.[session_id]
   ,s.[original_login_name]
   ,s.[login_name]
   ,w.[wait_duration_ms]
   ,w.[wait_type]
   ,r.[status]
   ,r.[wait_resource]
   ,w.[resource_description]
   ,s.[program_name]
   ,w.[blocking_session_id]
   ,s.[host_name]
   ,r.[command]
   ,r.[percent_complete]
   ,r.[cpu_time]
   ,r.[total_elapsed_time]
   ,r.[reads]
   ,r.[writes]
   ,r.[logical_reads]
   ,r.[row_count]
   ,q.[text]
   ,q.[dbid]
   ,p.[query_plan]
   ,r.[plan_handle]
 FROM [sys].[dm_os_waiting_tasks] w
 INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
 INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
 CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
 CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
 WHERE w.[session_id] > 50
  AND w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT'
      ,'ASYNC_NETWORK_IO'))
SELECT b.[session_id] AS [WaitingSessionID]
      ,b.[blocking_session_id] AS [BlockingSessionID]
      ,b.[login_name] AS [WaitingUserSessionLogin]
      ,s1.[login_name] AS [BlockingUserSessionLogin]
      ,b.[original_login_name] AS [WaitingUserConnectionLogin] 
      ,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
      ,b.[wait_duration_ms] AS [WaitDuration]
      ,b.[wait_type] AS [WaitType]
      ,t.[request_mode] AS [WaitRequestMode]
      ,UPPER(b.[status]) AS [WaitingProcessStatus]
      ,UPPER(s1.[status]) AS [BlockingSessionStatus]
      ,b.[wait_resource] AS [WaitResource]
      ,t.[resource_type] AS [WaitResourceType]
      ,t.[resource_database_id] AS [WaitResourceDatabaseID]
      ,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
      ,b.[resource_description] AS [WaitResourceDescription]
      ,b.[program_name] AS [WaitingSessionProgramName]
      ,s1.[program_name] AS [BlockingSessionProgramName]
      ,b.[host_name] AS [WaitingHost]
      ,s1.[host_name] AS [BlockingHost]
      ,b.[command] AS [WaitingCommandType]
      ,b.[text] AS [WaitingCommandText]
      ,b.[row_count] AS [WaitingCommandRowCount]
      ,b.[percent_complete] AS [WaitingCommandPercentComplete]
      ,b.[cpu_time] AS [WaitingCommandCPUTime]
      ,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
      ,b.[reads] AS [WaitingCommandReads]
      ,b.[writes] AS [WaitingCommandWrites]
      ,b.[logical_reads] AS [WaitingCommandLogicalReads]
      ,b.[query_plan] AS [WaitingCommandQueryPlan]
      ,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'
GO

Sample Output

To examine the results of this query, run it on SQL Server where you are experiencing blocks. For example, when I executed this query on my test SQL Server where I'm deliberately running some code to cause blocking, it brings the following results (Note: To fit the resultset, I've split the resultset into seven images):

The following are the columns returned by this query:

WaitingSessionID - The SPID of the waiting session.
BlockingSessionID - The SPID of the blocking session.
WaitingSessionUserLogin - The user session login name under which waiting session is currently executing.
BlockingSessionUserLogin - The user session login name under which blocking session is currently executing.
WaitingUserConnectionLogin - The login name that the user used to create waiting session.
BlockingSessionConnectionLogin - The login name that the user used to create waiting session.
WaitDuration - Waiting process wait time in milliseconds.
WaitType - Type of wait.
WaitRequestMode - Mode of the wait request.
WaitingProcessStatus - The status of waiting process.
BlockingSessionStatus - The status of blocking process.
WaitResource - The name of the resource request is waiting for.
WaitResourceType - The type of the resource request is waiting for.
WaitResourceDatabaseID - The database id of the database in which the requested resource exists.
WaitResourceDatabaseName - The name of the database in which the requested resource exists.
WaitResourceDescription - The detailed description of the waiting resource.
WaitingSessionProgramName - The name of the program that initiated the waiting session.
BlockingSessionProgramName - The name of the program that initiated the blocking session.
WaitingHost - The name of the workstation that is specific to waiting session.
BlockingHost - The name of the workstation that is specific to blocking session.
WaitingCommandType - The type of waiting session command.
WaitingCommandText - The text of waiting session command.
WaitingCommandRowCount - Expected number of rows return by the waiting session.
WaitingCommandPercentComplete - Percentage of the waiting request client.
WaitingCommandCPUTime - CPU time used by waiting session.
WaitingCommandTotalElapsedTime - The total time elapsed in milliseconds since the waiting request arrived.
WaitingCommandReads - The number of reads performed by the waiting session request.
WaitingCommandWrites - The number of writes performed by the waiting session request.
WaitingCommandLogicalReads - The number of logical reads performed by the waiting session request.
WaitingCommandQueryPlan - Waiting command execution plan.
WaitingCommandPlanHandle - Plan handle of the waiting session command.

As you can see from above resultset, that process 53 listed BlockingSessionID column of row 4 is not blocked by another process, hence identified as the SPID that is the cause of the blocking on my test SQL Server instance.

Next Steps

Transaction locks are the most common cause of blocked processes. The stronger (least concurrent) the isolation level, the more likely it is to cause a blocked process.
Try to use less granular lock for your queries, as the less granular the lock, the more likely a blocked process or deadlock will not occur. For example, if the entire table is locked, there is a higher likelihood of blocks than if only a single row is locked.
Revisit your database design because bad database design could be potential reason for excessive locking and blocking.
Check out these tips to learn more about locking and blocking:

Check SQL Server a specified database index fragmentation percentage (avg_fragmentation_in_percent)

2014-03-09T23:38:00.000-07:00

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.avg_fragmentation_in_percent DESC

How To Enable Data Access For Server

2014-02-26T22:07:00.001-08:00

exec sp_serveroption 'server name', 'data access', 'true'

Query To Drop All Constraints From Database

2014-02-25T22:05:00.003-08:00

DECLARE @STR VARCHAR(MAX)
DECLARE CUR CURSOR FOR

SELECT 'ALTER TABLE ' + '[' + S.[NAME] + '].[' + T.NAME + '] DROP CONSTRAINT ['+ C.NAME + ']'
FROM SYS.OBJECTS C, SYS.OBJECTS T, SYS.SCHEMAS S
WHERE C.TYPE IN ('C', 'F', 'PK', 'UQ', 'D')
AND C.PARENT_OBJECT_ID=T.OBJECT_ID AND T.TYPE='U' AND T.SCHEMA_ID = S.SCHEMA_ID
ORDER BY C.TYPE

OPEN CUR
FETCH NEXT FROM CUR INTO @STR
WHILE (@@FETCH_STATUS = 0) BEGIN
PRINT @STR
EXEC (@STR)
FETCH NEXT FROM CUR INTO @STR
END

CLOSE CUR
DEALLOCATE CUR

Find All Other Tables Which Depends On Foreign Keys Involved That Reference Your Own Table

2014-02-24T19:52:00.002-08:00

;WITH ReferencingFK AS
(
SELECT
fk.Name AS 'FKName',
OBJECT_NAME(fk.parent_object_id) 'ParentTable',
cpa.name 'ParentColumnName',
OBJECT_NAME(fk.referenced_object_id) 'ReferencedTable',
cref.name 'ReferencedColumnName'
FROM
sys.foreign_keys fk
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns cpa ON fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id
INNER JOIN
sys.columns cref ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id
)
SELECT
FKName,
ParentTable,
ParentColumnName,
ReferencedTable,
ReferencedColumnName
FROM
ReferencingFK
WHERE
ReferencedTable = 'table_name' -- <=== put your table name here!
ORDER BY
ParentTable, ReferencedTable, FKName

How To Get SQL Server Version

2014-02-20T01:12:00.000-08:00

Option 1

SELECT
        SERVERPROPERTY('productversion'),
        SERVERPROPERTY ('productlevel'),
        SERVERPROPERTY ('edition')

and get the details on the link: http://support.microsoft.com/kb/321185

Option 2

EXEC master..xp_msver

Option 3

SELECT @@version

Option 4

-- Option 4
DECLARE @ver NVARCHAR(128) = CAST(serverproperty('ProductVersion') AS NVARCHAR)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

SET @ver =
                CASE
                        WHEN @ver = '7' THEN 'SQL Server 7'
                        WHEN @ver = '8' THEN 'SQL Server 2000'
                        WHEN @ver = '9' THEN 'SQL Server 2005'
                        WHEN @ver = '10' THEN 'SQL Server 2008/2008 R2'
                        WHEN @ver = '11' THEN 'SQL Server 2012'
                        WHEN @ver = '12' THEN 'SQL Server 2014'
                        ELSE 'Unsupported SQL Server Version'
                END
SELECT @ver

Truncate All Tables in Database

2014-02-13T22:00:00.002-08:00

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

--some of the tables have identity columns we may want to reseed them
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

Data Type Conversion

2014-01-30T01:36:00.001-08:00

Implicit and Explicit Conversion

Data types can be converted either implicitly or explicitly.

Implicit conversions are not visible to the user. SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

GETDATE() implicitly converts to date style 0. SYSDATETIME() implicitly converts to date style 21.
Explicit conversions use the CAST or CONVERT functions.

The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another. For example, the following CAST function converts the numeric value of $157.27 into a character string of '157.27':

CAST ( $157.27 AS VARCHAR(10) )

Use CAST instead of CONVERT if you want Transact-SQL program code to comply with ISO. Use CONVERT instead of CAST to take advantage of the style functionality in CONVERT.
The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

Find Particular Database and All tables Sizes

2014-01-15T22:13:00.000-08:00

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t

DROP TABLE #t

Hold the Lock on Table and Execute Query First

2013-11-21T23:15:00.001-08:00

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRAN T1

DELETE FROM Temp WITH(HOLDLOCK, ROWLOCK);

COMMIT TRAN T1

Modifying SQL Server Synonyms (Can't Alter Synonyms)

2013-05-30T07:16:00.002-07:00

DECLARE
    @DropSynonym NVARCHAR(4000),
    @CreateSynonym NVARCHAR(4000),
    @Permissions NVARCHAR(4000)

SELECT
    @DropSynonym = '',
    @CreateSynonym = '',
    @Permissions = ''

SELECT
    @DropSynonym = @DropSynonym + 'DROP SYNONYM '
    + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) + '.' + QUOTENAME(NAME) + ';
',
    @CreateSynonym = @CreateSynonym + 'CREATE SYNONYM '
    + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) + '.' + QUOTENAME(NAME) + ' FOR '
    + REPLACE(base_object_name, '[OldDB]', '[NewDB]') + ';
'
FROM
    sys.synonyms ;
WITH    PermQuery
          AS (SELECT
                CASE WHEN perm.state <> 'W' THEN perm.state_desc
                     ELSE 'GRANT'
                END COLLATE database_default AS PermissionState,
                perm.permission_name COLLATE database_default AS Permission,
                SCHEMA_NAME(obj.schema_id) AS SchemaName,
                obj.name AS ObjectName,
                CASE WHEN cl.column_id IS NULL THEN SPACE(0)
                     ELSE '(' + QUOTENAME(cl.name) + ')'
                END AS ColumnName,
                CASE WHEN perm.state <> 'W' THEN 'N'
                     ELSE 'Y'
                END AS WithOption,
                usr.name AS UserName
              FROM
                sys.synonyms AS s
                INNER JOIN sys.all_objects AS obj
                    ON s.object_id = obj.object_id
                INNER JOIN sys.database_permissions AS perm
                    ON perm.major_id = obj.[object_id]
                INNER JOIN sys.database_principals AS usr
                    ON perm.grantee_principal_id = usr.principal_id
                LEFT JOIN sys.columns AS cl
                    ON cl.column_id = perm.minor_id
                       AND cl.[object_id] = perm.major_id)
    SELECT
        @Permissions = @Permissions + PermissionState + ' ' + Permission
        + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' '
        + ColumnName + ' TO ' + UserName
        + CASE WithOption
            WHEN 'Y' THEN ' WITH GRANT OPTION'
            ELSE ''
          END + ';
'
    FROM
        PermQuery

PRINT @DropSynonym
EXEC(@DropSynonym)

PRINT @CreateSynonym
EXEC(@CreateSynonym)

PRINT @Permissions
EXEC (@Permissions)

Auto Generate SQL Server All Databases Attach and Detach Scripts

2013-04-24T02:20:00.002-07:00

-- Detach All Databases

-- Build the sp_detach_db command (ONLINE, non-system databases only):
SELECT DISTINCT'exec sp_detach_db ''' + DB_NAME(dbid) + ''';'
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('C','D')
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')

-----------------------------------------------------------------------------

-- Attach All Databases
-- Build the sp_attach_db:
-- (I preach everyone against using cursor... so I don't)
SET NOCOUNT ON
DECLARE     @cmd        VARCHAR(MAX),
            @dbname     VARCHAR(200),
            @prevdbname VARCHAR(200)

SELECT @cmd = '', @dbname = ';', @prevdbname = ''

CREATE TABLE #Attach
    (Seq        INT IDENTITY(1,1) PRIMARY KEY,
     dbname     SYSNAME NULL,
     fileid     INT NULL,
     filename   VARCHAR(1000) NULL,
     TxtAttach VARCHAR(MAX) NULL
)

INSERT INTO #Attach
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid, filename, CONVERT(VARCHAR(MAX),'') AS TxtAttach
FROM master.dbo.sysaltfiles
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles
            WHERE SUBSTRING(filename,1,1) IN ('C','D'))
            AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
            AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
ORDER BY dbname, fileid, filename

UPDATE #Attach
SET @cmd = TxtAttach =
            CASE WHEN dbname <> @prevdbname
            THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''')
            ELSE @cmd
            END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''',
    @prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
    @dbname = dbname
FROM #Attach WITH (INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)

SELECT TxtAttach
FROM
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
GROUP BY dbname) AS x

DROP TABLE #Attach

Script to create SQL Server logins

2013-04-23T08:26:00.001-07:00

-- ***********************************************************************************/
-- Description  :    Script to create SQL Server logins 
--                        This Script will create SQL syntax for re-creating existing SQL Server Logins. The OUTPUT of this script will be a TSQL statement, which can used to recreate the logins.
--                            We can also use use this script to transfer logins from one SQL instance to another instance
-- Compatibility  :     2005+
-- ***********************************************************************************/

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
 
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  sysname
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (256)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
            SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l
            ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR
            SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l
            ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group
      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
                        SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
                        SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string
 
        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login has exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin
  END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
exec sp_help_revlogin

Find Last Index Rebuild

2013-04-23T00:19:00.002-07:00

SELECT object_name(si.[object_id]) AS [TableName],
CASE
WHEN si.[index_id] = 0 then 'Heap'
WHEN si.[index_id] = 1 then 'CL'
WHEN si.[index_id] BETWEEN 2 AND 250 THEN 'NC ' + RIGHT('00' + convert(varchar, si.[index_id]), 3)
ELSE '' END AS [IndexType],
si.[name] AS [IndexName],
si.[index_id] AS [IndexID],
CASE
WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(m, -1, getdate())
THEN '!! More than a month OLD !!'
WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(wk, -1, getdate())
THEN '! Within the past month !'
WHEN si.[index_id] BETWEEN 1 AND 250 THEN 'Stats recent'
ELSE ''
END AS [Warning],
STATS_DATE (si.[object_id], si.[index_id]) AS [Last Stats Update]
FROM sys.indexes AS si
WHERE OBJECTPROPERTY(si.[object_id], 'IsUserTable') = 1
ORDER BY [TableName], si.[index_id]

Get Missing Identity Value Information

2013-03-20T07:58:00.004-07:00

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @Store_TABLE_Info TABLE (    Ident int IDENTITY (1,1)
                                    , Table_Name varchar(256)
                                    , Column_Name varchar(130)
                                    , Seed_Value sql_variant
                                    , increment_value sql_variant
                                    , Last_value sql_variant
                                )

DECLARE @Table_Name varchar(261)
        ,@Column_Name varchar(130)
        ,@Seed_value sql_variant
        ,@Increment_Value sql_variant
        ,@last_Value sql_variant
        ,@count int
        ,@Sqlcmd1 nvarchar(4000)
        ,@Sqlcmd2 nvarchar(4000)
        ,@Sqlcmd3 nvarchar(4000)
        ,@count_Records varchar(50)

/*
List out all table names thats has identity column.
    * Get Complete Table Name (Including Schema Name).
    * Get Column Name on which Identity property is enabled
    * Get Seed Value (Starting Value of Identity)
    * Get Increment Value (Incremental value for Identity Property)
    * Get Current Seed value/ Last Value in column. (Current Value)
*/

INSERT INTO        @Store_TABLE_Info
                SELECT    '['+SCHEMA_NAME([Schema_id])+'].'+ '['+OBJECT_NAME(ST.[object_id])+']' [Table_Name]
                        ,'['+SIC.Name+']' [Column_Name]
                        ,Seed_Value
                        ,Increment_Value
                        ,ISNULL(last_Value,0)last_Value
                FROM    Sys.Tables ST
                        JOIN sys.Identity_columns SIC ON ST.[object_id] = SIC.[object_id]

CREATE TABLE    #Temp_Final_Mismatch    (    TABLE_Name varchar(261)
                                            , Column_Name varchar(130)
                                            , Missing_Identity_Values varchar (MAX)
                                        )

SET @count = 1
WHILE @count < = ( SELECT COUNT(* ) FROM @Store_TABLE_Info)
    BEGIN
        SELECT    @TABLE_Name = TABLE_Name
                ,@Column_Name = Column_Name
                ,@Seed_value = Seed_value
                ,@Increment_Value = Increment_Value
                ,@last_Value = last_Value
        FROM    @Store_TABLE_Info
        WHERE    Ident = @Count

        /*
        Considering One table at a time (Making Use of While Loop):

        Using Above information:
        1. we First create a Temporary table with same column name, with the same Seed Value and Same Increment value.
        2. We have Seed Value, Increment Value and Current Value, This information is enough to have complete set of Identity values in our Temporary table.
            * This is done through another while loop.
        3. Now we will compare Temporary Table created in Step 1 with User Defined table
            * Trying to find all missing Identity values that are present in Temporary table but not present in user defined table. (One Table at a time)
        4. Result from above step, we store into another temporary table.
        */

        --        Based on Original Seed Value, Increment Value and Current Value, We can calculate how many records suppose to be present in User Defined Table.

        SET @count_Records = CONVERT(varchar ,((CONVERT(int,@last_Value) - (1* CONVERT(int,@Seed_value)) )/CONVERT(int,@Increment_Value) )+    1)

        SET @Sqlcmd1 = '
            CREATE TABLE #Temp_'+CONVERT(varchar , @Count) +' ( '+@Column_Name+' int IDENTITY ('+CONVERT(varchar ,@Seed_value) +','+CONVERT( varchar,@Increment_Value)+'), Bit_Value bit default (1))

            '+/*
                Inserting Estimated No of Records into Temporary table.
            */+'
            DECLARE @Count1 int
            SET @Count1 = 1
            While @Count1 < = '+@count_Records+' -- To Get Actual Number of records that should be present in a TABLE.
                BEGIN
                    INSERT INTO #Temp_'+CONVERT(varchar , @Count) +' VALUES (default)
                    SET @Count1 = @Count1 + 1
                END

            '+/*
                Comparing Temporary Table with User Defined Table, To get Missing Identity Values information.
            */+'
            DECLARE @temp_store_id TABLE ( Ident int IDENTITY (1,1), Missing_Ident varchar (50))

            INSERT INTO @temp_store_id (Missing_Ident)
                                        SELECT '+@Column_Name+'
                                        FROM
                                            (
                                                SELECT    '+@Column_Name+'
                                                FROM    #Temp_'+CONVERT(varchar , @Count) +'
                                                    EXCEPT
                                                SELECT    '+@Column_Name +'
                                                FROM    '+@TABLE_Name+'
                                            ) X

            '+/*
                Now that we have all Missing value into @temp_store_id Table. Lets make it a string, So that we can easily read it.
            */+'
            DECLARE @String varchar (max)
            SET @String = '' ''
            SELECT @String = @String + CASE WHEN @string = '' '' THEN '''' ELSE '','' END + CONVERT( varchar(50),Missing_Ident)
            FROM @temp_store_id

            '+/*
                Store String Values in another temporary table, Which will be displayed at the end
            */+'
            If @String != '' ''
                INSERT INTO #Temp_Final_Mismatch VALUES ('''+@TABLE_Name+''', '''+@Column_Name +''' , @String )

            DROP TABLE #Temp_'+CONVERT(varchar , @Count)

            --print @Sqlcmd1
            EXEC Sp_EXECutesql @Sqlcmd1
        SET @count = @count + 1
END

-- Displaying Final Result.
SELECT    *
FROM    #Temp_Final_Mismatch

DROP TABLE #Temp_Final_Mismatch
SET NOCOUNT ON
SET ANSI_WARNINGS ON

Top 10 Unused Indexes

2013-03-20T07:52:00.002-07:00

SELECT TOP 10
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.object_id = i.object_id
INNER JOIN sys.objects o on dm_ius.object_id = o.object_id
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.object_id
                FROM sys.partitions p GROUP BY p.index_id, p.object_id) p
        ON p.index_id = dm_ius.index_id AND dm_ius.object_id = p.object_id
WHERE OBJECTPROPERTY(dm_ius.object_id,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

Top 10 Missing Indexes

2013-03-20T07:50:00.002-07:00

SELECT TOP 10
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
object_name(dm_mid.object_id,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + object_name(dm_mid.object_id,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC

Find Duplicate Indexes

2013-03-20T07:41:00.001-07:00

WITH MyDuplicate AS (SELECT
    Sch.[name] AS SchemaName,
    Obj.[name] AS TableName,
    Idx.[name] AS IndexName,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 1) AS Col1,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 2) AS Col2,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 3) AS Col3,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 4) AS Col4,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 5) AS Col5,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 6) AS Col6,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 7) AS Col7,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 8) AS Col8,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 9) AS Col9,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 10) AS Col10,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 11) AS Col11,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 12) AS Col12,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 13) AS Col13,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 14) AS Col14,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 15) AS Col15,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 16) AS Col16
FROM sys.indexes Idx
INNER JOIN sys.objects Obj ON Idx.[object_id] = Obj.[object_id]
INNER JOIN sys.schemas Sch ON Sch.[schema_id] = Obj.[schema_id]
WHERE index_id > 0)
SELECT    MD1.SchemaName, MD1.TableName, MD1.IndexName,
        MD2.IndexName AS OverLappingIndex,
        MD1.Col1, MD1.Col2, MD1.Col3, MD1.Col4,
        MD1.Col5, MD1.Col6, MD1.Col7, MD1.Col8,
        MD1.Col9, MD1.Col10, MD1.Col11, MD1.Col12,
        MD1.Col13, MD1.Col14, MD1.Col15, MD1.Col16
FROM MyDuplicate MD1
INNER JOIN MyDuplicate MD2 ON MD1.tablename = MD2.tablename
    AND MD1.indexname <> MD2.indexname
    AND MD1.Col1 = MD2.Col1
    AND (MD1.Col2 IS NULL OR MD2.Col2 IS NULL OR MD1.Col2 = MD2.Col2)
    AND (MD1.Col3 IS NULL OR MD2.Col3 IS NULL OR MD1.Col3 = MD2.Col3)
    AND (MD1.Col4 IS NULL OR MD2.Col4 IS NULL OR MD1.Col4 = MD2.Col4)
    AND (MD1.Col5 IS NULL OR MD2.Col5 IS NULL OR MD1.Col5 = MD2.Col5)
    AND (MD1.Col6 IS NULL OR MD2.Col6 IS NULL OR MD1.Col6 = MD2.Col6)
    AND (MD1.Col7 IS NULL OR MD2.Col7 IS NULL OR MD1.Col7 = MD2.Col7)
    AND (MD1.Col8 IS NULL OR MD2.Col8 IS NULL OR MD1.Col8 = MD2.Col8)
    AND (MD1.Col9 IS NULL OR MD2.Col9 IS NULL OR MD1.Col9 = MD2.Col9)
    AND (MD1.Col10 IS NULL OR MD2.Col10 IS NULL OR MD1.Col10 = MD2.Col10)
    AND (MD1.Col11 IS NULL OR MD2.Col11 IS NULL OR MD1.Col11 = MD2.Col11)
    AND (MD1.Col12 IS NULL OR MD2.Col12 IS NULL OR MD1.Col12 = MD2.Col12)
    AND (MD1.Col13 IS NULL OR MD2.Col13 IS NULL OR MD1.Col13 = MD2.Col13)
    AND (MD1.Col14 IS NULL OR MD2.Col14 IS NULL OR MD1.Col14 = MD2.Col14)
    AND (MD1.Col15 IS NULL OR MD2.Col15 IS NULL OR MD1.Col15 = MD2.Col15)
    AND (MD1.Col16 IS NULL OR MD2.Col16 IS NULL OR MD1.Col16 = MD2.Col16)
ORDER BY
    MD1.SchemaName,MD1.TableName,MD1.IndexName