check if database is in use via a script

Hi I have a large script that creates a database and imports data from
another database. If first drops the destination database if it exists. I
was wondering if there is a way to put conditional code in a script to check
if the database is in use and if so skip the script and display a warning
message? Since the script takes a few minutes to run this could save some
time! Thanks.
--
Paul G
Software engineer.Do a select from master..sysprocesses where dbid = <dbid>
If at least 1 record is returned then the db id in use.
--
MG
"Paul" wrote:

> Hi I have a large script that creates a database and imports data from
> another database. If first drops the destination database if it exists.
I
> was wondering if there is a way to put conditional code in a script to che
ck
> if the database is in use and if so skip the script and display a warning
> message? Since the script takes a few minutes to run this could save some
> time! Thanks.
> --
> Paul G
> Software engineer.|||You can use things like sysprocesses (etc, depending on your version) to che
ck whether there are
connection in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
> Hi I have a large script that creates a database and imports data from
> another database. If first drops the destination database if it exists.
I
> was wondering if there is a way to put conditional code in a script to che
ck
> if the database is in use and if so skip the script and display a warning
> message? Since the script takes a few minutes to run this could save some
> time! Thanks.
> --
> Paul G
> Software engineer.|||HI thanks for the response. Is the dbid the database process id or just the
database name?
--
Paul G
Software engineer.
"Hurme" wrote:
[vbcol=seagreen]
> Do a select from master..sysprocesses where dbid = <dbid>
> If at least 1 record is returned then the db id in use.
> --
> MG
>
> "Paul" wrote:
>|||Thanks for the information. I tried
select * from sysprocesses --
and it returned the name of the database along with the DB_ID, database ID,
although it did not return any information reguarding if in use. Using
MSSQL 2000 enterprise.
Paul G
Software engineer.
"Tibor Karaszi" wrote:

> You can use things like sysprocesses (etc, depending on your version) to c
heck whether there are
> connection in the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
>|||On Tue, 10 Jul 2007 23:04:08 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>You can use things like sysprocesses (etc, depending on your version) to ch
eck whether there are
>connection in the database.
sysprocesses will certainly tell you if any connection is defaulting
to a particular database, but I don't think it reveals that the
connection made to database Foo is referencing Bar..object. Perhaps
checking locks too would take care of that?
Roy Harvey
Beacon Falls, CT|||The DBID is the database ID of the database. If you execute sp_helpdb you
can find out what it is. If you don't see any records in the sysprocesses
table with that dbid then the db is not in use.
--
MG
"Paul" wrote:
[vbcol=seagreen]
> Thanks for the information. I tried
> select * from sysprocesses --
> and it returned the name of the database along with the DB_ID, database ID
,
> although it did not return any information reguarding if in use. Using
> MSSQL 2000 enterprise.
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>|||ok thanks for the information, so for each record that is returned that
means that the corresponding database is in use?
Paul G
Software engineer.
"Hurme" wrote:
[vbcol=seagreen]
> The DBID is the database ID of the database. If you execute sp_helpdb you
> can find out what it is. If you don't see any records in the sysprocesses
> table with that dbid then the db is not in use.
> --
> MG
>
> "Paul" wrote:
>|||YEs, each row represent a connection to the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:35FC149B-8748-46A4-9E06-59A621E59857@.microsoft.com...[vbcol=seagreen]
> ok thanks for the information, so for each record that is returned that
> means that the corresponding database is in use?
> --
> Paul G
> Software engineer.
>
> "Hurme" wrote:
>|||dbid is the id for the database. Use DB_NAME() to translate to database name
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:85F2D15F-6203-4C43-91E1-635D6BD448D5@.microsoft.com...[vbcol=seagreen]
> HI thanks for the response. Is the dbid the database process id or just t
he
> database name?
> --
> Paul G
> Software engineer.
>
> "Hurme" wrote:
>