This script is very useful when you have large tables and queries against those table usually runs slow then you can find if that is due to fragmentation of the table and its indexes or not. This script gives detailed information about the fragmentation in percentage.

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = db_id(N'Database_Name');
SET @object_id = OBJECT_ID(N'Table_Name');
BEGIN
   SELECT *
   FROM sys.dm_db_index_physical_stats(@db_Id,@object_ID, NULL, NULL , 'DETAILED') AS IPS;
END
GO

Sample:

What is IN_ROW_DATA?

The allocation of data in a row is of three types

Here is beautiful script that gives the how much of data in you table is allocated into what type of allocation.

SELECT OBJECT_NAME(p.object_id) AS Object_Name
       , i.name AS Index_Name
       , ps.in_row_used_page_count AS IN_ROW_DATA
       , ps.row_overflow_used_page_count AS ROW_OVERFLOW_DATA
       , ps.lob_used_page_count AS LOB_DATA
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
WHERE OBJECTPROPERTY (p.[object_id], 'IsUserTable') = 1
order by IN_ROW_DATA desc,LOB_DATA

By running the above query you can know whether that table is used for Image or text data.

 Follow @sqlfrndz