Finally i'm back after a month of crazyness due to my webhost not performing up to its promise of 99.9% uptime.
Enough with the rant and on with the article, today i'm going to talk about how to list databases in a SQL Server by just using T-SQL and then showing you how to truncate the logs on those databases in different ways.
First, let's look at the 5 different approaches that you can use to list down databases in SQL Server by just using plain old T-SQL.
The stored procedure approach ( sp_databases, sp_helpdb ) :
USE master;
-- Lists databases that either reside in an instance of the SQL Server
-- 2005 Database Engine or are accessible through a database gateway.
EXEC sp_databases
-- Reports information about a specified database or all databases.
EXEC sp_helpdb
The sys tables approach ( sys.databases, sys.sysdatabases ):
USE master;
-- [SQL Server 2000/2005] Contains one row for each database in an instance of
-- Microsoft SQL Server 2005. When SQL Server is first installed, sysdatabases
-- contains entries for the master, model, msdb, and tempdb databases.
SELECT NAME FROM sysdatabases
-- [SQL Server 2005] Contains one row per database in the instance of Microsoft SQL Server.
SELECT NAME FROM sys.sysdatabases
The using the undocumented stored procedure sp_MsForEachDatabase approach
USE master;
-- Undocumented SQL Server stored procedure
EXEC sp_msForEachDB 'PRINT ''?''';
Simple huh? Now that we know how to list databases let's go back to the problem of truncating all of them in one query (in fact it's just one line!). Some people would suggest writing cursors that would loop thru all the rows returned by our sysdatabases query to do this task. Their solution might be similar to the one listed below:
[SOLUTION 1]
USE master;
DECLARE
DBNames CURSOR
FOR
SELECT
NAME
FROM sysdatabases
OPEN DBNames
DECLARE @Name varchar(50)
FETCH NEXT FROM DBNames
INTO @Name
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DBCC SHRINKDATABASE( @Name , 0)
FETCH NEXT FROM DBNames
INTO @Name
END
CLOSE DBNames
DEALLOCATE DBNames
Others would suggest a more primitive approach which is building a simple query first that would list the names of the database appended with the DBCC SHRINKDATABASE command:
[SOLUTION 2]
USE master;
SELECT
'DBCC SHRINKDATABASE(' + NAME + ', 0)'
FROM sysdatabases
Then they would change the output option of the query into "Results to Text", copy the result to a new query window and execute the query from there. Pretty primitive. Alot of steps. Same results.
My suggested solution is using the sp_MSForEachDatabase procedure in conjunction with the DBCC SHRINKDATABASE function. This would result into a 1 line query. Less code with less steps to do that creates the same results.
[MY RECOMMENDED SOLUTION]
USE master;
-- truncates all the logs of all database in the server
EXEC sp_msForEachDB 'DBCC SHRINKDATABASE( ''?'', 0)'
Simple and straight to the point.
I hope you learned something from our article today. You can also do this programmatically by using C# and VB.NET. I wrote two articles about that topic here and here. Interested in truncating tables using sp_MsForEachTable? You can checkout this post.
Thanks!