Without adding so many paragraphs, since you are looking for fragmentation, you can below procedure.
You can create it in the same database.
CREATE PROCEDURE usp_CheckFragmentation
@TableName NVARCHAR(128) = NULL
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Fragmentation') IS NOT NULL
DROP TABLE #Fragmentation;
CREATE TABLE #Fragmentation (
DatabaseName SYSNAME,
SchemaName SYSNAME,
TableName SYSNAME,
IndexName SYSNAME,
IndexTypeDesc NVARCHAR(60),
AvgFragmentationInPercent FLOAT,
PageCount BIGINT
);
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '
INSERT INTO #Fragmentation
SELECT
DB_NAME() AS DatabaseName,
s.name AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexTypeDesc,
ps.avg_fragmentation_in_percent AS AvgFragmentationInPercent,
ps.page_count AS PageCount
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, ''LIMITED'') ps
INNER JOIN
sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
INNER JOIN
sys.tables t ON ps.object_id = t.object_id
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
(@TableName IS NULL OR t.name = @TableName)';
EXEC sp_executesql @SQL, N'@TableName NVARCHAR(128)', @TableName;
SELECT * FROM #Fragmentation ORDER BY AvgFragmentationInPercent DESC;
DROP TABLE #Fragmentation;
END;
GO
