DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find out the available free space in database files?

Execute the following SQL Server T-SQL example scripts in Management Studio Query Editor to create and test a stored procedure for database file free space check.

-- SQL Server stored procedure - SQL Server free space in database files

USE MASTER;

GO

CREATE PROC SpaceFree

           @Database SYSNAME

AS

  BEGIN

    DECLARE  @sqlCommand NVARCHAR(1024)

    

    SET @sqlCommand = 'USE ' + @Database + ' ' +

    'SELECT DatabaseFile = name, FreeSpaceMB=size/128.0

    -CAST(FILEPROPERTY(name, ''SpaceUsed'' )AS int)/128.0 ' +

    'FROM dbo.SYSFILES'

    

    PRINT @sqlCommand

 /*

 USE AdventureWorks SELECT DatabaseFile = name, FreeSpaceMB=size/128.0

    -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 FROM dbo.SYSFILES

 */

    

    EXEC sp_executesql  @sqlCommand

  END

GO

 

-- Stored procedure execution

EXEC SpaceFree 'AdventureWorks'

GO

/* Results

 

DatabaseFile            FreeSpaceMB

AdventureWorks_Data     3.187500

AdventureWorks_Log      1.125000

*/

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE