TSQL select query help

Question

Hi All,

I have 2 tables. both tables hold db file sizes.
I want the difference of sizes using tsql. As of now, I am using excel for this.
During prod refresh activity to non-prod environments , we are falling short of drive spaces.
So, Checking for script to find size differences using tsql.

Providing sample data below:

CREATE TABLE [dbo].prod_db_size ON [PRIMARY]
GO
INSERT [dbo].[prod_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'PRODSRV', N'PRODDB', N'PRODDB_prm', N'G:\MSSQL\Data\PRODDB\PRODDB_prm.mdf', CAST(5.25 AS Numeric(18, 2)))
GO
INSERT [dbo].[prod_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'PRODSRV', N'PRODDB', N'PRODDB_log', N'K:\MSSQL\Log\PRODDB\PRODDB_log.ldf', CAST(283.44 AS Numeric(18, 2)))
GO
INSERT [dbo].[prod_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'PRODSRV', N'PRODDB', N'PRODDB_dat', N'G:\MSSQL\Data\PRODDB\PRODDB_dat.ndf', CAST(3559.67 AS Numeric(18, 2)))
GO
INSERT [dbo].[prod_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'PRODSRV', N'PRODDB', N'PRODDB_ind', N'G:\MSSQL\Data\PRODDB\PRODDB_ind.ndf', CAST(598.97 AS Numeric(18, 2)))
GO

----------------------------------

CREATE TABLE [dbo].dev_db_size ON [PRIMARY]
GO
INSERT [dbo].[dev_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'DEVSRV', N'DEVDB', N'DEVDB_prm', N'V:\MSSQL\Data\DEVDB\DEVDB_prm.mdf', CAST(5.25 AS Numeric(18, 2)))
GO
INSERT [dbo].[dev_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'DEVSRV', N'DEVDB', N'DEVDB_log', N'I:\MSSQL\Log\DEVDB\DEVDB_log.ldf', CAST(10.00 AS Numeric(18, 2)))
GO
INSERT [dbo].[dev_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'DEVSRV', N'DEVDB', N'DEVDB_dat', N'E:\MSSQL\Data\DEVDB\DEVDB_dat.ndf', CAST(3400.58 AS Numeric(18, 2)))
GO
INSERT [dbo].[dev_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'DEVSRV', N'DEVDB', N'DEVDB_ind', N'V:\MSSQL\Data\DEVDB\DEVDB_ind.ndf', CAST(571.52 AS Numeric(18, 2)))
GO

SELECT * FROM [prod_db_size]
SELECT * FROM [dev_db_size]
--expected output with new colum "size_diff"
SELECT *,size_diff FROM [dev_db_size]

Kind Regards,
Bob

Answer 1

Hi @Bob sql

--expected output with new colum "size_diff"

Please show your expected output with more detailed image or table output. For now, I can only guess based on your description.
If you want difference of [CurrentSizeGB] between these two tables, then check this query:

SELECT D.*,D.CurrentSizeGB-P.CurrentSizeGB AS size_diff  
FROM [dev_db_size] D JOIN [prod_db_size] P   
     ON SUBSTRING(D.FileName,CHARINDEX('_',D.FileName),3)= SUBSTRING(P.FileName,CHARINDEX('_',P.FileName),3)  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.