deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can suggest
many thx
Assuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx
|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>
|||Hi,
Please find the script for the same:
------
--Name : s_RebuildIndices
--Author : Pallavi
--Description: Rebuilds all table indices
--Notes:
--Date: 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:

> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
>
>

deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can sugges
t
many thxAssuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>|||Hi,
Please find the script for the same:
----
--
--Name : s_RebuildIndices
--Author : Pallavi
--Description : Rebuilds all table indices
--Notes :
--Date : 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:

> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
>
>

deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can suggest
many thxAssuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>> Hi,
>> I need to delete approx 50 db on the same server. Instead of deleting
>> one
>> at a time, is there a better way? Is there a script that someone can
>> suggest
>> many thx
>|||Hi,
Please find the script for the same:
------
--Name : s_RebuildIndices
--Author : Pallavi
--Description : Rebuilds all table indices
--Notes :
--Date : 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:
> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> > Assuming you have only a couple of database that you want to keep, you
> > could create a script that uses a cursor to to select the databases
> > (master..sysdatabases) that you want to keep and then dynamically drop
> > eveything that is 'not in' your selected databases to keep.
> >
> > Be warned!!! - make sure you eliminate the system databases.
> >
> > If it were me, i'd just drop them manually just to be safe!
> > Immy
> > p.s. Ensure you have them all backed up! ;)
> >
> >
> > "stoney" <stoney@.discussions.microsoft.com> wrote in message
> > news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> >> Hi,
> >>
> >> I need to delete approx 50 db on the same server. Instead of deleting
> >> one
> >> at a time, is there a better way? Is there a script that someone can
> >> suggest
> >>
> >> many thx
> >
> >
>
>

deleteing small amount of records from a view causes IX lock on all the base tables.

Sorry that I had to post it as new message instead of replying since I got server application error.
Kalen, this is a different issue. I wonder why other 4 base tables got IX TAB lock as well since the partitioned view is supposed to look up the relevant tables only by querying on the constraint column.Tom
Can you please include relevant portions of the original message, so I can
know what I am replying to without having to search the archives?
If this is a question about partitioned views, did you supply the view
definition, and the version you are using?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:29346002-6963-4D4E-B63C-C6A5C5E292CD@.microsoft.com...
> Sorry that I had to post it as new message instead of replying since I got
server application error.
> Kalen, this is a different issue. I wonder why other 4 base tables got IX
TAB lock as well since the partitioned view is supposed to look up the
relevant tables only by querying on the constraint column.