Database (Exists)

[NoKeS]

HI,
I want to know the code to see if a DataBase is already in the Server.
Exemple... If the database that I want to create (by code) is in the server, and if already exists I want to put a print on the screen, HELP ME PLEASE!

 

[cdukes]

If you run the following query on the master database, it will give you a list of all the databases on the server. Add a where clause with the name of the database you are looking for.

select name from sysdatabases
where name = 'XXX'

Hope this helps,

Chris Dukes

 

[NoKeS]

What is the master database? Sorry for the question but i'm a little bit "lammer" in SQL. I'm a trainee.

 

[NoKeS]

The code is this: And I want to create the tables in the destiny database only if the tables doesn't exist. If the tables exists in the destiny database it give's me a PRINT message 'The table database already exist', and only copy the databases that doesn't exist. HELP ME PLEASE!!! I HAVE TO DELIVERY THIS WORK TO THE END OF THE DAY!! PLEASE!

CODE:

create proc CriaTablesBD @DBDest varchar(30)
as begin

declare @CreateTable varchar(255)

declare Tabelas cursor for
select Name , ID
from sysobjects
where Name not like 'sys%'
and type ='U'

Declare @name varchar(255), @ID int

--variaveis usadas para as colunas de uma tabela
declare @NameCol varchar(50),
@lengthCol int,
@typeCol varchar(255),

open Tabelas
fetch Tabelas into @Name , @ID
while (@@FETCH_STATUS = 0)
begin
--** codigo ** --
--select @Name as Nome
--select @ID as ID

declare cursorColun cursor for
select c.Name , c.length , t.name
from syscolumns c , systypes t
where c.xtype=t.xtype
and ID=@ID

select @CreateTable='create table ' +@DBDest + '.dbo.' + @Name + '( '

open cursorColun
fetch cursorColun into @NameCol ,@lengthCol ,@typeCol

while (@@FETCH_STATUS = 0)
begin
select @CreateTable = @CreateTable + @NameCol + ' '+ @typecol + ' '
if (@typecol = 'varchar') begin
select @CreateTable = @CreateTable + '( '+ convert(varchar(10),@lengthCol) + ') '
end

select @CreateTable =@CreateTable + ' , '

fetch cursorColun into @NameCol ,@lengthCol ,@typeCol

end

close cursorColun
DEALLOCATE cursorColun
--**fim codigo **--
fetch Tabelas into @Name , @ID

select @CreateTable =substring (@CreateTable , 1, len(@CreateTable)- 1 )
select @CreateTable =@CreateTable + ')'

select @CreateTable
exec (@CreateTable )

end

close Tabelas
DEALLOCATE Tabelas

end

/*
CriaTablesBD 'DillyHistorico'
*/

 

[cdukes]

The master database is a database held in SQLServer that contains most of the information about the server itself. This includes what databases the server has.

I am presuming that you are trying to copy the tables from one database to another.

If so, you can use the following SQL Commands:

-- Check that database exists
IF NOT exists( SELECT @name = name from master..sysdatabases
where name = @DBDest )
BEGIN
-- Database does not exists
-- you will need to create it
-- You will need to check the syntax????
EXEC('CREATE DATABASE ' + @dbDest)
END

The syntax for checking if a table exists is:

if exists (select * from sysobjects where id = object_id('dbo.oas_element') and sysstat & 0xf = 3)

Hope this helps,

Chris Dukes

 

[tlbroadbent]

Nokes,

I read your plea and wondered why you haven't read the documentation for SQL Server. SQL Books Online contains all the information you've asked for. You'd surely find an answer more quickly than waiting for a reply in this forum.

Do you have the electronic documentation for SQL Server installed? If not read faq183-689. It tells what SQL Books Online are and how to obtain them. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[NoKeS]

I can't find how to do this in the msdn online

 

[cdukes]

Terry,

Correct me if I am wrong, but most things can be found in Books Online and other books/electronic media ( I have about 30 on my desk ranging from VB, ADO, XML and ODBC) and it will probably take me about 6 months to read them all and by then it will be all be out of date anyway.

I was under the impression that forums like these where for novices and professionals who wanted a quick answer to a question to point them in the right direction by people who have already been there and done it, rather than spending hours trying to find the answer in mounds of technical documentation.


Chris Dukes

 

[NoKeS]

If that can be found, can you tell me where I can find the solution for my problem?? Yes... because I've already searched and i've not found nothinhg that revolved my problem....

I want to create the tables in the destiny database only if the tables doesn't exist. If the tables exists in the destiny database it give's me a PRINT message 'The table database already exist', and only copy the databases that doesn't exist

 

[cdukes]

Use

if NOT exists (select * from sysobjects where id = object_id('TABLE_NAME_XXX') and sysstat & 0xf = 3)
BEGIN
CREATE TABLE TABLE_NAME_XXX
(

)
END

Chris