This post is mainly to answer a question in one of my earlier post https://latheeshnk.wordpress.com/2015/06/02/sp_msforeachdb-changing-recovery-model-for-all-databases/

Question:

Mr Yagnesh was looking for a script to shrink log file for all of his databases using sp_msforeachdb.

To answer the question, Yes, we can very well modify the script in the post to shrink the log files. However, SHRINKFILE file is not advisable in all situations, I would suggest to take that option with extreme case.

You may find the below script to *ONLY* generate the SHRINKFILE DBCC commands for all databases except system databases. The reason for generating the DBCC commands is to verify the commands before you execute in your environment as a first level of verification to avoid any surprises.

Script:

 --To avoid/exception on applying the change, you may add your databases in the list. 
EXEC sp_msforeachdb 'Declare @logname varchar(500) 
IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
begin 
        set @logname = (Select name From Sys.master_files where DB_ID(''?'') = database_id and type=1) 
        print ''Use [?];'' 
        print ''DBCC SHRINKFILE (['' + @logname + ''] ,100)'' 
end 
' 

Alternatively, there is very simple method as below:

Select 'Use [' + db_name(database_id) + '];DBCC SHRINKFILE(['+ name + '],100)' 
	From sys.master_files where type=1
		and db_name(database_id) not in ('tempdb','master','msdb','model','Reportserver','ReportserverTempDB')

EDIT: 19th May 2017

One of my friend had difficulty to change the above to a dynamic execution statement. The below is my try to help those find it difficult:

EXEC sp_msforeachdb ' Declare @logname varchar(500) = '''';

IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
begin 
	set @logname = (Select name From [?].Sys.database_files where  type=1) 

	EXEC(''Use [?];
		DBCC SHRINKFILE (['' + @logname + ''] ,100)'')  
end 
' 

See Also:

You may be interested to know more on sp_MSforeachdb usage, please visit the below links

https://gallery.technet.microsoft.com/T-SQL-Script-Get-Table-Row-94e49d01
https://gallery.technet.microsoft.com/How-to-change-datacompressi-47dfce48
https://latheeshnk.wordpress.com/2015/05/06/identify-references-of-an-object-in-sql-server/
https://latheeshnk.wordpress.com/2015/06/02/sp_msforeachdb-changing-recovery-model-for-all-databases/