다음 스크립트를 실행하면 정상적으로 실행됩니다.
declare @temp table ( name varchar(255), field varchar(255), filename varchar(255), filegroup varchar(255), size varchar(255), maxsize varchar(255), growth varchar(255), usage varchar(255) ); INSERT @temp exec sp_msforeachdb @command1="use ?; Exec sp_helpfile;" 하지만 내 sp_foreachdb 프로 시저를 사용합니다. 소스 코드는 아래 링크에 있습니다.
declare @temp table ( name varchar(255), field varchar(255), filename varchar(255), filegroup varchar(255), size varchar(255), maxsize varchar(255), growth varchar(255), usage varchar(255) ); INSERT @temp exec sp_foreachdb @command="use ?; Exec sp_helpfile;" 예외가 발생합니다 (해당 절차에 예외 처리를 추가했습니다)
--EXCEPTION WAS CAUGHT-- THE ERROR NUMBER:8164 SEVERITY: 16 STATE: 1 PROCEDURE: sp_foreachdb LINE NUMBER: 165 ERROR MESSAGE: An INSERT EXEC statement cannot be nested. ------------------------------------ the sql ------------------------------------ SELECT name FROM sys.databases WHERE 1=1 AND state_desc = N"ONLINE" AND is_read_only = 0 Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 239 A cursor with the name "c" does not exist. Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 240 A cursor with the name "c" does not exist. (0 row(s) affected) 댓글
- '이 복잡한 코드를 사용하는 이유를 ' 잘 모르겠습니다. 방법. sys.master_files를 쿼리하지 않는 이유는 무엇입니까?
- @Nic은 잘 지적했지만 sp_helpfile을 예제로 사용했습니다. 실생활에서는 내가 호출 할 자체 저장 프로 시저 중 하나입니다.
답변
소스 코드 for Aaron “의 sp_foreachdb에는 다음 줄이 포함됩니다.
INSERT #x EXEC sp_executesql @sql;
오류 메시지 :
INSERT EXEC 문은 중첩 될 수 없습니다.
따라서 아래와 같은 코드는 INSERT xxx EXEC xxx 코드를 중첩하므로 유효하지 않습니다.
INSERT @temp exec sp_msforeachdb @command1="use ?; Exec sp_helpfile;" 댓글
- @marcellomiorelli 동적 SQL을 제거하려면 Aaron '의 SP를 크게 변경해야한다고 생각합니다. 이렇게하려면 다음을 사용해야합니다. 추가 기능.
답변
분산 쿼리 방법을 통해 자신의 서버에 연결하면 sp_foreachdb의 경우 트릭을 수행 할 수 있습니다. 단일 결과 집합을 반환합니다.
create table #temp ( name varchar(255), field varchar(255), filename varchar(255), filegroup varchar(255), size varchar(255), maxsize varchar(255), growth varchar(255), usage varchar(255) ); insert into #temp select * FROM OPENROWSET("SQLNCLI", "SERVER=****;UID=****;PWD=****", " exec sp_foreachdb @command="" Exec ?..sp_helpfile;"" WITH RESULT SETS ((name varchar(255), field varchar(255), filename varchar(255), filegroup varchar(255), size varchar(255), maxsize varchar(255), growth varchar(255), usage varchar(255))); ") select * from #temp 쿼리 w 위 ill은 sp_foreachdb 실행의 첫 번째 결과 집합 만 반환합니다. 그러나 아래 쿼리는 단일 결과 집합의 모든 데이터베이스에 대한 결과를 반환합니다.
create table #temp ( name varchar(255), field varchar(255), filename varchar(255), filegroup varchar(255), size varchar(255), maxsize varchar(255), growth varchar(255), usage varchar(255) ); exec sp_foreachdb @command="INSERT INTO #temp Exec ?..sp_helpfile;" select * from #temp 답변
제거하여 Aaron의 SP를 적용 해 볼 수 있습니다. 동적 부분은 제공된 인수를 기반으로 sys.databases에서 데이터베이스 이름 만 읽는 쿼리를 작성해야합니다. 동적 SQL은 쿼리를 가장 효율적으로 만들기 위해 선택됩니다. 특정 요구 사항을 고려할 때 약간의 희생이 필요할 수 있습니다.
하지만 아래에서 제공하는 재 작성으로 인해 성능이 크게 저하되지 않을 수 있습니다. div id = “cb2e0e1faf”>
시스템보기에는 일반적으로 행이 많지 않지만 어떤 경우에도 끝에 OPTION (RECOMPILE)를 추가 할 수 있습니다.하지만 느릴 수 있습니다. 하지만 약속 할 수있는 것은 다소 추악 할 가능성이 있습니다.
재 작성 방법은 다음과 같습니다. Aaron의 절차는 매개 변수 값이 확인되고 반복되는 패턴을 사용하여 쿼리를 작성하는 것입니다. , 결과에 따라 추가 쿼리가 동적 쿼리에 추가됩니다. 예 :
SET @sql = N"SELECT name FROM sys.databases WHERE 1=1" + CASE WHEN some_condition1 THEN "AND some_filter1" ELSE "" END + CASE WHEN some_condition2 THEN "AND some_filter2" ELSE "" END + ... 다음은이를 다시 작성하는 방법입니다.
SELECT name FROM sys.databases WHERE 1=1 AND (some_filter1 OR opposite_of_some_condition1) AND (some_filter2 OR opposite_of_some_condition2) AND ... OPTION (RECOMPILE); 예를 들어 @system_only는 다음과 같이 database_id IN (1,2,3,4) 필터를 포함할지 여부를 제어합니다.
SET @sql = N"SELECT name FROM sys.databases WHERE 1=1" + CASE WHEN @system_only = 1 THEN " AND database_id IN (1,2,3,4)" ELSE "" END 다시 작성된 쿼리는 다음과 같이 매개 변수화됩니다.
SELECT name FROM sys.databases WHERE 1=1 AND (database_id IN (1,2,3,4) OR @system_only <> 1) -- or: @system_only = 0 결과 쿼리를
SELECT CASE WHEN @suppress_quotename = 1 THEN db ELSE QUOTENAME(db) END FROM #x ORDER BY db 보시다시피 마지막 터치는 SELECT 부분으로 이동해야합니다. 여기서 간단한 가
SELECT CASE WHEN @suppress_quotename = 1 THEN name ELSE QUOTENAME(name) END 확실히 이제 테이블은 SP에서 더 이상 필요하지 않습니다.
마지막 메모는 각 데이터베이스에 대해 실행하려는 특정 스크립트에 관한 것입니다.
@command1="use ?; Exec sp_helpfile;" 대신
@command1="Exec ?..sp_helpfile;" 답변
원래 sp_foreachdb <에 비해 몇 가지 개선 사항이있는 교체를 작성했습니다. / a>, 중첩 기능 포함. 새롭고 선호되는 절차는 sp_ineachdb ( 파트 1 / 파트 2 ), Brent Ozar의 First Responder Kit 의 일부로도 제공됩니다.
몇 가지 배경 정보는 여기에 있습니다. :