Tag Archives: Table

How to determine the index fragmentation level on your SQL Server instance

Below is a script that will run against every table in every database on your Sql Server instance and output, among other things, the average index fragmentation percentage.

It can be a bit heavy when running against large databases with thousands of tables so I would recommend running it during downtime or outside office hours.

This script returns fragmentation stats by utilizing a DMV.

When Nulls are presented as parameters this DMV works at a server level and uses preset defaults.

[sys].[dm_db_index_physical_stats](NULL, NULL, NULL, NULL, NULL)

However it also accepts the parameter options below to limited the results returned.

[sys].[dm_db_index_physical_stats](Database_Id, Object_Id, Index_Id, partition_number, MODE)

MODE OPTIONS:

Mode specifies the scan level that is used to obtain statistics. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED.

DEFAULT will have 12 columns full of data and then NULLS in the remainder.
SAMPLED will have 21 columns full of data.
LIMITED will have 12 columns of data and the NULLS in the remainder.
DETAILED will have 21 columns full of data.

Note: I’ve capture more fields in the temp table #Index than I use in the final select statement. This is because you might want to include additional index related fields i.e. check if the index is unique etc. If all you want is the index name in the final select statement you can tidy up the query as needed and exclude the unwanted additional fields.

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Schema') IS NOT NULL
	DROP TABLE #Schema

IF OBJECT_ID('tempdb..#Index') IS NOT NULL
	DROP TABLE #Index

DECLARE @Sql AS VARCHAR(MAX)
DECLARE @Database TABLE (
	DbId INT
	,DbName SYSNAME
	)
DECLARE @DbName AS SYSNAME
DECLARE @DbId AS INT

CREATE TABLE #Schema (
	DbId INT
	,DbName SYSNAME
	,Object_Id INT NULL
	,ObjectName SYSNAME NULL
	,TableFlag BIT
	,ViewFlag BIT
	,Schema_Id INT NULL
	,SchemaName SYSNAME NULL
	)

CREATE TABLE #Index (
	DbId INT
	,DbName SYSNAME
	,Object_Id INT NOT NULL
	,NAME SYSNAME NULL
	,index_id INT NOT NULL
	,type TINYINT NOT NULL
	,type_desc NVARCHAR(60) NULL
	,is_unique BIT NULL
	,data_space_id INT NULL
	,ignore_dup_key BIT NULL
	,is_primary_key BIT NULL
	,is_unique_constraint BIT NULL
	,fill_factor TINYINT NOT NULL
	,is_padded BIT NULL
	,is_disabled BIT NULL
	,is_hypothetical BIT NULL
	,allow_row_locks BIT NULL
	,allow_page_locks BIT NULL
	,has_filter BIT NULL
	,filter_definition NVARCHAR(max) NULL
	)

INSERT INTO @Database (
	DbId
	,DbName
	)
SELECT dbid
	,NAME
FROM master.dbo.sysdatabases
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC

SET @DbId = 0

WHILE @DbId IS NOT NULL
BEGIN
	SET @DbId = (
			SELECT MIN(DbId)
			FROM @Database
			WHERE DbId > @DbId
			)
	SET @DbName = (
			SELECT DbName
			FROM @Database
			WHERE DbId = @DbId
			)
	SET @Sql = '
	INSERT INTO #Schema (
		DbId
		,DbName
		,Object_Id
		,ObjectName
		,TableFlag
		,ViewFlag
		,Schema_Id
		,SchemaName
		)
	SELECT ' + CONVERT(VARCHAR(128), @DbId) + ' AS DbId
		,' + '''' + @DbName + '''' + ' AS DbName
		,o.Object_Id
		,o.NAME AS ObjectName
		,CASE 
			WHEN o.type = ''IT''
				THEN 1
			WHEN o.type = ''S''
				THEN 1
			WHEN o.type = ''TT''
				THEN 1
			WHEN o.type = ''U''
				THEN 1
			ELSE NULL
			END AS TableFlag
		,CASE 
			WHEN o.type = ''V''
				THEN 1
			ELSE NULL
			END AS ViewFlag
		,s.Schema_Id
		,s.NAME AS SchemaName
	FROM ' + QUOTENAME(@DbName) + '.sys.objects AS o WITH (NOLOCK)
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.schemas AS s WITH (NOLOCK) ON o.schema_id = s.schema_id
	'

	EXEC (@Sql)

	SET @Sql = '
	INSERT INTO #Index (
	DbId
	,DbName
	,object_id
	,NAME
	,index_id
	,type
	,type_desc
	,is_unique
	,data_space_id
	,ignore_dup_key
	,is_primary_key
	,is_unique_constraint
	,fill_factor
	,is_padded
	,is_disabled
	,is_hypothetical
	,allow_row_locks
	,allow_page_locks
	,has_filter
	,filter_definition
	)
	SELECT ' + CONVERT(VARCHAR(128), @DbId) + ' AS DbId
		,' + '''' + @DbName + '''' + ' AS DbName
		,object_id
		,NAME
		,index_id
		,type
		,type_desc
		,is_unique
		,data_space_id
		,ignore_dup_key
		,is_primary_key
		,is_unique_constraint
		,fill_factor
		,is_padded
		,is_disabled
		,is_hypothetical
		,allow_row_locks
		,allow_page_locks
		,has_filter
		,filter_definition
	FROM ' + QUOTENAME(@DbName) + '.sys.indexes WITH (NOLOCK)
	'

	EXEC (@Sql)
