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:
> 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.|||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...
> > 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.
>|||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:
> 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...
> > > 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.
> >
> >|||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:
> 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:
> > 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...
> > > > 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.
> > >
> > >|||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...
> 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:
>> 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:
>> > 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...
>> > > > 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.
>> > >
>> > >|||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...
> HI thanks for the response. Is the dbid the database process id or just the
> database name?
> --
> Paul G
> Software engineer.
>
> "Hurme" wrote:
>> 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.