none
Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.name" could not be bound.

    Question

  • I want to get details about my databases.

    Can someone fix my query please;

     

    Select

    d.data_source as InstanceName, a.name as DatabaseName, b.name as DatabaseType,

    b

    .physical_name as DatabaseFilesPath, suser_sname(owner_sid) as Owner,

     

     

    (b.size /1024) as DataFileSizeMB,(b.growth /1024) as FileGrowthSizeMB,b.growth

    ,

    b

    .max_size, b.is_percent_growth as GrowthRateIsInPercent,

     

    @@VERSION as VersionDetails, a.compatibility_level as SQLServer,

     

    SERVERPROPERTY('productversion')as Version, SERVERPROPERTY ('edition')as ServerEdition,

     

    SERVERPROPERTY ('productlevel')as ServicePack, a.create_date,

    a

    .recovery_model_desc as RecoveryMode,a.state_desc as CurrentState, c.Name as FIleGroup,
     

     

    COALESCE(Convert(varchar(12), MAX(e.backup_finish_date), 101),'Not Yet Taken') as LastBackUpTaken,
     

     

    COALESCE(Convert(varchar(12), MAX(e.user_name), 101),'NA') as UserName
     

    From

    sys.databases a, sys.master_files b, sys.data_spaces c, sys.servers d,

     

    sys.sysdatabases LEFT OUTER JOIN msdb..backupset e ON e.database_name = a.name

     

    Where

     

    a.database_id = b.

    database_id

    and

     

    e.type=

    'D'

    or

     

    e.Type=

    'L'

    GROUP

     

    BY d.data_source, a.Name, e.

    type

    ORDER

     

    BY d.data_source, a.Name, e.

    type

    Tuesday, May 31, 2011 2:47 AM

Answers

  • Try:

    SELECT  a.name              AS DatabaseName, 
         b.name              AS DatabaseType, 
         b.physical_name         AS DatabaseFilesPath, 
         SUSER_SNAME(owner_sid)      AS Owner, 
         (b.size / 1024)         AS DataFileSizeMB, 
         (b.growth / 1024)        AS FileGrowthSizeMB, 
         b.growth, 
         b.max_size, 
         b.is_percent_growth       AS GrowthRateIsInPercent, 
         @@VERSION            AS VersionDetails, 
         a.compatibility_level      AS SQLServer, 
         SERVERPROPERTY('productversion') AS Version, 
         SERVERPROPERTY('edition')    AS ServerEdition, 
         SERVERPROPERTY('productlevel')  AS ServicePack, 
         a.create_date, 
         a.recovery_model_desc      AS RecoveryMode, 
         a.state_desc           AS CurrentState, 
         COALESCE(CONVERT(VARCHAR(12),MAX(e.backup_finish_date),101), 
             'Not Yet Taken') AS LastBackUpTaken, 
         COALESCE(CONVERT(VARCHAR(12),MAX(e.user_name),101), 
             'NA') AS UserName 
    FROM   sys.databases a 
         INNER JOIN sys.master_files b 
          ON a.database_id = b.database_id 
         LEFT OUTER JOIN msdb..backupset e 
          ON e.database_name = a.name 
           AND e.TYPE IN ('D','L') 
    GROUP BY a.Name, 
         b.name, 
         b.physical_name, 
         a.owner_sid, 
         b.size, 
         b.growth, 
         b.max_size, 
         b.is_percent_growth, 
         a.compatibility_level, 
         a.create_date, 
         a.recovery_model_desc, 
         e.TYPE, 
         a.state_desc 
    ORDER BY a.Name, 
         e.TYPE
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, May 31, 2011 3:05 AM
    Moderator

All replies

  • Two things before we start:

    1. Learn how to post code http://social.msdn.microsoft.com/Forums/en-AU/transactsql/thread/ce8989b5-dc58-47d8-af65-db5bcd4f7ee2
    2. Learn how to use correct JOIN methodology as the way you currently do it is very outdated.

    How are these meant to be joined?

    sys.databases a, sys.master_files b, sys.data_spaces c, sys.servers d, sys.sysdatabases LEFT OUTER JOIN msdb..backupset e ON e.database_name = a.name 
    


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Tuesday, May 31, 2011 2:59 AM
  • Try:

    SELECT  a.name              AS DatabaseName, 
         b.name              AS DatabaseType, 
         b.physical_name         AS DatabaseFilesPath, 
         SUSER_SNAME(owner_sid)      AS Owner, 
         (b.size / 1024)         AS DataFileSizeMB, 
         (b.growth / 1024)        AS FileGrowthSizeMB, 
         b.growth, 
         b.max_size, 
         b.is_percent_growth       AS GrowthRateIsInPercent, 
         @@VERSION            AS VersionDetails, 
         a.compatibility_level      AS SQLServer, 
         SERVERPROPERTY('productversion') AS Version, 
         SERVERPROPERTY('edition')    AS ServerEdition, 
         SERVERPROPERTY('productlevel')  AS ServicePack, 
         a.create_date, 
         a.recovery_model_desc      AS RecoveryMode, 
         a.state_desc           AS CurrentState, 
         COALESCE(CONVERT(VARCHAR(12),MAX(e.backup_finish_date),101), 
             'Not Yet Taken') AS LastBackUpTaken, 
         COALESCE(CONVERT(VARCHAR(12),MAX(e.user_name),101), 
             'NA') AS UserName 
    FROM   sys.databases a 
         INNER JOIN sys.master_files b 
          ON a.database_id = b.database_id 
         LEFT OUTER JOIN msdb..backupset e 
          ON e.database_name = a.name 
           AND e.TYPE IN ('D','L') 
    GROUP BY a.Name, 
         b.name, 
         b.physical_name, 
         a.owner_sid, 
         b.size, 
         b.growth, 
         b.max_size, 
         b.is_percent_growth, 
         a.compatibility_level, 
         a.create_date, 
         a.recovery_model_desc, 
         e.TYPE, 
         a.state_desc 
    ORDER BY a.Name, 
         e.TYPE
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, May 31, 2011 3:05 AM
    Moderator
  • Your from clause is a mixup:

    From sys.databases a, sys.master_files b, sys.data_spaces c, sys.servers d,

    sys.sysdatabases LEFT OUTER JOIN msdb..backupset e ON e.database_name = a.name

     

    The error is because you did not specify the alias a for sys.sysdatabases in the LEFT OUTER JOIN and a.name is referred in the ON part.

     

    Please decide on how you want to join the tables.

     


    The simpler the solution the stronger it is
    If this post answers you, please mark it as answer..
    If this post is useful, please vote it as useful..
    Tuesday, May 31, 2011 3:12 AM
  • Thanks Naomi,

    It worked like a charm. I also want to add instance and database name in the list as well so I can get details from all the registered servers in the environment.

    Also I want to know only the latest backup dates of both data and log databases but the query is showing last two backup dates for each of databases.  Could you please help me achieving that as well?

    Tuesday, May 31, 2011 5:30 AM
  • You already have database name in the query (first column). I'm not sure how to add servers names, as sys.servers view is not related to the others. In my case I got 2 records there.

    I'm also not understanding your last question - you get max date based on the fields you're grouping on.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, May 31, 2011 1:35 PM
    Moderator