Latest Entries
SQL Server 2008 R2 / SQL Server 2012 / Uncategorized

Find fragmentation and if the table/index is partitioned

select DB_NAME(a.database_id), OBJECT_NAME(a.object_id), ,i.index_id,index_type_desc,index_level, case when p.partition_number <> 1 Then ‘1’ else ‘0’ end isPartitioned, avg_fragmentation_in_percent,is_unique,is_disabled, page_count,avg_fragment_size_in_pages,a.partition_number from sys.dm_db_index_physical_stats (DB_ID(),object_id(‘NameOfTable’),NULL,NULL,’LIMITED’) a join sys.indexes i on a.object_id=i.object_id and a.index_id=i.index_id join sys.partitions p on a.object_id=p.object_id and a.index_id=p.index_id and a.partition_number=p.partition_number where avg_fragmentation_in_percent >0 and page_count > 0 and i.is_disabled=0 order by OBJECT_NAME(a.object_id) , avg_fragmentation_in_percent desc Continue reading


Scripting Foreign Key Constraints

The Script below ¬†allows you to script Foreign Keys on the fly. This takes in to consideration if, the Constraint was created with NOCHECK OPTION. especially useful if you’re creating constraints when doing partition SWITCH. select ‘ALTER TABLE ‘ + object_name(fk.parent_object_id) + CASE WHEN fk.is_not_trusted =1 THEN ‘ WITH NOCHECK’ ELSE ” END + ‘ … Continue reading

SQL Server 2008 R2 / SQL Server 2012

Working with days of a year in SQL

declare @FromDay varchar(20),@today varchar(20),@Monday varchar(30), @thursday varchar(30) select @FromDay=’Monday’,@today =’Thursday’ select DATEADD(D,number-1,DATEADD(YYYY,115,0)) as [Date], DATENAME(DW,DATEADD(D,number-1,DATEADD(YYYY,115,0)))as [DateName], DATENAME(WEEK,DATEADD(D,number-1,DATEADD(YYYY,115,0))) as [Week] –into #Test from master..spt_values where type=’P’ and number between 1 and (DATEDIFF(D,DATEADD(year,115,0),DATEADD(year,116,0))) IF (DATENAME(DW,GETDATE())) NOT IN(‘Friday’, ‘Saturday’) BEGIN Select @Monday=[Date] from #Test where Week=DATENAME(WEEK,GETDATE())-1 and [DateName]=@FromDay Select @thursday=[Date] from #Test where Week=DATENAME(WEEK,GETDATE()) -1 and [DateName]=@today END … Continue reading

Batch Script / Powershell / SQL Server 2008 R2 / SQL Server 2012

Install New Fonts in Multiple machines in a domain

Installing FONTS in a domain without restarting machines is a tricky one. It could be achieved through a startup script. However, if you don’t want / unable to restart them, here’s how you can do it. I was unable to use powershell outright as WinRM was not enabled in the estate and we had workstations … Continue reading


Find Missing Indexes with CREATE Script

select DB_Name(database_id),OBJECT_NAME(object_id),equality_columns,inequality_columns,included_columns,statement,user_seeks,user_scans,unique_compiles,avg_system_impact,avg_total_system_cost,avg_total_user_cost,avg_user_impact, ‘CREATE INDEX <Name of INDEX> on ‘ + statement + ‘(‘+ equality_columns + ‘)’ + CASE WHEN included_columns is null THEN ” ELSE ‘ include (‘+ included_columns +’)’ END from sys.dm_db_missing_index_groups mig join sys.dm_db_missing_index_group_stats migs on mig.index_group_handle = migs.group_handle join sys.dm_db_missing_index_details mid on mid.index_handle=mig.index_handle –cross apply sys.dm_db_missing_index_columns(mid.index_handle) AND MID.Equality_Columns IS NOT NULL WHERE … Continue reading