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