Defragmentation

Query to find the fragmentation percent

SELECT OBJECT_NAME(A.OBJECT_ID),avg_fragmentation_in_percent,* 
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN SYS.INDEXES B on a.index_id = b.index_id
AND A.OBJECT_ID= B.OBJECT_ID 
AND database_id = DB_ID()
AND (index_type_desc ='CLUSTERED INDEX' or index_type_desc ='NONCLUSTERED INDEX')
ORDER BY 2

To Reorganize the Index can use the below code
ALTER INDEX INDEXNAME
ON TABLENAME REORGANIZE

Useful Links

1. Fragmentation and Defragmentation steps video

Sql Queries

To list the index of the table

SELECT 

     TableName = t.name,

     IndexName = ind.name,

     IndexId = ind.index_id,

     ColumnId = ic.index_column_id,

     ColumnName = col.name,

     is_included_column,

     ind.*,

     ic.*,

     col.* 

FROM 

     sys.indexes ind 

INNER JOIN 

     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 

INNER JOIN 

     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 

INNER JOIN 

     sys.tables t ON ind.object_id = t.object_id 

WHERE 

     ind.is_primary_key = 0 

     AND ind.is_unique = 0 

     AND ind.is_unique_constraint = 0 

     AND t.is_ms_shipped = 0 

     and t.name = 'lcp_productdetail'

ORDER BY 

     t.name, ind.name, ind.index_id, ic.index_column_id

To fetch the last executed SP

SELECT DB_Name(SqlTxt.[dbid]) AS [Database]     , 

Object_Name(SqlTxt.objectid, SqlTxt.[dbid]) AS [Stored Procedure]     , 

Max(stat.last_execution_time) As [Last Executed Time] FROM sys.dm_exec_query_stats AS stat     

CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) AS SqlTxt     INNER JOIN sys.procedures SP         

ON SP.object_id = SqlTxt.objectid     

where db_name(SqlTxt.[dbid])     = 'MMP_Generator'

and stat.last_execution_time >'2014-08-11 09:08:50.860'

AND SqlTxt.[dbid] IS NOT NULL GROUP BY 

db_name(SqlTxt.[dbid])     , 

Object_Name(SqlTxt.objectid, SqlTxt.[dbid]) ORDER BY 1, 2   

To change the identity value of a table

DBCC CHECKIDENT (yourtable, reseed, 34)

This will insert the new row with the identity value as 35.
Note:Even if already there is value 35 in the table,it will get repeated,so have to be careful if it is considered as the column for the uniqueness of the table.(go through the below link)

Useful Links

1. Identity reseed