END

SELECT ROW_NUMBER() OVER (
		ORDER BY s.DbName ASC
			,s.SchemaName ASC
			,s.ObjectName
		) AS Row
	,s.DbName
	,s.SchemaName AS SchemaName
	,s.Object_Id AS ObjectId
	,s.ObjectName
	,s.TableFlag
	,s.ViewFlag
	,i.Name AS IndexName
	,ddips.index_type_desc AS IndexType
	,ddips.page_count AS Pages
	,ddips.record_count AS Records
	,ddips.fragment_count AS Frags
	,ddips.avg_fragmentation_in_percent AS AvgFragPercent
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED') AS ddips
INNER JOIN #Schema AS s ON ddips.database_id = s.DbId
INNER JOIN #Index AS i ON s.DbId = i.DbId
	AND s.Object_Id = i.Object_Id
	AND ddips.index_id = i.index_id
	AND ddips.object_id = s.Object_Id
WHERE (
		s.TableFlag = 1
		OR s.ViewFlag = 1
		)
ORDER BY s.DbName ASC
	,s.SchemaName ASC
	,s.ObjectName

SQL table of common file types and extensions used in business

Below is a list of the file types and their respective extensions commonly used in business. You can use the SQL script on this page to create a table for use in queries and stored procedures.

(The table below was created used No-Cruft Excel to HTML Table Converter)

FileType Extension
Microsoft Word 97 – 2003 Document doc
Microsoft Word 97 – 2003 Template dot
Word document docx
Word macro-enabled document docm
Word template dotx
Word macro-enabled template dotm
Word binary document introduced in Microsoft Office 2007 docb
Microsoft Excel 97-2003 Worksheet xls
Microsoft Excel 97-2003 Template xlt
Excel macro xlm
Excel workbook xlsx
Excel macro-enabled workbook xlsm
Excel template xltx
Excel macro-enabled template xltm
Excel binary worksheet xlsb
Excel add-in or macro xla
Excel add-in xlam
Excel XLL add-in xll
Excel workspace xlw
Legacy PowerPoint presentation ppt
Legacy PowerPoint template pot
Legacy PowerPoint slideshow pps
PowerPoint presentation pptx
PowerPoint macro-enabled presentation pptm
PowerPoint template potx
PowerPoint macro-enabled template potm
PowerPoint add-in ppam
PowerPoint slideshow ppsx
PowerPoint macro-enabled slideshow ppsm
PowerPoint slide sldx
PowerPoint macro-enabled slide sldm
The file extension for the Office Access 2007 file format ACCDB
The file extension for Office Access 2007 files that are in “execute only” mode ACCDE
The file extension for Access Database Templates. ACCDT
The file extension for the Office Access 2007 file format that enables you to open a database in runtime mode ACCDR
Microsoft Publisher file extension pub
Windows BitMap BMP
Data Interchange format DIF
Graphics Interchange Format GIF
Web page source text HTML
JPEG graphic JPG
JPEG graphic JPEG
Web page imagemap MAP
Acrobat -Portable document format PDF
Public Network graphic PNG
Adobe PhotoShop PSD
PaintShop Pro PSP
Rich Text Format RTF
Stuffit Compressed Archive SIT
UNIX TAR Compressed Archive TAR
TIFF graphic TIF
ASCII text (Mac text does not contain line feeds–use DOS Washer Utility to fix) TXT
Windows sound WAV
MS Works WKS
PC Zip Compressed Archive ZIP

 

