Let me first say that I never truncate my production databases! It’s a bad practice and in most cases you don’t win anything. A good article about this: Stop Shrinking Your Database Files. Seriously. Now.
Besides the downsides of truncating your files I’ve had situations where I had to restore several databases for read-only purposes to a server.
When you deal with a lot of databases and want to shrink all the files you have to query the values or look at the properties of each database. This is not a efficient way to do this and therefore I’ve created the a little script to generate a truncate script to do everything for you.
For SQL Server 2000
SELECT 'USE [' + d.name + ']; PRINT ''Trunking ' + af.filename + ' '' DBCC SHRINKFILE (N''' + f.name + ''' , 0, TRUNCATEONLY)' FROM sysaltfiles af INNER JOIN sysdatabases d ON f.dbid = d.dbid WHERE d.name NOT IN ('master', 'msdb', 'model', 'tempdb')
For SQL Server 2005 and up
SELECT 'USE [' + d.name + ']; PRINT ''Trunking ' + mf.physical_name + ' '' DBCC SHRINKFILE (N''' + mf.name + ''' , 0, TRUNCATEONLY)' FROM sys.master_files mf INNER JOIN sys.databases d ON mf.database_id = d.database_id WHERE d.name NOT IN ('master', 'msdb', 'model', 'tempdb')
I left out the system databases because I didn’t want to mess with them.