To know which Tables need rebuild index in MSSQL

Some times we need to rebuild the index of the tables in MS-Sql database .In those case we consider the which tables real need the rebuild of the index.As per my knowledge below query is help to know which tables need  rebuild of the index .

Declare @dbid int
set @dbid=db_id()
Select convert(decimal(5,2), avg_fragmentation_in_percent) as  avg_fragmentation_percent,
object_name(d.object_id) as [table], as [name], d.index_type_desc,
alloc_unit_type_desc as [type],
index_depth, index_level,
fragment_count, page_count
from sys.dm_db_index_physical_stats( @dbid,null, -1, null, ‘SAMPLED’) d
inner join sys.indexes i on i.object_id=d.object_id and i.index_id=d.index_id
where  avg_fragmentation_in_percent>30

Delete All Procedures from a database in SQL Server

Sometime we need to delete the all the stored procedures in particular database .For this use the below script to generate to drop the all the store procedures. After execute this query it will gives script for drop store procedures with schema name.


 ‘DROP PROCEDURE ‘ +SCHEMA_NAME(schema_id)+‘.’+name+”

  as ‘CopyThisColumnAndExecuteToDeleteAllProcedures

  FROM     sys.procedures 

 WHERE      [type] = ‘P‘      AND  is_ms_shipped = 0

AND [name] NOT LIKE ‘sp[_]%diagram%

ORDER BY       [name] ASC