Simple way to use SP_EXECUTESQL for loop through all databases I have a BIG problem running queries on all databases. I have a lot of scripts that works, but I would like to understand better how to use SP_EXECUTESQL instead of SP_MSFOREACHDB.

Normally when I'm in a hurry I do this:



Then I just copy the results, and I execute it. It works great. 

I would like to know how to transform this in a SP_EXECUTESQL loop statement.

I have this that uses a cursor, but is there a way to use SP_EXECUTESQL without a cursor?

create table #temp (
    Dbase varchar(100),
    Feature_Name varchar(100),
    feature_id int
                    )
go

DECLARE @DB_NAME NVARCHAR(100)
DECLARE @CMD NVARCHAR(MAX)
DECLARE CR_FEATURE CURSOR FOR
SELECT NAME 
    FROM SYS.sysdatabases

OPEN CR_FEATURE

FETCH NEXT FROM CR_FEATURE INTO @DB_NAME

WHILE @@FETCH_STATUS = 0 
BEGIN
    SELECT @CMD='insert into #temp  select '''+@DB_NAME+''' ,* from sys.dm_db_persisted_sku_features'
    EXEC SP_EXECUTESQL @cmd

FETCH NEXT FROM CR_FEATURE INTO @DB_NAME
END

CLOSE CR_FEATURE
DEALLOCATE CR_FEATURE

DROP TABLE #temp


I could even create a simple query to run that simple select, but I would like everything in the same query window:
With sp_ineachdb from here created in master. With this #temp table:

CREATE TABLE #temp 
(
    Dbase varchar(100),
    Feature_Name varchar(100),
    feature_id int
);


The code is simply:

DECLARE @cmd nvarchar(max) = N'INSERT #temp 
  SELECT db_name(), feature_name, feature_id
    FROM sys.dm_db_persisted_sku_features;';

EXEC master.dbo.sp_ineachdb @command = @cmd, @user_only = 1;


Alternatively:

DECLARE @cmd nvarchar(max) = N'SELECT db_name(), feature_name, feature_id
    FROM sys.dm_db_persisted_sku_features;';

INSERT #temp EXEC master.dbo.sp_ineachdb @command = @cmd, @user_only = 1;


There isn't really a way to execute a command across multiple databases without using some kind of loop. You could create a view, perhaps, across all the current databases:

USE master;
GO

CREATE VIEW dbo.AllPersistedSkuFeatures
AS
  SELECT 'db1', feature_name, feature_id FROM db1.sys.dm_db_persisted_sku_features
  UNION ALL
  SELECT 'db2', feature_name, feature_id FROM db1.sys.dm_db_persisted_sku_features
  UNION ALL
  ...


But there are multiple problems with this. You have to maintain this view to account for databases that get added, dropped, or renamed. You have to account for cases where any single database can be in any state other than online. And permissions. You could build code to generate the view (or a CTE) at runtime, but that will still require a cursor or a loop. 

So if your goal is to simplify your runtime code, no problem. If your goal is to eliminate a cursor or loop at any step in the process, not likely.