There is a cool list of MetaData functions present in SQL server. I will be discussing one of them - object_id
Often we need to check if the Database is present and if it not then we need to create a new one. The most common answer to this is, try to connect to the Db and if the connection fails/throws an exception then the database does not exist. Do you think it is correct ?
It is definitely wrong. The connection may fail due to various reasons like - the server is down, the credentials are wrong and maybe the database do actually not exist. So the mentioned solution cannot be termed as the "solution". The right way to do it is, query the master database's sys.databases view to find out if the database exist. Now, master database is always present and sys.databases view contains all databases present on the server.
string strQuery = "select * from sys.databases where name ='" + databaseName + "'";
NOTE: For the above query connection should be to the master database. If you have connection string configured with a different database name then you will have to change it to point to master database. Like this,
conn.Open();
conn.ChangeDatabase("master");
//execute the query
If the query (i.e. strQuery) succeeds it means that the database if present. If the query fails then no such database exists.
Using this approach we have eliminated other causes that let down our first solution.
Leading to the next question - how would we find if a table exists?
Here comes the magic of MetaData functions. The cool "object_id" metadata function can be used to determine if a table exists.
The query would be,
string strQuery = "Select object_id('dbo." + tableNameToSearch + "', 'U')"
The parameter "U" indicates a table.
Note: For this query, the connection should be to the Database in which you want to find the table and not "master" database as in our previous example of finding database existence.
If the above query returns null then it means that there is no such table. If the table exists then the query returns an object_id.
Similarly, we can do the same for finding out if a Stored procedure exists.
string strQuery = "Select object_id('dbo." + storedprocNameToSearch + "', 'P')"
The parameter "P" indicates a stored procedure.
Note: For this query, the connection should be to the Database in which you want to find the stored procedure and not "master" as in our previous example of finding database existence.
If the above query returns null then it means that there is no such stored procedure. If the stored procedure exists then the query returns an object_id.
Have fun.
For more details, visit http://abitsmart.com/?p=123
|
| Author: Prasoon Kumar 29 Jun 2009 | Member Level: Silver Points : 1 |
Hi
Good one ,
I don't know the need of your project but rather than checking
if it exists or not better way would have to generate it using Dynamic
SQL
dynamically.
Regards
Prasoon.
|