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 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.
|||You can use things like sysprocesses (etc, depending on your version) to check 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 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.
|||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 check 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 check 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 the
> database name?
> --
> Paul G
> Software engineer.
>
> "Hurme" wrote:
sql