Dynamically generate the dbcc shrink database syntax for all
of the databases on my box
select 'dbcc shrinkdatabase([' + name + '], TRUNCATEONLY);' from sys.databases where name <> 'master'
copy the output into a sql server query window and run it
I had issues with one database because it was restoring.
After removing that database from the list of queries, it ran rather quickly
and I got a gobs of disk space back. If you have databases on your machine that
you do not want to shrink, you can remove those from the list
For more information on SHRINKDATABASE
http://msdn.microsoft.com/en-us/library/ms190488.aspx
Update
You can use the following to generate a dynamic sql statement and execute it using EXEC
DECLARE @SQL VARCHAR(MAX)
select @SQL = COALESCE(@SQL,'') + '
dbcc shrinkdatabase([' + name + '], TRUNCATEONLY);' from sys.databases
where name not in ( 'master', 'model', 'tempdb', 'msdb')
print cast(@SQL as ntext)
EXEC(@SQL)
Update
You can use the following to generate a dynamic sql statement and execute it using EXEC
DECLARE @SQL VARCHAR(MAX)
select @SQL = COALESCE(@SQL,'') + '
dbcc shrinkdatabase([' + name + '], TRUNCATEONLY);' from sys.databases
where name not in ( 'master', 'model', 'tempdb', 'msdb')
print cast(@SQL as ntext)
EXEC(@SQL)