USE [YourDatabase]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IH_FileType](
	[IH_FileType_ID] [int] IDENTITY(1,1) NOT NULL,
	[FileType] [varchar](250) NULL,
	[Extension] [varchar](5) NULL,
	[OfficeFileType] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[IH_FileType_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[IH_FileType] ON
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (1, N'Microsoft Word 97 - 2003 Document', N'doc', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (2, N'Microsoft Word 97 - 2003 Template', N'dot', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (3, N'Word document', N'docx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (4, N'Word macro-enabled document', N'docm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (5, N'Word template', N'dotx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (6, N'Word macro-enabled template', N'dotm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (7, N'Word binary document introduced in Microsoft Office 2007', N'docb', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (8, N'Microsoft Excel 97-2003 Worksheet', N'xls', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (9, N'Microsoft Excel 97-2003 Template', N'xlt', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (10, N'Excel macro', N'xlm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (11, N'Excel workbook', N'xlsx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (12, N'Excel macro-enabled workbook', N'xlsm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (13, N'Excel template', N'xltx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (14, N'Excel macro-enabled template', N'xltm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (15, N'Excel binary worksheet', N'xlsb', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (16, N'Excel add-in or macro', N'xla', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (17, N'Excel add-in', N'xlam', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (18, N'Excel XLL add-in', N'xll', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (19, N'Excel workspace', N'xlw', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (20, N'Legacy PowerPoint presentation', N'ppt', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (21, N'Legacy PowerPoint template', N'pot', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (22, N'Legacy PowerPoint slideshow', N'pps', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (23, N'PowerPoint presentation', N'pptx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (24, N'PowerPoint macro-enabled presentation', N'pptm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (25, N'PowerPoint template', N'potx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (26, N'PowerPoint macro-enabled template', N'potm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (27, N'PowerPoint add-in', N'ppam', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (28, N'PowerPoint slideshow', N'ppsx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (29, N'PowerPoint macro-enabled slideshow', N'ppsm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (30, N'PowerPoint slide', N'sldx', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (31, N'PowerPoint macro-enabled slide', N'sldm', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (32, N'The file extension for the Office Access 2007 file format', N'accdb', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (33, N'The file extension for Office Access 2007 files that are in "execute only" mode', N'accde', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (34, N'The file extension for Access Database Templates.', N'accdt', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (35, N'The file extension for the Office Access 2007 file format that enables you to open a database in runtime mode', N'accdr', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (36, N'Microsoft Publisher file extension', N'pub', 1)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (37, N'Windows BitMap', N'bmp', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (38, N'Data Interchange format', N'dif', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (39, N'Graphics Interchange Format', N'gif', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (40, N'Web page source text', N'html', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (41, N'JPEG graphic', N'jpg', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (42, N'JPEG graphic', N'jpeg', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (43, N'Web page imagemap', N'map', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (44, N'Acrobat -Portable document format', N'pdf', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (45, N'Public Network graphic', N'png', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (46, N'Adobe PhotoShop', N'psd', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (47, N'PaintShop Pro', N'psp', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (48, N'Rich Text Format', N'rtf', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (49, N'Stuffit Compressed Archive', N'sit', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (50, N'UNIX TAR Compressed Archive', N'tar', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (51, N'TIFF graphic', N'tif', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (52, N'ASCII text (Mac text does not contain line feeds--use DOS Washer Utility to fix)', N'txt', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (53, N'Windows sound', N'wav', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (54, N'MS Works', N'wks', 0)
INSERT [dbo].[IH_FileType] ([IH_FileType_ID], [FileType], [Extension], [OfficeFileType]) VALUES (55, N'PC Zip Compressed Archive', N'zip', 0)
SET IDENTITY_INSERT [dbo].[IH_FileType] OFF


SELECT *
FROM IH_FileType

Distinct Row Count Table

If you come from a programming background you might want to do a loop using TSQL rather than using a cursor.

The syntax for this is pretty recognisable, exempli gratia,

However if you want to feed in dynamic variables captured from a table you can create a distinctive row count table.

This deduplicates the occurrence of a field value and assigns it a row number.

Where this can be used will be included in a more complicated example coming in the future.