Test for existence of databases on the local and/or a linked server

Working with many databases on many servers can be a drag sometimes. In special situations - especially when using dynamic queries - it is imperative to test for existence of linked db's programmatically. I use this function:

create function dbo.fnGet_dbExists
 (
 @dbName varchar(8000)
 )
returns bit
as
begin
 declare @result  bit
 declare @serverName varchar(128)
 declare @serverExists bit

 if (@dbName like '%.%')
  begin
   set @serverName = substring(@dbName, 1, charindex('.', @dbName) - 1)
   set @dbName = substring(@dbName, charindex('.', @dbName) + 1, len(@dbName))

   if (@serverName = serverproperty('ServerName'))
    begin
     set @serverName = null
    end
  end
 else
  begin
   set @serverName = null
  end

 if (@serverName is not null)
  begin
   if (exists (
     select master.dbo.sysservers.srvid
      from master.dbo.sysservers
      where (master.dbo.sysservers.srvname = @serverName)
     ))
    begin
     set @serverExists = 1
    end
   else
    begin
     set @serverExists = 0
    end
  end
 else
  begin
   set @serverExists = 1
  end

 if (@serverExists = 1)
  begin
   if (@serverName is not null)
    begin
     if (exists (
       select RemoteC.catalog_name
        from master.dbo.sysremote_catalogs(@serverName) RemoteC
        where (RemoteC.catalog_name = @dbName)
       ))
      begin
       set @Result = 1
      end
     else
      begin
       set @Result = 0
      end
    end
   else
    begin
     if (db_id(@dbName) is not null)
      begin
       set @Result = 1
      end
     else
      begin
       set @Result = 0
      end
    end
  end
 else
  begin
   begin
    set @result = 0
   end
  end

 return @result
end
go

The name of the database passed as @dbName must be qualified with the name of the linked server. For databases on the local server such qualifications can be omitted. Examples:

1. To test for existence of a database named "Product" on the linked server named "Vienna" dbName should contain the value "Vienna.Product".
2. If the local server is named "Berlin" both "Berlin.Product" and "Product" are valid values.

I hope you find good use of this function. ML