How to rename database file programmatically?

I have two databases:

• database_A. Name of file is database_A.mdf.
• database_B. Name of file is database_B.mdf.

Then I what I do:

1. Drop database_A.
2. Rename database_B to database_A: sp_renamedb 'database_B','database_A.

However, name of file is still database_B.mdf.

Is it possible just by code rename database_B.mdf to database_A.mdf without setting path to location of databases? I cannot use Management Studio to deattach database.

Something like this but, without setting path name:

ALTER DATABASE database_name
   MODIFY FILE ( NAME = logical_file_name, 
                 FILENAME = ' new_path/os_file_name_with_extension ' )

I don’t know whether it will help to clarify why I want it, however, I will try why I need it. It is necessary to me because when I will create database_A, then I will get this error:

Cannot create file ‘…database_A.mdf’ because it already exists.

Is it possible just by code rename database_B.mdf to database_A.mdf without setting path to location of databases? I cannot use Management Studio to deattach database.

Answer

if you want to change file name of your database you will read this content

Renaming Physical Database File Names

when your database attach in database service you cant change the name of database file.

becuase the database service used the data and log file.

you will be set data base singel user and set offline state for your database then change file name with

ALTER DATABASE XXX MODIFY FILE (Name='XXX', FILENAME='F:MSSQL12.MSSQLSERVERMSSQLDATAXXX_Renamed.mdf')

you will be set your data base state offline and then change your file name and set state online with new file name and object browser file name.