How to check if a database name is exists in SqlServer

[Harvinder]

Hi Support,

How to check if a database name is exists in SqlServer Instance?

I would like to test if database 'DB_Test' is exists in the provided instance or not. How to check this in MSI?

Thanks,
Harvinder

[bokkie]

Good morning, Harvinder.

I'm a little rusty on the subject of SQL Server but I do recall looking in the registry for the database instance. Have a search with RegEdit or another registry utility of your choosing and see if it's in there. I think you will need to handle different versions of SQL Server. For example, I have a distant memory of seeing databases in a registry section something like 7.0, 8.0 for the different versions of SQL Server installed on machines. IA does have a registry plugin you can use to help get the registry information.

[Harvinder]

Hi,

I not looking for database instances or database version.

I want to know if a particular database name is exists in a provided sql server instance.

To check the same in SQL Server we have query:-

Code: Select all

if exists( SELECT database_id FROM sys.databases WHERE Name = 'DB_Test')
	print 'YES'
else
	print 'NO'

I want the result of this query in MSI code variable. And will implement the script logic accordingly.

-Harvinder

[FrancescoT]

Dear Harvinder,

Unfortunately, using a query I do not think you can do it, because the query it is not able to return a value.

So to do this you should include in the same query all the code necessary to create a database conditionally (... if this is your target).
Otherwise, as far as I know, you must create a MS SQL Stored Procedure ( ... but probably it is already available a similar function for it) and these can be used as function.

I'm not an MS SQL expert but doing a quick search over the web you should find something about it.

Hope this helps you.

Regards.

[BobSchaefer]

Do a search on my name and SQL and you'll find out how to trick the SQL plugin to spew the answer out into a variable within the script. It involves causing the SQL script to generate an error as its last step and telling the plugin to return any errors in a variable, then you parse the error for the value you want.

[FrancescoT]

Thanks for sharing!

Regards