Home / Questions / 136565
Accepted
SeWaNsX
Asked: 2020-02-07 06:10:13 +0800 CST 2020-02-07 06:10:13 +0800 CST

Dynamic SQL, Know if the DB Exists SQL 2008?

  • 772

I pose my problem. I have a SQL Server 2008 Database Server in which new databases are constantly being created, many of them are also being deleted and I don't have complete knowledge of how the log works, because I can actually see which databases have been deleted, but a while ago it stopped showing many, so I think the LOG is deleted or moved.

I would like a Dynamic SQL where I indicate by some variable or cursor, all the bases that I want to consult if they exist and that for each one I return the value "exists" or "does not exist".

I have nothing advanced since I can't think of how I could do it. I thought with some cursor along with a while, but I can't really think of it.

Any idea how I could?

sql

2 Answers

  1. You can use a temporary table to have the names of the databases and from this make a query that returns what you need,

        CREATE TABLE #Tabla
    ( nombreDB    VARCHAR(30))

    SELECT 
         nombreDB,
        CASE 
            WHEN   name IS null  THEN 'NO Existe'
        ELSE   
           'Existe'
         END    AS  Estatus
   FROM master.dbo.sysdatabases    DB
   RIGHT JOIN   #Tabla             T
   ON   DB.name                     =               T.nombreDB

    with this you can make an SP or a script according to what you need.

    I hope it helps you.

    • 1
  2. Best Answer

    You can run this:

    declare @NombreBd varchar(50)
declare db cursor for
SELECT name FROM master.dbo.sysdatabases
open db
fetch next from db into @NombreBd
begin
    print @NombreBd
    fetch next from db into @NombreBd
    --Select o cualquier consulta con Exec(...)
end
deallocate db
    • 1