Move All MDF - LDF Files to a new location

--Move All MDF - LDF Files to a new location
--write by Sushil Kumar Rout
CREATE PROCEDURE MoveAllDatabaseFile
@FileType CHAR (3),
@fullpath VARCHAR(500)
AS

IF @FileType not in ('MDF','LDF')
BEGIN
SELECT '@FileType must be MDF or LDF and @fullpath should be like N''C:\DatabaseFiles\'' the script was terminated' as
'ERROR'
SELECT 'example of right syntax - exec MoveAllDatabaseFile ''MDF'',N''C:\DatabaseFiles\''' as 'Example Script'
return
END
declare @dbname as varchar (100)
declare @dbfilename as varchar (100)
declare @runalter as varchar(500)
--Case LDF

IF @FileType = 'LDF'
BEGIN
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD
FOR
SELECT sysdb.name AS 'DBName',masterfiles.name as 'FileName'
FROM sys.master_files masterfiles,sys.sysdatabases sysdb
WHERE masterfiles.database_id = sysdb.dbid
AND masterfiles.type_desc = 'LOG' and masterfiles.database_id > 4 --not take system DB's
END

--Case MDF
ELSE
BEGIN
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD
FOR
SELECT sysdb.name as 'DBName',masterfiles.name as 'FileName'
FROM sys.master_files masterfiles,sys.sysdatabases sysdb
WHERE masterfiles.database_id = sysdb.dbid
AND masterfiles.type_desc = 'ROWS' and masterfiles.database_id > 4 --not take system DB's
END

--Start execute MDF or LDF
OPEN CRS_db
FETCH NEXT FROM CRS_db INTO @dbname,@dbfilename

WHILE @@FETCH_STATUS = 0
BEGIN

SET @runalter = 'Alter database ' + @dbname + ' modify file (name = ' + @dbfilename + ' , filename = N'''+ @fullpath +
@dbfilename + '.' + @FileType + ''')'

select (@runalter)

FETCH NEXT FROM CRS_db INTO @dbname,@dbfilename
END
CLOSE CRS_db
DEALLOCATE CRS_db

Find Used Index

SELECT o.name Object_Name,
SCHEMA_NAME(o.schema_id) Schema_name,
i.name Index_name,
i.Type_Desc,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id
JOIN
sys.dm_db_index_usage_stats AS s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE o.type = 'u'
-- Clustered and Non-Clustered indexes
AND i.type IN (1, 2)
-- Indexes that have been updated by not used
AND(s.user_seeks > 0 or s.user_scans > 0 or s.user_lookups > 0 );

Find UnUsed Index

SELECT o.name Object_Name,
i.name Index_name,
i.Type_Desc
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.object_id = i.object_id
LEFT OUTER JOIN
sys.dm_db_index_usage_stats AS s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE o.type = 'u'
-- Clustered and Non-Clustered indexes
AND i.type IN (1, 2)
-- Indexes without stats
AND (s.index_id IS NULL) OR
-- Indexes that have been updated by not used
(s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );