How to Change Database Name inside Stored Procedure in SQL Server - SQL Server Tutorial

How to Change Database Name inside Stored Procedure in SQL Server - TSQL Tutorial/ SQL Server Tutorial
We often come across different scenarios where we need to switch the database name inside stored procedure. Let's take a look for few of scenarios below

We have never introduced Audit Columns to our tables on SQL Server Instance such as ModifiedBy, ModifiedOn. Now we want to add these two columns to all the tables on SQL Server Instance.
We want to get the record count for all the tables in SQL Server Instance. That includes any Database on our Current SQL Server Instance.
We have enabled CDC on multiple Databases on our SQL Server Instance, we want to get the list of tables from multiple Databases on which CDC is enabled.

In all above scenarios we have to switch Database to get the required results. In our below example we are creating a stored procedure that should return us all the user tables on SQL Server Instance from User databases with record count. To get the record count we have to run our query on each Database. We will be using Dynamic SQL to change the database scope for our query so we can get results from that Database.

You can use Dynamic SQL to change the Database Name and run query against that database as shown in below example.

Blog post link with script for this video

[hgbhysi]

how to change the table name after executed the procedure using parameter

[donaldoji]

you are amazing! That was very helpful.

[blackisblack]

So in short there is no way to change session's database inside a stored procedure.