/*
Credit for this script goes to:
DAVE TURPIN
http://www.daveturpin.com/2011/07/how-to-drop-a-tempdb-database-file/
*/

-- Is there data in the second file of tempdb?
--drop table #sfs
--drop table #fixed_drives
--drop table #output_table
--drop table #databases
--drop table #dbf
--drop table #fg


IF OBJECT_ID('tempdb..#sfs') IS NOT NULL
	DROP TABLE #sfs;

IF OBJECT_ID('tempdb..#fixed_drives') IS NOT NULL
	DROP TABLE #fixed_drives;

IF OBJECT_ID('tempdb..#output_table') IS NOT NULL
	DROP TABLE #output_table;

IF OBJECT_ID('tempdb..#databases') IS NOT NULL
	DROP TABLE #databases;

IF OBJECT_ID('tempdb..#dbf') IS NOT NULL
	DROP TABLE #dbf;

IF OBJECT_ID('tempdb..#fg') IS NOT NULL
	DROP TABLE #fg;

--------------------------
-- Save result set from showfilestats
--------------------------
CREATE TABLE #sfs (
	fileid TINYINT
	,filegroupid TINYINT
	,totalextents INT
	,usedextents INT
	,dbfilename SYSNAME
	,physfile VARCHAR(255)
	);

------------------------------
-- Save result set from sys.database_files
------------------------------
CREATE TABLE #dbf (
	[file_id] INT
	,file_guid UNIQUEIDENTIFIER
	,[type] TINYINT
	,type_desc NVARCHAR(60)
	,data_space_id INT
	,[name] SYSNAME
	,physical_name NVARCHAR(260)
	,[state] TINYINT
	,state_desc NVARCHAR(60)
	,size INT
	,max_size INT
	,growth INT
	,is_media_ro BIT
	,is_ro BIT
	,is_sparse BIT
	,is_percent_growth BIT
	,is_name_reserved BIT
	,create_lsn NUMERIC(25, 0)
	,drop_lsn NUMERIC(25, 0)
	,read_only_lsn NUMERIC(25, 0)
	,read_write_lsn NUMERIC(25, 0)
	,diff_base_lsn NUMERIC(25, 0)
	,diff_base_guid UNIQUEIDENTIFIER
	,diff_base_time DATETIME
	,redo_start_lsn NUMERIC(25, 0)
	,redo_start_fork_guid UNIQUEIDENTIFIER
	,redo_target_lsn NUMERIC(25, 0)
	,redo_target_fork_guid UNIQUEIDENTIFIER
	,back_lsn NUMERIC(25, 0)
	);

------------------------------
-- Save result set from sys.filegroups select * from sys.filegroups
------------------------------
CREATE TABLE #fg (
	[name] SYSNAME
	,data_space_id INT
	,[type] CHAR(2)
	,type_desc NVARCHAR(60)
	,is_default BIT
	,is_system BIT
	,[filegroup_id] UNIQUEIDENTIFIER
	,log_filegroup_id INT
	,is_read_only BIT
	);

-- Populate #disk_free_space with data 
CREATE TABLE #fixed_drives (
	DriveLetter CHAR(1) NOT NULL
	,FreeMB INT NOT NULL
	);

INSERT INTO #fixed_drives
EXEC master..xp_fixeddrives;

CREATE TABLE #output_table (
	DatabaseName SYSNAME
	,FG_Name SYSNAME
	,GB_Allocated NUMERIC(8, 2)
	,GB_Used NUMERIC(8, 2)
	,GB_Available NUMERIC(8, 2)
	,DBFilename SYSNAME
	,PhysicalFile SYSNAME
	,Free_GB_on_Drive NUMERIC(8, 2)
	);

SELECT NAME AS DBName
INTO #databases
FROM sys.databases
WHERE database_id <= 4
	AND state_desc = 'ONLINE';

DECLARE @dbname SYSNAME;

SELECT @dbname = (
		SELECT TOP (1) DBName
		FROM #databases
		);

DELETE
FROM #databases
WHERE DBName = @dbname;

WHILE @dbname IS NOT NULL
BEGIN
	-- Get the file group data
	INSERT INTO #sfs
	EXEC ('USE ' + @dbname + '; DBCC showfilestats;');

	INSERT INTO #dbf
	EXEC ('USE ' + @dbname + '; SELECT * FROM sys.database_files;');

	INSERT INTO #fg
	EXEC ('USE ' + @dbname + '; SELECT * FROM sys.filegroups;');

	-- Wrap it up!
	INSERT INTO #output_table (
		DatabaseName
		,FG_Name
		,GB_Allocated
		,GB_Used
		,GB_Available
		,DBFilename
		,PhysicalFile
		,Free_GB_on_Drive
		)
	SELECT @dbname AS DATABASE_NAME
		,fg.NAME AS [File Group Name]
		,CAST(((sfs.totalextents * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Allocated
		,CAST(((sfs.usedextents * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Used
		,CAST((((sfs.totalextents - sfs.usedextents) * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Available
		,sfs.dbfilename
		,sfs.physfile
		,CAST((fd.FreeMB / 1000.0) AS NUMERIC(8, 2)) AS Free_GB_on_Drive
	FROM #sfs sfs
	INNER JOIN #dbf dbf ON dbf.[file_id] = sfs.fileid
	INNER JOIN #fg fg ON fg.data_space_id = sfs.filegroupid
	INNER JOIN #fixed_drives fd ON fd.DriveLetter = SUBSTRING(sfs.physfile, 1, 1);

	SELECT @dbname = (
			SELECT TOP (1) DBName
			FROM #databases
			);

	IF @dbname IS NOT NULL
		DELETE
		FROM #databases
		WHERE DBName = @dbname;

	TRUNCATE TABLE #sfs;

	TRUNCATE TABLE #dbf;

	TRUNCATE TABLE #fg;
END

SELECT CONVERT(INT, CONVERT(CHAR, current_timestamp, 112)) AS CaptureDate
	,DatabaseName
	,FG_Name
	,GB_Allocated
	,GB_Used
	,GB_Available
	,DBFilename
	,PhysicalFile
	,Free_GB_on_Drive
FROM #output_table
ORDER BY DatabaseName
	,FG_Name

SET NOCOUNT ON; 

DECLARE @x INT

SET @x = 1

CREATE TABLE #MyTempTable (id BIGINT)

WHILE @x < 6553600
BEGIN
		;

	INSERT INTO #MyTempTable (id)
	VALUES (1)

	SET @x = @x + 1
END;