Programmatically determine available free space on a database?

Question

Ok, I have tried searching around for this answer, but with no luck. I have about 50 databases on our SQL Server at work that I have to check almost every day to see if any space can be freed up via shrinking. Our drives tend to fill up a lot, so this is why it is an almost daily task.

Is there a quick way to check what databases actually have free space available? Does anyone know of a system/custom stored procedure or system view that can generate a list of every database and how much free space is available to be shrunk on that database?

By the way, using sql server 2005.

Answer 1

---

Run sp_spacedused for each database:

--temporary table to hold database names
CREATE TABLE #Databases (name varchar(255))

INSERT INTO #Databases
SELECT name FROM master..sysdatabases

DECLARE abc CURSOR FOR
   SELECT name FROM #Databases

DECLARE @name varchar(255)
DECLARE @sql nvarchar(1024)

OPEN abc

FETCH NEXT FROM abc INTO @name

WHILE @@FETCH_STATUS = 0 
BEGIN
   --build sql to switch to that database...
   SET @sql = 'USE '+@name
   PRINT @sql
   EXECUTE (@sql)

   --and run spaceused on it
   SET @sql = 'EXECUTE sp_spaceused @UpdateUsage=True'
   PRINT @sql
   EXECUTE (@sql)

   FETCH NEXT FROM ABC INTO @name
END

CLOSE abc
DEALLOCATE abc
DROP TABLE #Databases

---

Sample singular results

Execute T-SQL:

USE Contoso
EXECUTE sp_spaceused @UpdateUsage=True

Results:

database_name: Contoso
database_size:     85.13 MB
unallocated_space: 15.41 MB

reserved:         70,368 KB (68.7 MB)
data:             42,944 KB (41.9 MB)
index_size:       24,200 KB (23.6 MB)
unused:            3,224 KB ( 3.1 MB)

Shrink:

DBCC SHRINKDATABASE (Contoso)

Check space again:

EXECUTE sp_spaceused @UpdateUsage=True

Results:

database_name: Contoso
database_size:     69.81 MB
unallocated_space:  0.20 MB

reserved:         70,256 KB (68.6 MB)
data:             43,024 KB (42.0 MB)
index_size:       24,200 KB (23.6 MB)
unused:            3,032 KB ( 3.0 MB)