Help with a Join/ Update Statement.

I am trying to take the four tables I have and concatenate them into a
single table that has everything. I would have used a join operation
but the results are not consistence, especially if you have renamed a
database and or have multiple files for both the data and logs in a
single DB.
-TIA-

code:

/ ****************************************
**********
Script to calculate information about the Log Files
 ****************************************
**********/
CREATE TABLE #dbcc_sqlperf (
DB_Name varchar(100),
Log_Size decimal (28, 5),
Log_Used_Percent decimal (28, 5),
Status tinyint )
CREATE TABLE #logstats (
DB_ID tinyint,
DB_Name varchar(100),
File_Type varchar(10),
Total_Space_in_MB decimal (28, 2),
Used_Space_in_MB decimal (28, 2),
Free_Space_in_MB decimal (28, 2),
Percent_Used decimal (28, 2))
INSERT #dbcc_sqlperf EXEC ('dbcc sqlperf(logspace) with no_infomsgs')
-- SELECT * FROM #dbcc_sqlperf --Debug
INSERT #logstats (DB_Name, File_Type, Total_Space_in_MB,
Used_Space_in_MB, Free_Space_in_MB, Percent_Used)
SELECT DB_Name = DB_Name,
File_Type = 'Log',
Total_Space_in_MB = log_size,
Used_Space_in_MB = sum (log_size*(log_used_percent/100)),
Free_Space_in_MB = sum (log_size
-(log_size*(log_used_percent/100))),
Percent_Used = log_used_percent
FROM #dbcc_sqlperf
GROUP BY DB_Name, Log_Size, Log_Used_Percent, Status
update #logstats set DB_ID=(select dbid from master..sysdatabases
where name = #logstats.DB_Name)
where exists (select * from master..sysdatabases where name =
#logstats.DB_Name)
SELECT * FROM #logstats --Debug
/ ****************************************
******
Collects the data file size on the drive and
space used within the file.
 ****************************************
******/
DECLARE @.dbname varchar(50)
DECLARE @.string varchar(250)
DECLARE @.DB_ID tinyint
SET @.string = ''
CREATE TABLE #dbcc_showfilestats (
FileID tinyint,
FileGroup tinyint,
TotalExtents decimal (28, 2),
UsedExtents decimal (28, 2),
Name varchar(100),
FileName sysname)
CREATE TABLE #dbstats (
DB_ID tinyint,
Logical_Name varchar(100),
File_Type varchar(10),
Total_Space_in_MB decimal (28, 2),
Used_Space_in_MB decimal (28, 2),
Free_Space_in_MB decimal (28, 2),
Percent_Used decimal (28, 2))
SELECT name INTO #databases FROM master..sysdatabases -- SET UP
'CURSOR' TABLE
DECLARE @.unqName nvarchar(100) -- DEFINE LOOPING PARAMETER
SELECT @.unqName = name FROM #databases -- SELECT LOOPING PARAMETER
WHILE LEN(@.unqName) >
 0 -- ENTER WHILE LOOP
BEGIN
SET @.string = 'use ' + @.unqName + ' DBCC SHOWFILESTATS'
Select @.DB_ID = dbid from master..sysdatabases where name = @.unqName
INSERT #dbcc_showfilestats
EXEC (@.string)
-- SELECT * FROM #dbcc_showfilestats -- Debug
DECLARE @.rows int
SELECT @.rows = FileID from #dbcc_showfilestats
While @.rows >
 0
BEGIN
INSERT #dbstats (DB_ID, Logical_Name, File_Type, Total_Space_in_MB,
Used_Space_in_MB, Free_Space_in_MB, Percent_Used)
SELECT DB_ID = @.db_id,
Logical_Name = Name,
File_Type = 'Data',
Total_Space_in_MB = sum(TotalExtents)*65536.0/1048576.0,
Used_Space_in_MB = sum(UsedExtents)*65536.0/1048576.0,
Free_Space_in_MB =
sum(TotalExtents-UsedExtents)*65536.0/1048576.0,
Percent_Used = sum(UsedExtents/TotalExtents)*100
FROM #dbcc_showfilestats where FileID=@.rows
Group BY Name, FileName
SET @.rows = @.rows - 1
END
TRUNCATE TABLE #dbcc_showfilestats
-- PERFORM LOOP CODE
DELETE FROM #databases WHERE name = @.unqName -- DELETE ROW FROM
LOOPING TABLE #databases
SELECT @.unqName = '' -- CLEAR VARIABLE
SELECT @.unqName = name FROM #databases
END
SELECT * FROM #dbstats --Debug
/ ****************************************
**********
File information.
 ****************************************
