SQL Server Performance Forum – Threads Archive

How to get the name of database from the data file

[?]I found a mdf file in one folder …say db1.mdf in D:data.When i tried to delete it ..i got a msg which says I cannot delete this file as its being used by some other program.Now My question is :is there any way by which we can know which database is using this file.i have 400 database in one server ,so i cannot check the physical location of data file for each and every database.
ne other short way..[xx(]

---

From the query anazlyer run SP_HELPDB using each database on the server . Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Name of the DB will be part of the datafile and logfile. this is the common procedure everyone follow <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />) . Northwind database:- datafile like northwind.Mdf,logfile like northlog.ldf .<br /><br />try followin also .<br /><br />use northwind<br />Go<br />sp_helpfile<br />Go<br /><br />ur scenarios are good <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

But, if not, sp_helpdb is the way.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.

---

well u can try these sps too.
1> sp_who
2>sp_who2

---

That gives you the number of connections against the databases and not the associated physical name of the database.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

for individual DB fire query
use DB
select * from sysfiles
Go
in QA & it will populate the current DB description

---

You can create one tmp table,where you can store all the database name and their location
here is the Eg: CODE:
Use Tempdb
–this will Create Tmp Table
select * into #Tmp1 from sysfiles where 1=2 –Then Exec this SP
EXEC sp_Msforeachdb "insert into #Tmp1 select * from ?..sysfiles" –Then You can Query Tmp Table for database file path
select * from #Tmp1 where filename=’D:datadb1.mdf’ Rajeev Kumar Srivastava
–ALWAYS BE POSITIVE!–

---

Try this select name from sysdatabases where filename like ‘%pubs.mdf’

---

<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />sometimes simple things dosent strike mind.thank u moonwalker2000

---

It is a general recommendation to NOT to query system tables directly when there are pre-supplied system stored procedures are available. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Did someone mention that not all MDF and LDF files that you find on a SQL Server machine are necessarily in use?

---

I think the general recommendation is not to manipulate system tables directly but to use the supplied stored procs. Althought I have done this also to allow non sa the ability to manage security. Your sp_helpdb dbname suggestion is a lot of work for 400 databases. Another example: I had to findout which of the 250 views that we use point to tables in older databases that we are going to discontinue. I used the following: select object_name(c.id) from syscomments c
join sysobjects o on o.id = c.id
where o.type = ‘V’
and c.ctext like ‘%DBNAME_%’
I found 6 in about 2 seconds. How would you do this run sp_helptext on all 250 views and try and find the text needed?

---

The question was about database name, and no "I lost my databases names all the time".
So, the natural answer is to use what SQL in standard way provide. Of course with 400 databases will take some time, but allways is better to recommend something easy to check using BOL. Sometimes you can write a nice query but the member does not have idea how it work.
Aaronsandy like your solution, does not means Satya is wrong in his comment.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.

---

The text column of syscomments is NVARCHAR(4000) and so scripts can span multiple rows. You have to catch consecutive rows where the string starts at the end of the first row but is completed only on the next row: SELECT OBJECT_NAME(C.[id])
FROM dbo.syscomments AS C
INNER JOIN dbo.sysobjects o on c.id = o.id
WHERE o.type = ‘V’ AND C.text LIKE ‘%DBNAME_%’
UNION
SELECT OBJECT_NAME(C.[id])
FROM dbo.syscomments AS C
INNER JOIN dbo.sysobjects o on c.id = o.id
INNER JOIN dbo.syscomments AS C2 ON C.[id] = C2.[id]
WHERE o.type = ‘V’ AND C2.colid = C.colid + 1
AND C.text + C2.text LIKE ‘%DBNAME_%’

---

Thank You Adriaan. I knew if I put the select script up somebody would improve it for me. Patrick

---

]]>