The following T-SQL script will extract all data/log files for the currently attached databases, and then scan all of those directories to find files that are not being used by SQL Server. This will quickly locate mdf, ldf or ndf files located in your data and log directories to free up space.

SET NOCOUNT ON;
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'func_SplitPath')
	DROP FUNCTION dbo.func_SplitPath
GO
IF object_id('tempdb..#tmp_sql_files') IS NOT NULL
BEGIN
   DROP TABLE #tmp_sql_files
END
GO
IF object_id('tempdb..#tmp_os_files') IS NOT NULL
BEGIN
   DROP TABLE #tmp_os_files
END
GO
CREATE FUNCTION dbo.func_SplitPath
(@filename varchar(2048), @part int)
RETURNS varchar(2048)
AS
BEGIN

	DECLARE @pos AS int
	
	SET @pos = CHARINDEX('\',REVERSE(@filename))

	IF (@pos > 0)
		BEGIN
			SET @pos = LEN(@filename) - @pos
			IF (@part = 1) -- Directory
				RETURN LEFT(@filename,@pos)
			ELSE
				RETURN RIGHT(@filename,LEN(@filename) - @pos - 1)
		END
	RETURN ''
END
GO

DECLARE cur_dbs CURSOR
READ_ONLY
FOR SELECT name FROM master.dbo.sysdatabases WHERE DATABASEPROPERTYEX(name,'Status') = 'ONLINE'

DECLARE @dbname sysname
OPEN cur_dbs

CREATE TABLE #tmp_sql_files (filename varchar(2048) NOT NULL, dir varchar(2048) NULL)

FETCH NEXT FROM cur_dbs INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		DECLARE @sql varchar(2048)

		SET @sql = 'SELECT RTRIM(filename),NULL FROM [' + @dbname + '].dbo.sysfiles'

		INSERT INTO #tmp_sql_files
		EXEC(@sql)
		
	END
	FETCH NEXT FROM cur_dbs INTO @dbname
END


CLOSE cur_dbs
DEALLOCATE cur_dbs

-- Split the files and directories portions up
UPDATE #tmp_sql_files
SET dir = dbo.func_SplitPath(filename,1)

UPDATE #tmp_sql_files
SET filename = dbo.func_SplitPath(filename,0)



--- Get files in these directories from the OS
DECLARE cur_files CURSOR
READ_ONLY
FOR SELECT DISTINCT dir FROM #tmp_sql_files

DECLARE @dir varchar(2048)
OPEN cur_files

CREATE TABLE #tmp_os_files (filename varchar(2048))

FETCH NEXT FROM cur_files INTO @dir
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		DECLARE @cmd varchar(255)
		SET @cmd = 'dir /B ' + @dir
		
		INSERT INTO #tmp_os_files
		EXEC master..xp_cmdshell @cmd

		UPDATE #tmp_os_files
		SET filename = @dir + '\' + filename
		WHERE filename IS NOT NULL AND CHARINDEX('\',filename) = 0
	END
	FETCH NEXT FROM cur_files INTO @dir
END

CLOSE cur_files
DEALLOCATE cur_files

DELETE FROM #tmp_os_files
WHERE filename IS NULL

ALTER TABLE #tmp_os_files ADD dir varchar(2048) NULL;
GO
-- Split the files and directories portions up
UPDATE #tmp_os_files
SET dir = dbo.func_SplitPath(filename,1)

UPDATE #tmp_os_files
SET filename = dbo.func_SplitPath(filename,0)


-- files in data/log directories that are not being used by sql server
SELECT o.dir, o.filename
FROM #tmp_os_files o
	LEFT OUTER JOIN #tmp_sql_files s ON
		o.filename = s.filename
WHERE s.filename IS NULL
	AND (o.filename LIKE '%.mdf'
		OR o.filename LIKE '%.ldf'
		OR o.filename LIKE '%.ndf')

Ever wonder when the last time your table stats where updated (perhaps done automatically because auto_stats is on?). Here is your answer:

SELECT i.name AS [Index Name], STATS_DATE(i.id, i.indid) AS [Statistics Date]
FROM sysobjects o, sysindexes i
WHERE o.name = 'table_name' AND o.id = i.id

The following VBScript will retrieve the current hotfix for a specified SQL 2005 Server, and return that hotfix as a the return code. Useful in batch files for selectively applying unattended service packs.

Option Explicit
Dim sServer, sInstance, nVersion, bVerbose, sSQLServer

If (ParseCommandLine()) Then
	nVersion = GetSQLServerHotfix(sServer, sInstance)
	WScript.Quit(nVersion)
Else
	ShowUsage()
	WScript.Quit(-1)
End If


'*******************************************************************
'* GetSQLServerHotfix - Retrieves SQL 2005 server hotfix version #
'*******************************************************************
Function GetSQLServerHotfix(strServer, strInstance)
Dim oWMI, oRst, oRow, nHotfix, sSQLName, sVersion, sTemp, nPos

	nHotfix = 0

	If (UCase(sInstance) <> "MSSQLSERVER") Then
		sSQLName = "MSSQL$" & sInstance
	Else
		sSQLName = "MSSQLSERVER"
	End If

	Set oWMI = GetObject("winmgmts:\\" & strServer & "\root\Microsoft\SqlServer\ComputerManagement")
	Set oRst = oWMI.ExecQuery("SELECT PropertyStrValue FROM SQLServiceAdvancedProperty " _
					& " WHERE PropertyName='Version'" _
					& " AND ServiceName='" & sSQLName & "'",,48)

	For Each oRow in oRst
		sVersion = oRow.PropertyStrValue
		If (bVerbose) Then
			WScript.Echo("Server: " & sSQLServer)
			WScript.Echo("Version: " & sVersion)
		End If
		If (Len(sVersion) > 2) Then
			sTemp = sVersion
			nPos = InStr(3,sTemp,".")
			If (nPos > 0) Then
				sTemp = Mid(sTemp,nPos + 1)
				nPos = InStr(1,sTemp,".")
				If (nPos > 0) Then
					nHotfix = Left(sTemp,nPos-1)
					If (bVerbose) Then
						WScript.Echo("Hotfix: " & nHotfix)
					End If
				End If
			End If
		End If
	Next

	Set oRst = Nothing
	Set oWMI = Nothing

	GetSQLServerHotfix = nHotfix

End Function



'*******************************************************************
'* ParseCommandLine - Parses command line options into global vars
'*******************************************************************
Function ParseCommandLine()
Dim objArgs, i, sNextArg

	
	Set objArgs = WScript.Arguments
	For i = 0 To objArgs.Count - 1
		If (i < objArgs.Count - 1) Then
			sNextArg = objArgs(i+1)
		Else
			sNextArg = ""
		End If
		Select Case (UCase(objArgs(i)))
			Case "/SERVER":
				sSQLServer = sNextArg
				i = i + 1
			Case "/VERBOSE":
				bVerbose = True
			Case "/HELP":
				ParseCommandLine = False
				Exit Function
			Case "/H":
				ParseCommandLine = False
				Exit Function
		End Select
	Next

	' Check for valid arguments
	If (Len(sSQLServer) > 0) Then
		' Parse the server name/instance
		Dim nSlash
		nSlash = InStr(1,sSQLServer,"\")
		If (nSlash > 0) Then
			sInstance = Mid(sSQLServer,nSlash+1)
			sServer = Left(sSQLServer,nSlash-1)
		Else
			sInstance = "MSSQLSERVER"
			sServer = sSQLServer
		End If
		ParseCommandLine = True
	Else
		ParseCommandLine = False
	End If

End Function