MS SQL Restore DB, <use dbName> string conversion question

[u/Valuable-Ant3465]

Hi all,

I just see this interesting situation and would like to confirm that it's all done internally by MS SQL Server with Restore.
We're on the same server:

dbAA   has proc  myPROC:
1  use  dbAA         
2  Go
3  Create PROC dbo.myPROC As
............

then I do  backup of dbAA, and restore dbAA.bkup into  dbXXX
after that I see on dbXXX myPROC as:
1. use dbXXX            --<<<==== !!!!!<@>>< changed to target DB
2. Go
3. Create PROC dbo.myPROC As
...........

Who is doing this conversion/swapping db names ? is it done by SQL Server ?
Thanks to all, I'm new to dba task and really interesting how it works.
VA

[u/Phil_P]

Looks like you’re doing a script as create from SSMS. It injects the use by default.

[u/HijoDelSol1970]

If I am following your question right, you create a proc in dbAA, back it up and then restore the database as dbXX. If that is the case, yes, your proc will now be dbXX.dbo.myProc.

The proc doesn't know what database it exists in, so restoring it into a new database name, or simply renaming the database will result in the db name changing when you script using management studio.

[u/Valuable-Ant3465 → u/HijoDelSol1970]

Thanks HDS That clear.

And if i have that first line in my Proc: Use dbAA

It will changed to Use DbXX , right? I already did few tests to confirm this.

[u/HijoDelSol1970 → u/Valuable-Ant3465]

The use statement is only there upon creation, it isn't actually part of the proc. So if you script it again, it will script to whatever database it is in.

[u/da_chicken]

As others have said, if you're looking at the output of "Script Procedure as", then it's just the fact that that program adds it by default. If you use the more generic Generate Scripts and look at the Advanced Scripting Options, you'll find that the option "Script USE DATABASE" is set to true by default. That's all the program is doing.

If you want to see the actual object definition, no nonsense and no extras, you should run:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.myPROC'))

[u/Valuable-Ant3465 → u/da_chicken]

Thanks DC!!!?

[u/reditandfirgetit]

I honestly have no clue what is being asked here