使用SP_EXECUTESQL for循环遍历所有数据库的简单方法
使用SP_EXECUTESQL for循环遍历所有数据库的简单方法
提问于 2019-03-18 14:14:29
在所有数据库上运行查询都有很大问题。我有很多可以工作的脚本，但是我想更好地理解如何使用SP_EXECUTESQL而不是SP_MSFOREACHDB。
通常，当我赶时间的时候，我会这样做：
然后我复制结果，然后执行它。效果很好。
我想知道如何在SP_EXECUTESQL循环语句中转换它。
我有一个使用游标的方法，但是有没有一种不用游标使用SP_EXECUTESQL的方法？
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我甚至可以创建一个简单的查询来运行这个简单的选择，但是我希望在同一个查询窗口中提供所有内容：
回答 2
Database Administration用户
回答已采纳
发布于 2019-03-18 15:44:07
用sp_ineachdb从这里中创建master。使用这个#temp表：
CREATE TABLE #temp
(
Dbase varchar(100),
Feature_Name varchar(100),
feature_id int
);守则很简单：
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;另一种选择是：
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;没有一种方法可以在不使用某种循环的情况下跨多个数据库执行命令。您可以在所有当前数据库中创建一个视图：
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
...但是这有很多问题。您必须维护此视图，以说明添加、删除或重命名的数据库。您必须说明任何单个数据库都可以处于除联机之外的任何状态的情况。和许可。您可以构建代码以在运行时生成视图(或CTE)，但这仍然需要游标或循环。
因此，如果您的目标是简化您的运行时代码，没有问题。如果您的目标是消除游标或循环在过程中的任何一步，不太可能。
Database Administration用户
发布于 2019-03-18 14:25:40
试着这样做：
IF Object_id('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp
(
Dbase VARCHAR(100),
Feature_Name VARCHAR(100),
feature_id INT
);
DECLARE @DB SYSNAME,@cmd NVARCHAR(4000)
SET NOCOUNT ON
SET @cmd = ''
SELECT TOP 1 @DB = D.name
FROM master..sysdatabases AS D
WHERE dbid > 4
AND DATABASEPROPERTYEX(D.name, 'status') = 'ONLINE'
ORDER BY D.name;
WHILE @@ROWCOUNT = 1
BEGIN
SET @cmd = 'USE ' + @DB + ';
INSERT INTO #Temp
SELECT ''' + @DB + ''' ,* from sys.dm_db_persisted_sku_features
';
EXEC SP_EXECUTESQL @cmd
SELECT TOP 1 @DB = NAME
FROM master..sysdatabases AS D
WHERE dbid > 4
AND DATABASEPROPERTYEX(D.name, 'status') = 'ONLINE'
AND D.NAME > @DB
ORDER BY D.name
END
SELECT * FROM #temp另外，如果您正在使用sys.databases进行迭代，您的游标类型应该是静态的，否则它可能跳过数据库。
这也是我不喜欢在这种情况下使用游标的原因之一。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/232434
复制相关文章
相似问题
腾讯云开发者
