Quck TSQL question with regards to sys.master_files

• ade-d

  Valued Member

  Points: 54

  More actions

  November 15, 2017 at 11:29 am

  #329605

  Hi,
  I hope this is the correct forum place to ask this question but I am getting an odd error with a query
SELECT
  db.name AS DBName,
  type_desc AS FileType,
  name AS Logical_name,
   Physical_Name AS Location
    
FROM
  sys.master_files mf
INNER JOIN
  sys.databases db ON db.database_id = mf.database_id

  It gives me an error Ambiguous column name 'name'. so who do i specify the column 'name' in sys.master_files?

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  November 15, 2017 at 11:34 am

  #1968245

  You're getting an error, because both sys.master_files and sys.databases have a column "name".  Just specify which one you want in your select statement.

  SELECT
    db.name AS DBName,
    type_desc AS FileType,
    mf.name AS Logical_name,
   Physical_Name AS Location
    
  FROM
    sys.master_files mf
  INNER JOIN 
    sys.databases db ON db.database_id = mf.database_id

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• ade-d

  Valued Member

  Points: 54

  More actions

  November 15, 2017 at 11:36 am

  #1968246

  thanks ever so much 🙂

• ScottPletcher

  SSC Guru

  Points: 100949

  More actions

  November 15, 2017 at 12:02 pm

  #1968250

  Best practice is to explicitly specify the table name/alias for every column in a query.  Otherwise, if one of the tables adds a column with the same name, the same error that you originally had could suddenly pop up again.

  
SELECT
    db.name AS DBName,
    mf.type_desc AS FileType,
    mf.name AS Logical_name,
    mf.Physical_Name AS Location

  FROM
    sys.master_files mf
INNER JOIN
    sys.databases db ON db.database_id = mf.database_id

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 15, 2017 at 12:12 pm

  #1968253

  ScottPletcher - Wednesday, November 15, 2017 12:02 PM

  Best practice is to explicitly specify the table name/alias for every column in a query.  Otherwise, if one of the tables adds a column with the same name, the same error that you originally had could suddenly pop up again.

  
SELECT
    db.name AS DBName,
    mf.type_desc AS FileType,
    mf.name AS Logical_name,
    mf.Physical_Name AS Location

  FROM
    sys.master_files mf
INNER JOIN
    sys.databases db ON db.database_id = mf.database_id

  I recommend that on single table queries.  You never know when you may add one or more tables to a query.