PowerShellを使ってSQL Serverの設定値を取得する２

今回はPowerShellを利用して、SQL Serverの設定値を一括で取得する方法のクエリ部分をご紹介します。

PowerShell側の中身を確認したい場合は下記記事をご確認ください。

blog.jbs.co.jp

パラメータシートなどを作成する際に便利だと思います。

今回紹介するクエリ
- parameter_check.sql
- username_check.sql
さいごに

今回紹介するクエリ

前回の記事で解説した通り、今回作成したPowerShellスクリプトは、直接実行したいクエリを記載せずに、ファイルを読み込んでクエリを実行する方法をとっています。

スクリプトの中では、以下の2つのクエリを実行しています。

parameter_check.sql
username_check.sql

本記事では、この二つのクエリの詳細を紹介します。

parameter_check.sql

パラーメーターをチェックするためのクエリです。

 
DECLARE @CONFIG int
DECLARE	@DB_Name nvarchar(100)
DECLARE @SQL nvarchar(4000)
DECLARE @SERVER   VARCHAR(MAX) = 'localhost';
DECLARE @USER_ID  VARCHAR(MAX) = 'xxxx'; --ユーザ名
DECLARE @PASSWORD VARCHAR(MAX) = 'xxxx';　--パスワード
  IF 0 = (select value from sys.configurations where name = 'xp_cmdshell')
	BEGIN
		IF 0 = (select value from sys.configurations where name = 'show advanced options')
			BEGIN
				EXEC sp_configure 'show advanced options',1;
				RECONFIGURE;
				EXEC sp_configure 'xp_cmdshell',1
				RECONFIGURE;
				SET @CONFIG = 1
			END
		ELSE
			BEGIN
				EXEC sp_configure 'xp_cmdshell',1
				RECONFIGURE;
				SET @CONFIG = 2
			END
	END


DECLARE DB_Cursor CURSOR FOR
SELECT Name FROM master.dbo.sysdatabases
OPEN	DB_Cursor
 
FETCH NEXT FROM	DB_Cursor INTO @DB_Name;
 
WHILE @@FETCH_STATUS  =  0
BEGIN
	SET @sql = ''
	SET @sql = @sql + ' USE ' + @DB_Name + ';'
	SET @sql = @sql + ' SELECT * FROM sys.filegroups RIGHT OUTER JOIN sys.database_files ON sys.filegroups.data_space_id = sys.database_files.data_space_id ' + ';'
	SET @sql = @sql + ' SELECT * FROM sys.database_scoped_configurations ' + ';'
	SET @sql = @sql + ' SELECT * FROM sys.change_tracking_databases ' + ';'
	SET @sql = @sql + ' SELECT * FROM sys.configurations ' + ';'
	SET @sql = @sql + ' SELECT OBJECT_NAME(major_id) AS object,USER_NAME(grantee_principal_id) AS user_name,permission_name as permission,state_desc as state_desc FROM sys.database_permissions ' + ';'
	SET @sql = @sql + ' EXEC sp_db_vardecimal_storage_format ' + @DB_Name + ';'
 
    DECLARE @CMD VARCHAR(8000) = '';
    SELECT
		@CMD += 'SQLCMD',
		@CMD += ' -S{SERVER}',
		@CMD += ' -d{DATABASE}',
		@CMD += ' -U{USER_ID}',
		@CMD += ' -P{PASSWORD}',
		@CMD += ' -q "{query}"',
		@CMD += ' -s, -W',
		@CMD += ' -o {FILE}',
		@CMD = REPLACE(@CMD,'{SERVER}'  ,@SERVER),
		@CMD = REPLACE(@CMD,'{DATABASE}',@DB_Name),
		@CMD = REPLACE(@CMD,'{USER_ID}' ,@USER_ID),
		@CMD = REPLACE(@CMD,'{PASSWORD}',@PASSWORD),
		@CMD = REPLACE(@CMD,'{query}'   ,@SQL),
		@CMD = REPLACE(@CMD,'{FILE}'    ,'C:\Users\admin02\Desktop\temp\' + @DB_Name + '.csv')
	;
 
	EXEC master..xp_cmdshell @CMD;

	FETCH NEXT FROM DB_Cursor
	INTO @DB_Name;
 
END 

CLOSE		DB_Cursor
DEALLOCATE	DB_Cursor

IF @CONFIG = 1
	BEGIN
		EXEC sp_configure 'show advanced options',0;
		RECONFIGURE;
		EXEC sp_configure 'xp_cmdshell',0;
		RECONFIGURE;
	END
ELSE IF @CONFIG = 2
	BEGIN
		EXEC sp_configure 'xp_cmdshell',0;
		RECONFIGURE;
	END

username_check.sql

ユーザー名をチェックするためのクエリです。

 SELECT name AS UserName,
    CASE type_desc
        WHEN 'WINDOWS_LOGIN' THEN 'Windows Authentication'
        WHEN 'SQL_LOGIN' THEN 'SQL Authentication'
        ELSE 'Other'
    END AS AuthenticationType
FROM sys.server_principals
WHERE type_desc IN ('WINDOWS_LOGIN', 'SQL_LOGIN')