Steps to Rename Physical Database Files in SQL Server

The users can manually rename the physical files according to choice using the Transact-SQL commands in the following manner:

Firstly, we will create a database “new_db” using SQL query. Naturally, the physical files will be named as “new_db.mdf”. Our main aim is to rename SQL Server physical files from “new_db.mdf” to “old_db.mdf”. Follow the below mentioned steps to manually rename the files:

1. Firstly, run the following command to create a new database with name ”new_db”.

CREATE DATABASE [new_db]
CONTAINMENT = NONE
ON PRIMARY
( NAME = 'name', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\new_db.mdf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )

Thus, a new database is created with physical file “new_db”. The following steps can be used to rename SQL Server physical files name.

2. Now, locate the physical location of file on your system using the following command:

USE new_db
GO
SELECT file_id, name as [logical_file_name], physical_name
FROM sys.database_files


3. Also, the database should be brought to OFFLINE state because the files cannot be renamed in the online mode. Run the following command to bring database to offline state:

USE [master];
GO
--Disconnect all existing session.
ALTER DATABASE new_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--Change database in to OFFLINE mode.
ALTER DATABASE new_db SET OFFLINE


4. Since the database is offline now, so browse to the location of the file as found from Step(1). Rename the file to desired name e.g., old_db.mdf.
5. Now, update the system catalog for updating the new name of the physical file.

ALTER DATABASE new_db MODIFY FILE (Name='new_db', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\old_db.mdf') GO

This updates the physical file name in system catalog file.


6. Run the following command to bring the database in ONLINE mode:

ALTER DATABASE new_db SET ONLINE
Go
ALTER DATABASE new_db SET MULTI_USER
Go



So, the database physical file name has been renamed to “old_db.mdf” and the database is also in the Online mode. The users can now work normally on the database to perform any operation.

Conclusion

The user may feel it necessary to rename physical file name in SQL Server due to any reason. However, to avoid confusion between the physical files and database, the users are always advised to rename SQL Server physical database files using T-SQL. All the detailed steps have been mentioned in the above section.

Queries regarding Mysql Proceduers

To find out all the procedures of a database:-

SHOW PROCEDURE STATUS WHERE Db = 'thirstydb';


To set a new definer:-
mysql> UPDATE `mysql`.`proc` p SET definer = 'thirstydev@%' WHERE definer='thirstylive@localhost' AND db='devthirstydb';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE `mysql`.`proc` p SET definer = 'thirstydev@%' WHERE definer='thirstylive@%' AND db='devthirstydb';
Query OK, 85 rows affected (0.04 sec)
Rows matched: 85  Changed: 85  Warnings: 0

To select all the procdure of a particular db:-
select name FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'PROCEDURE';

select name FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'FUNCTION';


To delete all the procdure of a particular db:-
DELETE FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'PROCEDURE';

DELETE FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'FUNCTION';




Mysql procedures:-

To find out all the procedures:-

mysql> show procedure status;
+--------------+---------------------------------------+-----------+---------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db           | Name                                  | Type      | Definer       | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+--------------+---------------------------------------+-----------+---------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| thirstydb    | advanced_vendor_filtering             | PROCEDURE | thirstylive@% | 2016-08-12 05:13:27 | 2016-08-12 05:13:27 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | advanced_vendor_filtering_my_bookings | PROCEDURE | thirstylive@% | 2016-08-12 05:22:29 | 2016-08-12 05:22:29 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | advanced_vendor_filtering_temp        | PROCEDURE | thirstylive@% | 2016-08-08 08:29:11 | 2016-08-08 08:29:11 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | all_food_categories                   | PROCEDURE | thirstylive@% | 2016-08-08 08:29:20 | 2016-08-08 08:29:20 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | apikeyValidity                        | PROCEDURE | thirstylive@% | 2016-08-08 08:29:29 | 2016-08-08 08:29:29 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | apikeyValidity_vendor                 | PROCEDURE | thirstylive@% | 2016-08-08 08:29:41 | 2016-08-08 08:29:41 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| thirstydb    | directory                             | PROCEDURE | thirstylive@% | 2016-08-19 03:00:51 | 2016-08-19 03:00:51 | DEFINER       |         | utf8


To find out a procedure by a certain definer name and dbname:-
mysq> use mysql
mysql> select name from  proc where definer = 'thirstydev@%' AND db='devthirstydb';

+---------------------------------------+
| name                                  |
+---------------------------------------+
| advanced_vendor_filtering             |
| advanced_vendor_filtering_my_bookings |
| advanced_vendor_filtering_temp        |
| all_food_categories                   |
| apikeyValidity                        |
| apikeyValidity_vendor                 |
| base_url                              |
| directory                             |
| facebook_login  


To call a procedure:-
mysql> call search_result(1);
Empty set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)


To view a procedure:-

mysql> show create procedure user_login;
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure  | sql_mode | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | character_set_client | collation_connection | Database Collation |
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| user_login |          | CREATE DEFINER=`thirstylive`@`%` PROCEDURE `user_login`(email_in VARCHAR(255), password_in VARCHAR(255),
api_key_in VARCHAR(50), base_url_in TEXT)
BEGIN

        DECLARE email_verified_declared INT(2);
        DECLARE user_id_declared BIGINT;

        SELECT email_verified INTO email_verified_declared FROM user_profile WHERE email = email_in AND `password` = password_in;
        SELECT user_id INTO user_id_declared FROM user_profile WHERE email = email_in AND `password` = password_in;

        IF email_verified_declared = 0 THEN

                SELECT 'unverified_email' AS  col_name;

        ELSEIF user_id_declared IS NULL THEN

                SELECT 'invalid_credentials' AS col_name;

        ELSE

                UPDATE user_profile SET api_key = api_key_in WHERE user_id = user_id_declared;
                CALL user_profile(user_id_declared, base_url_in);

        END IF;


END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+-------------------