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

1. 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.
2. 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.
3. 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.

USE Test
GO

CREATE PROCEDURE dbo.GetTableRecordCountForSQLInstance
AS
BEGIN
    --Drop Temp table if exists to save record count for all the tables on SQL Server Instance
    IF OBJECT_ID('tempdb..##RecordCount') IS NOT NULL
        DROP TABLE ##RecordCount

    CREATE TABLE ##RecordCount (
        DatabaseName VARCHAR(500)
        ,TableName VARCHAR(500)
        ,RecordCount INT
        )

    --Use Cursor to Loop through Databases
    DECLARE @DatabaseName AS VARCHAR(500)

    DECLARE CDCCursor CURSOR
    FOR
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

    OPEN CDCCursor

    FETCH NEXT
    FROM CDCCursor
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To change the Database Name 
        EXEC (
                N'USE ' + @DBName + N'; EXEC(''
Insert into ##RecordCount
Select DB_Name() AS DatabaseName,t.name,s.rows from sysindexes s
inner join sys.tables t
on s.id=t.object_id
and t.is_ms_shipped=0
'');'
                );

        FETCH NEXT
        FROM CDCCursor
        INTO @DatabaseName
    END

    CLOSE CDCCursor

    DEALLOCATE CDCCursor

    --Return DatabaseName,TableName and Record Count from Temp Table
    SELECT *
    FROM ##RecordCount
END

You can create this stored procedure in any of the Database you want. Once you run the Stored Procedure, It is going to get the user databases list and then use Cursor to loop through. Inside the Cursor we are using Dynamic SQL to change the scope of Query to Database by using USE statement.

SQL Server Video Tutorial: Detailed Video how to Change DB Name inside Stored Procedure