how to create sample table and inserting data?

CREATE TABLE sandeep ( id INT PRIMARY KEY, name VARCHAR(20) );
INSERT INTO sandeep VALUES ( 1, ‘sam’ );
INSERT INTO sandeep VALUES ( 2, ‘sandy’ );
INSERT INTO sandeep VALUES ( 3, ‘sand’ );

Advertisements

Get Index Fragmentation Percentage For All Tables In SQL Server Database

select object_name(itable.object_id) as tablename,
itable.name as IndexName,
indexfrag.avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(), null, null, null, ‘DETAILED’) indexfrag
inner join sys.indexes itable on itable.object_id = indexfrag.object_id
and itable.index_id = indexfrag.index_id
— make sure to set this where clause to the percentage below which you want to exclude results.
where indexfrag.avg_fragmentation_in_percent > 20
order by avg_fragmentation_in_percent desc, tablename