quote:

---

Originally posted by SQLServerDBA_Dan

return LDF and MDF:
select filename from master..sysfiles
select filename from pubs..sysfiles
select filename from northwind..sysfiles

return MDF only:
select filename from master..sysfiles where filename like '%.mdf%'
select filename from pubs..sysfiles where filename like '%.mdf%'
select filename from northwind..sysfiles where filename like '%.mdf%'

enjoy.

Daniel
SQL Server DBA

---

-- Script to build a script sutiable for truncated logs on all database
-- NOTE: Default state is to just "EXEC" the truncate command
-- Make sure "Results in Text" is set in QA - [Use Control-T]
-- Script-mode shows the size of LOGs for each DB;  
-- you can truncate the lot, or use the Size as an indication of which ones 
-- would benefit from truncated

SET NOCOUNT ON
DECLARE	@strDatabase	sysname,
	@strSQL		varchar(8000)

DECLARE CUR_Database CURSOR FOR
	SELECT	[name]
	FROM	master.dbo.sysdatabases
	WHERE	    DATABASEPROPERTYEX(name, 'Status') <> 'OFFLINE'
		AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE'
	ORDER BY 1

OPEN CUR_Database
FETCH NEXT FROM CUR_Database into @strDatabase

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Build a SQL script to truncate this database.
	-- NOTE: "GO" cannot appear at the beginning of a line, so {GO} is substituted later
	-- NOTE: The blank lines separate each "job" a little
	SELECT	@strSQL = 
'USE {DATABASE}
--{GO}
SELECT	[--] = ''--'', 
	size, 
	[{DATABASE}] = name 
FROM	dbo.sysfiles
WHERE	convert(varchar(255), [name]) LIKE ''%\_log'' ESCAPE ''\'' 
--        AND size > 256
ORDER BY 1
SELECT TOP 100 
''USE {DATABASE}
{GO}
BACKUP LOG {DATABASE} WITH TRUNCATE_ONLY
{GO}
DBCC SHRINKFILE ('' + rtrim(name) +'', 1)
{GO}




''
FROM	dbo.sysfiles 
WHERE	RTrim(name) LIKE ''%\_log'' ESCAPE ''\'' 
--        AND size > 256	-- Use this to only target "big ones"
ORDER BY 1'

	SELECT	@strSQL = REPLACE(REPLACE(@strSQL, '{DATABASE}', @strDatabase), '{GO}', 'GO')
--	SELECT	[--SQL] = @strSQL	-- For debugging
	EXEC (@strSQL)

	FETCH NEXT FROM CUR_Database into @strDatabase
END

CLOSE CUR_Database
DEALLOCATE CUR_Database