Compacting all MSSQL databases

Now that my hangover has just about worn off from a rough night out, I thought i’d post something useful. Below is a quick and (very) dirty way of clearing down the log and shrinking MSSQL datbases. Something useful for people who want to keep their Windows systems/networks tidy.


-- DECLARE STUFF.
DECLARE @rowCount int
DECLARE @rowCurrent int
DECLARE @rowData table (dbID int IDENTITY (1, 1), dbName varchar(50))
DECLARE @dbName varchar(50)
SET @rowCurrent = 0

-- Populate @rowData with list of databases on server.
INSERT INTO @rowData(dbName) SELECT name FROM sysdatabases

-- Number of rows in @rowData.
SELECT @rowCount = COUNT(*) FROM @rowData

-- Itherate through rows.
WHILE @rowCurrent < @rowCount
BEGIN
SELECT @dbName = dbName FROM @rowData WHERE dbID = @rowCurrent
PRINT  @dbName
BACKUP LOG @dbName WITH NO_LOG
DBCC SHRINKDATABASE (@dbName)
SET @rowCurrent = @rowCurrent + 1
END

If anyone has some suggestions, feel free to let me know.