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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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