**********/
CREATE TABLE #master_sysaltfiles (
DB_ID tinyint,
Logical_Name varchar(100),
File_Type varchar(10),
File_ID tinyint, -- 2 = logs, 1 = data
File_Path varchar(1000),
File_Max_Size_in_MB decimal(28,2), -- Maximum file size, in 8-KB
pages. -1 = Unlimited
File_Growth_in_MB decimal(28,2), -- 0 = No growth. Can be either
the number of pages or the percentage of file size, depending on the
value of status. If status is 0x100000, growth is the percentage of
file size;
 otherwise, it is the number of pages.
File_Growth_Percent decimal(28,2))
INSERT #master_sysaltfiles (DB_ID, Logical_Name, File_Type, File_ID,
File_Path, File_Max_Size_in_MB, File_Growth_in_MB, File_Growth_Percent)
SELECT
DB_ID = dbid,
Logical_Name = name,
File_Type = CASE WHEN groupid = 1 THEN 'Data' ELSE 'Log'
END,
File_ID = fileid,
File_Path = filename,
File_Max_Size_in_MB = convert(decimal(28,2),maxsize)*8192/1048576,
File_Growth_in_MB = CASE WHEN status <
= 0x100000
THEN convert(decimal(28,2),growth)*8192/1048576
ELSE convert(decimal(28,2),growth)*size*8192/1048576/100 END,
File_Growth_Percent = CASE WHEN status <
= 0x100000
THEN convert(decimal(28,2),growth)/size*100
ELSE convert(decimal(28,2),growth) END
FROM master..sysaltfiles
Group By dbid, fileid, filename, name, groupid, size, maxsize, status,
growth
-- SELECT * FROM sys.master_files -- SQL 2005 Only
-- SELECT * FROM master..sysaltfiles
SELECT * FROM #master_sysaltfiles --Debug
-- DROP TABLE #sys_master_files
/ ****************************************
*******************
Returns a the STATUS of all databases on a server in English
 ****************************************
*******************/
CREATE TABLE #master_sysdatabases (
DB_ID int,
DB_Name varchar(100),
Compatability_Level int,
File_Version int,
Status_1 varchar(1000),
Status_2 varchar(1000))
INSERT #master_sysdatabases (DB_ID, DB_Name, Compatability_Level,
File_Version, Status_1, Status_2)
SELECT
DB_ID = DBID,
DB_Name = name,
Compatability_Level = CMPTLEVEL,
File_Version = version,
Status_1 = SUBSTRING(
CASE status & 1 WHEN 0 THEN '' ELSE ', Autoclose' END
+
CASE status & 4 WHEN 0 THEN '' ELSE ', Select Into / Bulk
Copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ', Truncate Log on
Checkpoint' END +
CASE status & 16 WHEN 0 THEN '' ELSE ', Torn Page Detection'
END +
CASE status & 32 WHEN 0 THEN '' ELSE ', Loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ', Pre-Recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ', Recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ', Not Recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ', Offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ', Read Only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ', dbo USE Only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ', Single User' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ', Emergency Mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ', Autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ', Cleanly Shutdown'
END,
2,8000),
Status_2 = SUBSTRING(
CASE status2 & 16384 WHEN 0 THEN '' ELSE ', ANSI NULL
Default' END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ', Concatenate NULL
Yields NULL' END +
CASE status2 & 131072 WHEN 0 THEN '' ELSE ', Recursive Triggers'
END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ', Default to Local
Cursor' END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ', Quoted Identifier'
END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ', Cursor CLOSE on
Commit' END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ', ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ', ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ', Full Text Enabled'
END,
2,8000)
FROM master..sysdatabases
-- select * from master..sysdatabases
Select * From #master_sysdatabases
Create Table #tmp (
DB_ID tinyint,
File_ID tinyint, -- 2 = logs, 1 = data
DB_Name varchar(100),
File_Type varchar(10),
Total_Space_in_MB decimal(28,2),
Used_Space_in_MB decimal(28,2),
Free_Space_in_MB decimal(28,2),
Percent_Used decimal(28,2),
Logical_Name varchar(100),
File_Path varchar(1000),
File_Max_Size_in_MB decimal(28,2), -- Maximum file size, in 8-KB
pages. -1 = Unlimited
File_Growth_in_MB decimal(28,2), -- 0 = No growth. Can be either
the number of pages or the percentage of file size, depending on the
value of status. If status is 0x100000, growth is the percentage of
file size;
 otherwise, it is the number of pages.
