SQL利用CMD以及IIS命令APPCMD 实现一些IIS操作

--列出本机的IIS的网站以及对应的应用程序池的所有相关信息
--EXEC P_SELECT_IIS_SITE_APPPOOLSALLMESSAGE
CREATE PROC P_SELECT_IIS_SITE_APPPOOLSALLMESSAGE
AS
SET NOCOUNT ON
BEGIN TRY 
	--开启CMDSHELL
	EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1
	RECONFIGURE
	EXEC SP_CONFIGURE 'XP_CMDSHELL', 1
	RECONFIGURE

	IF OBJECT_ID('TEMPDB.DBO.#APPPOOLLIST') IS NOT NULL
	DROP TABLE #APPPOOLLIST
	CREATE TABLE #APPPOOLLIST(COL NVARCHAR(MAX))
	INSERT INTO #APPPOOLLIST EXEC MASTER.DBO.XP_CMDSHELL 'DIR %SYSTEMROOT%\SYSTEM32\INETSRV\APPCMD.EXE'--判断是否有IIS的APPCMD文件--可以用master..xp_fileexist
	IF EXISTS(SELECT 1 FROM #APPPOOLLIST WHERE COL IS NOT NULL AND COL LIKE '%APPCMD.EXE%')
	BEGIN
		--EXEC MASTER.DBO.XP_CMDSHELL '%SYSTEMROOT%\SYSTEM32\INETSRV\APPCMD LIST SITES /text:name'--读取所有网站
		--/text:[path=''/''].applicationpool 不能两个同时
		--TRUNCATE TABLE #SITELIST
		IF OBJECT_ID('TEMPDB.DBO.#SITE_apppool_LIST') IS NOT NULL--最终结果网站名---应用程序池名
		DROP TABLE #SITE_apppool_LIST
		CREATE TABLE #SITE_apppool_LIST(NAME NVARCHAR(1024),APPPOOLNAME NVARCHAR(256) DEFAULT '')
		
		IF OBJECT_ID('TEMPDB.DBO.#apppool_LIST') IS NOT NULL--应用程序池
		DROP TABLE #apppool_LIST
		CREATE TABLE #apppool_LIST(APPPOOLNAME NVARCHAR(256) DEFAULT '')
		
		
		declare @NAME NVARCHAR(1024),@sql nvarchar(4000)
		
		IF OBJECT_ID('TEMPDB.DBO.#SITELIST') IS NOT NULL--网站
		DROP TABLE #SITELIST
		CREATE TABLE #SITELIST(NAME NVARCHAR(1024))		
		insert into  #SITELIST(NAME) EXEC MASTER.DBO.XP_CMDSHELL '%SYSTEMROOT%\SYSTEM32\INETSRV\APPCMD LIST SITES /text:name'--读取所有网站
		IF @@ROWCOUNT>0
		BEGIN
				--SELECT * FROM #SITELIST
				declare cur_site cursor fast_forward
				for
				SELECT * FROM #SITELIST  WHERE NAME IS NOT NULL
				open cur_site
				fetch next from cur_site into @NAME
				while @@FETCH_STATUS=0
				begin
						set @sql='%SYSTEMROOT%\SYSTEM32\INETSRV\APPCMD LIST SITE /name:"'+@name+'" /text:[path=''/''].applicationpool'
						--SELECT @SQL;
						INSERT INTO #apppool_LIST EXEC MASTER.DBO.XP_CMDSHELL @sql--读取网站的应用程序池
						INSERT INTO #SITE_apppool_LIST SELECT @NAME,APPPOOLNAME FROM #apppool_LIST WHERE APPPOOLNAME IS NOT NULL--将网站与应用程序池存在临时表
						TRUNCATE TABLE #apppool_LIST
					fetch next from cur_site into @NAME
				end
				close cur_site
				deallocate cur_site
		END
		
		--读取应用程序池的相关信息
		TRUNCATE TABLE #APPPOOLLIST
		INSERT INTO #APPPOOLLIST EXEC MASTER.DBO.XP_CMDSHELL '%SYSTEMROOT%\SYSTEM32\INETSRV\APPCMD LIST APPPOOL'

		SELECT
				--APPPOOLHASH,
				APPPOOLNAME,
				REPLACE(PARSENAME(VMS,3),'|','.') AS MGDVERSION,
				REPLACE(PARSENAME(VMS,2),'|','.') AS MGDMODE,
				REPLACE(PARSENAME(VMS,1),'|','.') AS STATE INTO #POOLS
		FROM
		(
			SELECT 
					SUBSTRING(COL,CHARINDEX('"',COL)+1,CHARINDEX('"',COL,CHARINDEX('"',COL)+1)-(CHARINDEX('"',COL)+1)) AS APPPOOLNAME,
					REPLACE(REPLACE(SUBSTRING(COL,CHARINDEX('(',COL)+1,LEN(COL)-(CHARINDEX('(',COL)+1 )+1-1 ),'.','|'),',','.') AS VMS
					--RIGHT(LOWER(SYS.FN_VARBINTOHEXSTR(HASHBYTES('MD5',COL))),256) AS APPPOOLHASH
			FROM 
					#APPPOOLLIST WHERE COL IS NOT NULL
		)T
	
		SELECT 
				S.*,
				ISNULL(P.MGDVERSION,'')MGDVERSION,
				ISNULL(P.MGDMODE,'')MGDMODE,
				isnull(P.STATE,'')STATE
		FROM 
				#SITE_apppool_LIST S 
				LEFT JOIN #POOLS P ON S.APPPOOLNAME=P.APPPOOLNAME--返回最终结果
	END
	ELSE 
	BEGIN
		SELECT '失败'
		--return
	END
END TRY
BEGIN CATCH
		SELECT ERROR_MESSAGE()+' '+ERROR_PROCEDURE();
END CATCH
--关闭CMDSHELL
EXEC SP_CONFIGURE 'XP_CMDSHELL', 0
RECONFIGURE
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 0
RECONFIGURE

--回收IIS指定的应用程序池
--EXEC P_RECYCLE_APPPOOLSBYNAME 'wzzhmb.api.hys.com',0
CREATE PROC P_RECYCLE_APPPOOLSBYNAME(@POOLNAME NVARCHAR(256)=NULL,@ISEXEC BIT=0)
AS
SET NOCOUNT ON
BEGIN TRY 
		EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1
		RECONFIGURE
		EXEC SP_CONFIGURE 'XP_CMDSHELL', 1
		RECONFIGURE
		DECLARE @SQL NVARCHAR(4000)
		IF ISNULL(@POOLNAME,'')=''
		BEGIN
			DECLARE @APPPOOLNAME NVARCHAR(1024)
			DECLARE CUR_APP CURSOR FAST_FORWARD
			FOR
			SELECT APPPOOLNAME FROM APPPOOLS WHERE UPDATEDISRECYCLE=1 --AND STATE='STATE:STOPPED'

			OPEN CUR_APP
			FETCH NEXT FROM CUR_APP INTO @APPPOOLNAME
			WHILE @@FETCH_STATUS=0
			BEGIN
					SET @SQL='%SYSTEMROOT%\SYSTEM32\INETSRV\APPCMD RECYCLE APPPOOL /APPPOOL.NAME:"'+@APPPOOLNAME+'"';
					IF(ISNULL(@ISEXEC,0)=0)
					SELECT @SQL;
					ELSE
					BEGIN
							EXEC MASTER.DBO.XP_CMDSHELL @SQL
							IF @@ERROR=0
							SELECT '应用程序池:'+@APPPOOLNAME+':已回收';
					END
					FETCH NEXT FROM CUR_APP INTO @APPPOOLNAME
			END
			CLOSE CUR_APP
			DEALLOCATE CUR_APP;
		END
		ELSE
		BEGIN
					SET @SQL='%SYSTEMROOT%\SYSTEM32\INETSRV\APPCMD RECYCLE APPPOOL /APPPOOL.NAME:"'+@POOLNAME+'"';--最好是加上检查这个名字的
					IF(ISNULL(@ISEXEC,0)=0)
					SELECT @SQL;
					ELSE
					BEGIN
							EXEC MASTER.DBO.XP_CMDSHELL @SQL
							IF @@ERROR=0
							SELECT '应用程序池:'+@APPPOOLNAME+':已回收';
					END				
		END
		
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()+' '+ERROR_PROCEDURE();
END CATCH

--关闭CMDSHELL
EXEC SP_CONFIGURE 'XP_CMDSHELL', 0
RECONFIGURE
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 0
RECONFIGURE