First function will displays fragmentation information for the data and indexes of the specified mssql table. It take 3 arguments: DB name, table name and if you want to see the data for each particular index or as a general report.
<cffunction name="getFragmentationInfo" output="No" returntype="any" description="Displays fragmentation information for the data and indexes of the specified table">
<cfargument name="DBName" default="master" type="any">
<cfargument name="DBObjName" default="" type="any">
<cfargument name="allIndexes" default="No" type="any">
<cfset var getFragmentationInfo = "">
<cfquery name="getFragmentationInfo" datasource="#dsn#" username="#userName#" password="#passwd#">
use #DBName#
DBCC SHOWCONTIG(#DBObjName#) with tableresults<cfif allIndexes eq "Yes">, ALL_INDEXES</cfif>
</cfquery>
<cfreturn getFragmentationInfo>
</cffunction>
And this function will returns some fragmentation statistics as being the size and fragmentation. Possible parameters are: DB name, table name, index name (if not provided it will show the info for all indexes), mode (possible values are Limited and Detailed) and fragmentation value from which to show the result.
<cffunction name="getFragmentationStats" output="No" returntype="any" description="returns size and fragmentation statistics for all indexes and partitions">
<cfargument name="DBName" default="master" type="any">
<cfargument name="DBObjName" default="" type="any">
<cfargument name="indexName" default="" type="any">
<cfargument name="mode" default="Limited" type="any">
<cfargument name="maxFrag" default="50" type="any">
<cfset var getFragmentationStats = "">
<cfif ListFindNoCase("Limited,Detailed", mode, ",") eq 0>
<cfset mode = "Limited">
</cfif>
<cfquery name="getFragmentationStats" datasource="#dsn#" username="#userName#" password="#passwd#">
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'#DBName#');
SET @object_id = OBJECT_ID(N'#DBObjName#');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT i.name, s.*
FROM sys.indexes i, sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , '#mode#') s
Where avg_fragmentation_in_percent >= #Val(maxFrag)#
and i.index_id = s.index_id
<cfif Len(Trim(indexName)) gt 0>
and i.name = '#indexName#'
</cfif>
END;
</cfquery>
<cfreturn getFragmentationStats>
</cffunction>