Space allocated to the databases by disk letters

  • Comments posted to this topic are about the item Space allocated to the databases by disk letters

  • CREATE PROCEDURE spDbFileAllocation
    @db VARCHAR(255) = ''
    AS BEGIN
    DECLARE @qry VARCHAR(MAX) = '';

    SET @qry = CONCAT(
    ';WITH tbl AS (
    SELECT *
    FROM (SELECT [dbName] = ''',@db,''',
    [DiskDrive] = SUBSTRING(physical_name, 1, 3),
    [GB] = (8.0*max(size))/1024/1024,
    name
    FROM sys.master_files GROUP BY SUBSTRING(physical_name, 1, 3), name) x
    PIVOT (
    max(GB)
    FOR name IN ([',@db,'], [',@db,'_log])
    ) p
    ) SELECT [dbName] = dbName,
    [Drive] = DiskDrive,
    [Database] = ',@db,',
    [LogFile] = ',@db,'_log,
    [TotalSize] = ',@db,' + ',@db,'_log
    FROM tbl');
    EXEC(@qry)
    END

    Great script!  Thanks.  Used it to create a generic procedure.  Pivoted the data as well.  I'm sure this could be improved on but it is doing the job for me.

  • Make sure you want to move all database or particular database.

    For all database above query is fine but for particular database can we use below queries,

    SELECT DB_NAME(database_id) AS DatabaseName,
    Name AS Logical_Name,
    Physical_Name,
    (size * 8) / 1024 SizeMB
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'database name'

    or

    use  databsename
    exec sp_spaceused

     

  • mikestahr wrote:

    Great script!  Thanks.  Used it to create a generic procedure.  Pivoted the data as well.  I'm sure this could be improved on but it is doing the job for me.

    That one is dangerous, the object names are being injected insecurely. @db should be properly parametrised for the literal strings (which should also be an nvarchar), which is impossible with syntax like EXEC(@SQL) instead of using sys.sp_executesql, and it should be injected securely for the dynamic objects with QUOTENAME. Also, the parameter for the proc should be a sysname; no need to open the injection attack more with an varchar(255).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply