A use statement is not allowed in a procedure, function, or trigger

Question

I have the following code that I compiled that seems to work fine. However, I need to put it in a stored procedure, but I get the following error:

use statement not allowed in procedure, function, or trigger

Does anyone know about work?

---

CREATE TABLE #TableRowCounts1
(
      [TableName] VARCHAR(128), 
      [RowCount] INT
) 

CREATE TABLE #TableRowCounts2
(
      [TableName] VARCHAR(128), 
      [RowCount] INT
) 

use Database1
GO

INSERT INTO #TableRowCounts1
(
      [TableName], 
      [RowCount]
      )
EXEC sp_MSforeachtable 
      'SELECT ''?'' 
      [TableName], 
      COUNT(*) [RowCount] 
      FROM ?'

use Database2
GO

INSERT INTO #TableRowCounts2
(
      [TableName], 
      [RowCount]
      )
EXEC sp_MSforeachtable 
      'SELECT ''?'' 
      [TableName], 
      COUNT(*) [RowCount] 
      FROM ?'

---

Answer 1

You have to change it so that it works. You need to provide the full name using the database (and schema) in front of the stored processes that you want to run in different databases.

CREATE PROCEDURE SomeProc

AS

CREATE TABLE #TableRowCounts1
(
      [TableName] VARCHAR(128), 
      [RowCount] INT
) 

CREATE TABLE #TableRowCounts2
(
      [TableName] VARCHAR(128), 
      [RowCount] INT
) 

INSERT INTO #TableRowCounts1
(
      [TableName], 
      [RowCount]
      )
EXEC Database1.[dbo].sp_MSforeachtable 
      'SELECT ''?'' 
      [TableName], 
      COUNT(*) [RowCount] 
      FROM ?'

INSERT INTO #TableRowCounts2
(
      [TableName], 
      [RowCount]
      )
EXEC Database2.[dbo].sp_MSforeachtable 
      'SELECT ''?'' 
      [TableName], 
      COUNT(*) [RowCount] 
      FROM ?'

Answer 2

Instead of using the USE operator, you can simply use the full name ie

DATABASENAME.SCHEMANAME.TABLENAME.ColumnName