Alternative and Effective method of Using sp_msforeachdb

/*Lot of you may not be aware that the command string parameter  does  not allow more than 2000 characters. So what  happens if the parameter you want to pass is greater than 2000.

Below is the script you can use while running across the database activities.Also unlike sp_msforeachdb  the script isn't undocumented.*/

/****** Object:  Stored Procedure [dbo].[run_query_across_databases]    Script Date: 02/20/2017 22:50:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[run_query_across_databases]

@sql_command VARCHAR(MAX)

AS

BEGIN

       SET NOCOUNT ON;

-- Stores database name for use in the cursor

DECLARE @database_name VARCHAR(300)        

DECLARE @sql_command_to_execute NVARCHAR(MAX) -- Will store the TSQL after the database name has been inserted

 -- Stores our final list of databases to iterate through, after filters have been applied

       DECLARE @database_names TABLE          (database_name VARCHAR(100))

       DECLARE @SQL VARCHAR(MAX)-- Will store TSQL used to determine databaselist

       SET @SQL =

       '      SELECT

                     SD.name AS database_name

              FROM sys.databases SD

       '

       -- Prepare database name list

       INSERT INTO @database_names  ( database_name )

       EXEC (@SQL)

       DECLARE db_cursor CURSOR FOR SELECT database_name FROM @database_names

       OPEN db_cursor

       FETCH NEXT FROM db_cursor INTO @database_name

       WHILE @@FETCH_STATUS = 0

       BEGIN

         -- Replace "?" with the database name

         SET @sql_command_to_execute = REPLACE(@sql_command, '?', @database_name)          

  

         EXEC sp_executesql @sql_command_to_execute

         FETCH NEXT FROM db_cursor INTO @database_name

       END

       CLOSE db_cursor;

       DEALLOCATE db_cursor;

END

---------------------------------------------------------------------------------

--Example below showing the list of each tables under each Database on the  current server.

DECLARE @sqlCommand NVARCHAR(MAX)

SET @sqlCommand='

USE [?]

SELECT ''?'',NAME FROM ?.SYS.OBJECTS WHERE TYPE=''U''

'

EXEC dbo.run_query_across_databases @sqlCommand

--------------------------------------------------------------------------------