multi-part identifier "f.name" could not be bound

Question

Hi All,

I am running the below query to fetch inmemory file details using powershell.

SELECT getdate() as AuditTime,@@SERVERNAME AS InstanceName,DB_NAME() Database_Name,f.name,f.physical_name,fg.type_desc,* FROM sys.filegroups FG   
               JOIN sys.database_files F  
			     ON FG.data_space_id = F.data_space_id  
     WHERE FG.type = ''FX'' AND F.type = 2  

But it throws the below error on some instances.

Failed during execution | The multi-part identifier "f.name" could not be bound.

Can you please help?

Thanks,
ACDBA

Answer 1

I am not sure that a "solution" without understanding have a lot of value since you will have such issue in the future.

The explanation is very simple

case sensitivity of Object names depend on the COLLATE

Here is a simple example of two databases. First one is using COLLATE which is Case Sensitive. Therefore we can create two tables named T and t. In this database you query will returns error since T and t are not the same.

create database Hebrew_100_CS_AS COLLATE Hebrew_100_CS_AS  
use Hebrew_100_CS_AS  
CREATE TABLE T(id int)  
CREATE TABLE t(id int)  
-- No issue using 2 object with the same name with upper and lower case  
-- Since the database COLLATE is case sensitive  
  
SELECT getdate() as AuditTime,@@SERVERNAME AS InstanceName,DB_NAME() Database_Name  
,f.name,f.physical_name,FG.type_desc,* FROM sys.filegroups FG  
JOIN sys.database_files F  
ON FG.data_space_id = F.data_space_id  
-- ERROR! The multi-part identifier "f.physical_name" could not be bound.  
----------------------------------------  
----------------------------------------  
create database Hebrew_100_CI_AS COLLATE Hebrew_100_CI_AS  
use Hebrew_100_CI_AS  
CREATE TABLE T(id int)  
CREATE TABLE t(id int)  
-- Error! There is already an object named 't' in the database.  

SELECT getdate() as AuditTime,@@SERVERNAME AS InstanceName,DB_NAME() Database_Name  
,f.name,f.physical_name,FG.type_desc,* FROM sys.filegroups FG  
JOIN sys.database_files F  
ON FG.data_space_id = F.data_space_id  
-- No error :-)   

Answer 2

SELECT getdate() as AuditTime,@@SERVERNAME AS InstanceName,DB_NAME() Database_Name
,F.name,F.physical_name,FG.type_desc,* FROM sys.filegroups FG
JOIN sys.database_files F
ON FG.data_space_id = F.data_space_id
WHERE FG.type = ''FX'' AND F.type = 2