TRUNCATEONLY
Free space in the data files is released to the operating system and shrinks the file to the last allocated extent,
TARGET_PERCENT is ignored when TRUNCATEONLY is used.

TARGET_PERCENT : Specifies the % of the free space to be retained in the file

DBCC SHRINKDATABASE (SALES, 10) , - - Retains 10 % of free space

To get the list of the table sizes in the database

CREATE PROCEDURE dbo.Tables_Space

AS

CREATE TABLE #tblResults

(

   [name]   nvarchar(20),

   [rows]   int,

   [reserved]   varchar(18),

   [reserved_int]   int default(0),

   [data]   varchar(18),

   [data_int]   int default(0),

   [index_size]   varchar(18),

   [index_size_int]   int default(0),

   [unused]   varchar(18),

   [unused_int]   int default(0)

)

EXEC sp_MSforeachtable @command1=

         "INSERT INTO #tblResults

           ([name],[rows],[reserved],[data],[index_size],[unused])

          EXEC sp_spaceused '?'"

-- Strip out the " KB" portion from the fields

UPDATE #tblResults SET

   [reserved_int] = CAST(SUBSTRING([reserved], 1, 

                             CHARINDEX(' ', [reserved])) AS int),

   [data_int] = CAST(SUBSTRING([data], 1, 

                             CHARINDEX(' ', [data])) AS int),

   [index_size_int] = CAST(SUBSTRING([index_size], 1, 

                             CHARINDEX(' ', [index_size])) AS int),

   [unused_int] = CAST(SUBSTRING([unused], 1, 

                             CHARINDEX(' ', [unused])) AS int)

SELECT * FROM #tblResults order by rows desc.

exec Tables_Space

    Name - the name of the table

    Rows - the number of rows in the table

    Reserved - amount of total reserved space for the table

    Data - amount of space used by the data for the table

    Index_Size - amount of space used by the table's indexes

    Unused - amount of usused space in the table

Exec space_used 'Orders'

To get information about the index on the table

EXEC sp_helpindex 'Orders'

To get the fragmentation details

SELECT a.index_id, name, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Orders'),

     NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

GO

If avg_fragmentation_in_percent value  > 5% and < = 30%   ALTER INDEX REORGANIZE

> 30%  ALTER INDEX REBUILD WITH (ONLINE = ON)*

DBCC LOGINFO