List out all MDF and LDF File locations of SQL Sever Databases

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN
    sys.databases db ON db.database_id = mf.database_id

Move User Databases

USE master; GO -- Return the logical file name. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2012') AND type_desc = N'LOG'; GO ALTER DATABASE AdventureWorks2012 SET OFFLINE; GO -- Physically move the file to a new location. -- In the following statement, modify the path specified in FILENAME to -- the new location of the file on your server. ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = AdventureWorks2012_Log, FILENAME = 'C:\NewLoc\AdventureWorks2012_Log.ldf'); GO ALTER DATABASE AdventureWorks2012 SET ONLINE; GO --Verify the new location. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2012') AND type_desc = N'LOG';

Space occupied by each Schema in SQL Server database

SELECT  SCHEMA_NAME(so.schema_id) AS SchemaName
               ,SUM(ps.reserved_page_count) * 8.0 / 1024 AS SizeInMB
        FROM    sys.dm_db_partition_stats ps
        JOIN    sys.indexes i
          ON    i.object_id                                     =           ps.object_id
         AND    i.index_id                                      =           ps.index_id
JOIN sys.objects so
  ON i.object_id = so.object_id
       WHERE    so.type = 'U'
    GROUP BY so.schema_id
    ORDER BY OBJECT_SCHEMA_NAME(so.schema_id), SizeInMB DESC