Database with no visible name

Question

Database with no visible name

Waldman

SSC Veteran

Points: 260
More actions
July 19, 2004 at 6:42 pm

#102116

I have a database attached to SQL2000 which appears to have no name or a space for the name and contains no tables, views or any thing else. However, in Enterprise Manager, it will not allow its deletion or renaming. How do I get rid of the db?
Thanks,
Mike W

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Interesting. How do you know there is a such database there? Can you confirm that by running sp_helpdb and paste the result here?

Waldman SSC Veteran Points: 260 More actions · Answer 2

I'll have to do that (sp_helpdb&nbsp when I'm back in the client's office tomorrow. In Enterprise Mgr, when listing attached databases, all the databases are shown including the one that has no name. I did do a search of the all the drives and there are no strange mdf or ldf files around.

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 3

Using Query Analyzer:

select name,dbid from master.dbo.sysdatabases

will list the name and database id (dbid) for all the databases on your server. Then using the dbid you can drop the database from the server with:

drop database QUOTENAME(db_name([dbid]))

eg: drop database QUOTENAME(db_name(14))

Julian Kuiters
juliankuiters.id.au

Waldman SSC Veteran Points: 260 More actions · Answer 4

Ran sp_helpdb and it returned list of databases with the first one nothing under name, db_size null, owner administrator, dbid=11, created Nov 20,2003, status=suspect, updateability= read, write,...

Error msg are 1038, level 15, cannot use empty object or column names.

Drop database quotename(db_name(dbid)) doesn't work 'cause there is not db_Name and hence generates the error.

Question: can the db be dropped based in the dbid alone?

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

Can you detatch the db from EM ?

(rightclick and detatchdb)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Waldman SSC Veteran Points: 260 More actions · Answer 6

Negative on EM detachdb. Returns error mgs #21776 - [SQL-DMO] The name " was not found in the Database collection. If the name is a qualified name, use [] to separate various parts of the name and try again.

Any more suggestions?

dlink SSC Eights! Points: 901 More actions · Answer 7

You might be able to update sysdatabases table in the master database with a new name and then try dropping it using the new name.

Good Luck!

Waldman SSC Veteran Points: 260 More actions · Answer 8

Already tried altering the master db sysdatabases and it would not allow the changes.

Hal White SSC-Addicted Points: 436 More actions · Answer 9

Look up -

system tables, direct updates

in BOL. This will show you how to alter system tables

Waldman SSC Veteran Points: 260 More actions · Answer 10

After much trial and error, the command to get rid of the db with no name is as follows:

Execute sp_detachdb @dbname=N' '

GO

Note the space between the single quotes. Thanks for all the comments and help tips.