How do I find index fragmentation in a SQL Server database?

I'm running into index issues on SQL Server and wanted to figure out index fragmentation to see if we could improve it.  Here's a way to find index fragmentation:

SELECT object_name(dt.object_id) Tablename,si.name
IndexName,dt.avg_fragmentation_in_percent AS
ExternalFragmentation,dt.avg_page_space_used_in_percent AS
InternalFragmentation
FROM
(
    SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats (db_id(),null,null,null,'DETAILED')
    WHERE index_id  0
) AS dt 
INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 

‍But, I was running into an issue because my database is running in SQL 80 mode (SQL Server 2000).  The following fixes this issue.

declare @db_id smallint
set @db_id=db_id()

SELECT object_name(dt.object_id) Tablename,si.name
IndexName,dt.avg_fragmentation_in_percent AS
ExternalFragmentation,dt.avg_page_space_used_in_percent AS
InternalFragmentation
FROM
(
    SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats (@db_id,null,null,null,'DETAILED')
    WHERE index_id  0
) AS dt 
INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 

‍


It seems that in SQL 80 mode, you can't use the output of functions directly, so storing it in a variable first does the trick.

The output of this method shows internal and external fragmentation levels:

Exernal Fragmentation
External fragmentation occurs when on disk, the physical storage of contents is non-contiguous.  This result represents the average amount of fragmentation on an index.  The lower this number is, the better.  Any result over 10% should be dealt with.

Internal Fragmentation
Internal fragmentation occurs when records are stored non-contiguously inside the page.  In other words, there is space between records in the index.  This can happen as a table is modified (INSERT, UPDATE, DELETE) without rebuilding the index.  This result represents the average amount of page storage consumed in a given index.  The higher the result, the better.