File_Growth_Percent decimal(28,2),
Compatability_Level int,
File_Version int,
Status_1 varchar(1000),
Status_2 varchar(1000))
/ ****************************************
*******************
Concatenates all the above tables into a single table
 ****************************************
*******************/
Insert #tmp (DB_ID, Logical_Name, File_Type, File_ID, File_Path,
File_Max_Size_in_MB, File_Growth_in_MB, File_Growth_Percent)
SELECT DB_ID, Logical_Name, File_Type,
 File_ID, File_Path, File_Max_Size_in_MB,
File_Growth_in_MB, File_Growth_Percent
FROM #master_sysaltfiles
update #tmp
set DB_Name=DB_Name, Compatability_Level=Compatability_Level,
File_Version=File_Version, Status_1=Status_1, Status_2=Status_2
where exists (select * FROM #master_sysdatabases where DB_ID = DB_ID)
Select * From #tmp
Drop table #tmp
DROP TABLE #master_sysdatabases
DROP TABLE #dbstats
DROP Table #databases
DROP TABLE #dbcc_showfilestats
DROP TABLE #logstats
DROP TABLE #dbcc_sqlperf
DROP TABLE #master_sysaltfiles

No one willing to give this a shot? I have joined the first two tables
but now I need to update/replace all null values in the tmp table with
values from the other two table. Based upon weather is a data file or a
log.

code:

/ ****************************************
*******************
Concatenates all the above tables into a single table
 ****************************************
*******************/
Create Table #tmp (
DB_ID tinyint,
File_ID tinyint, -- 2 = logs, 1 = data
DB_Name varchar(100),
File_Type varchar(10),
Total_Space_in_MB decimal(28,2),
Used_Space_in_MB decimal(28,2),
Free_Space_in_MB decimal(28,2),
Percent_Used decimal(28,2),
Logical_Name varchar(100),
File_Path varchar(1000),
File_Max_Size_in_MB decimal(28,2), -- Maximum file size, in 8-KB
pages. -1 = Unlimited
File_Growth_in_MB decimal(28,2), -- 0 = No growth. Can be either
the number of pages or the percentage of file size, depending on the
value of status. If status is 0x100000, growth is the percentage of
file size;
 otherwise, it is the number of pages.
File_Growth_Percent decimal(28,2),
Compatability_Level int,
File_Version int,
Status_1 varchar(1000),
Status_2 varchar(1000))
Insert Into #tmp (DB_ID, Logical_Name, File_Type, File_ID, File_Path,
File_Max_Size_in_MB,
File_Growth_in_MB, File_Growth_Percent,
File_Version,Compatability_Level,
Status_1, Status_2)
SELECT #master_sysaltfiles.*,
#master_sysdatabases.File_Version,
#master_sysdatabases.Compatability_Level,
#master_sysdatabases.Status_1,
#master_sysdatabases.Status_2
FROM #master_sysaltfiles INNER JOIN #master_sysdatabases ON
#master_sysaltfiles.DB_ID = #master_sysdatabases.DB_ID