SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQLCMD を使用して DB 単位の件数をテキストファイルに取得する

leave a comment

PowerShell や SQLSMO を使用したスクリプトを作成すればよいと思うのですが古い環境などでもどうさせる必要があったので、SQLCMD で実行する方法を。

とはいってもバッチ用実行用のコマンドラインを生成しているだけですが。

データベース内のテーブルの件数を取得したい場合、以下のようなクエリで概算の件数を取得することができます。


SET NOCOUNT ON
GO
SELECT
	DB_NAME() AS DbName,
	SCHEMA_NAME(so.schema_id) AS SchemaName,
	so.name,
	dps.*
FROM
	sys.dm_db_partition_stats dps
	INNER JOIN
	sys.objects so
	ON
	so.object_id = dps.object_id
	AND
	SCHEMA_NAME(so.schema_id)  'sys'
WHERE index_id IN(0, 1)
ORDER BY so.name

 

アドホックなクエリを実行するようにしてカーソル等で DB 単位で実行してもよいのですが、少し可読性が落ちるので、sqlcmd を使用してタブ区切りテキストを出力したいと思います。

bcp の queryout を使用すれば特定のクエリの結果を BCP で出力できますが外部ファイルの SQL を入力ファイルとして指定することができないようなのですよね。

上記のクエリを [c:sqltable_count.sql] として保存します。

あとはこのクエリを DB 単位で実行する SQLCMD のコマンドラインを生成します。

SET NOCOUNT ON
GO

DECLARE @dbname sysname
DECLARE @outfilename sysname
DECLARE @sqlfilename sysname

SET @outfilename = 'C:sqltable_count_'
SET @sqlfilename = 'C:sqltable_count.sql'

DECLARE DBCURSOR CURSOR FOR
SELECT name FROM sys.databases WHERE database_id >= 5

OPEN DBCURSOR
FETCH NEXT FROM DBCURSOR
INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'sqlcmd -S . -E -s "	" -W -d ' + @dbname + ' -i ' + @sqlfilename + ' -o ' + @outfilename + @dbname + '.txt'
	FETCH NEXT FROM DBCURSOR
	INTO @dbname
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR

 

クエリを実行するとメッセージに以下のようなコマンドが出力されます。

image

これをコピーしてバッチファイルに貼り付けます。

# タブ区切りにしているのでそのままコマンドプロンプトに貼り付けると、タブ文字がタブ保管の内容となってしまいます。

image

あとはこのバッチを実行すると件数を出力したタブ区切りのテキストが出力されます。

image

いい加減ツール化したいなと思いながら未だにアナログな方法に頼っています…。

Written by masayuki.ozawa

5月 14th, 2014 at 10:47 pm

Posted in SQL Server

Tagged with

Leave a Reply

*