SQLServerのSQLメモ

取得したテーブル一覧に対して処理

ポイント

• テーブル名の取得
• ループ
• 動的SQLの作成と実行

-- 対象DB設定
USE d_test;
GO

/*
=========================================
  条件値設定
=========================================
*/

-- 抽出対象更新日時
DECLARE @cond_datetime nvarchar(30) = '2021-09-20 00:00:00';

/*
=========================================
  テーブルと列名のMAP
========================================= 
*/
DECLARE @table_col_map TABLE(
    TABLE_NAME NVARCHAR(100)
    , COL_NAME NVARCHAR(100)
);

-- テーブルと調べる更新日時列名の対応関係を設定
INSERT INTO @table_col_map VALUES
  ('crews', 'update_at'),
  ('clients', 'update_at');

/*
=========================================
変数定義
=========================================
*/

DECLARE @table_name nvarchar(100);
DECLARE @sql nvarchar(2000);
DECLARE @updated_count int = 0;
DECLARE @updatetime_col_name nvarchar(100);
/*
=========================================
データ取得と結果表示
=========================================
*/

-- データ取得カーソル定義
DECLARE cur_table CURSOR FOR SELECT name FROM sys.tables 

-- ヘッダ表示
print 'テーブル, 更新件数' 
-- データ取得開始
OPEN cur_table;

FETCH NEXT FROM cur_table INTO @table_name 
WHILE @@FETCH_STATUS = 0 BEGIN 
  -- print '=== target_table:' + @table_name

  -- 更新日時の列名取得
  SET @updatetime_col_name = (SELECT COL_NAME FROM @table_col_map WHERE TABLE_NAME = @table_name);

  -- sql作成
  SET @sql = N'SELECT @updated_count = count(*) FROM #table WHERE #updatetime_col > ''#cond_datetime'' ';
  SET @sql = replace(@sql, '#table', @table_name);
  SET @sql = replace(@sql, '#updatetime_col', @updatetime_col_name);
  SET @sql = replace(@sql, '#cond_datetime',@cond_datetime);

  --print @sql
  -- 指定更新日より後の件数を取得
  EXECUTE sp_executesql 
    @sql,
    N'@updated_count int OUTPUT',
    @updated_count OUTPUT;

  -- 指定更新日時以降に更新があったテーブル名と件数を表示
  IF @updated_count > 0 
    print @table_name + ',' + CAST(@updated_count AS nvarchar(10)) 
  
  -- 次の行を取得
  FETCH NEXT FROM cur_table INTO @table_name

END 

-- カーソルの解放
CLOSE cur_table; 
DEALLOCATE cur_table;

テーブルと列の名前とコメント取得

use d_test;
go

-- テーブルの名前とコメント取得
SELECT
  t.name    as TABLE_NAME
  ,ep.value  as COMMENT
FROM
  sys.tables t 
  LEFT JOIN sys.extended_properties ep ON t.object_id = ep.major_id

-- 列名と列コメント取得

SELECT
  t.name    as TABLE_NAME
  ,c.name    as COLUMN_NAME
  ,ep.value  as COMMENT
FROM
  sys.tables t 
  LEFT JOIN sys.columns c ON t.object_id = c.object_id
  LEFT JOIN sys.extended_properties ep ON c.object_id = ep.major_id AND c.column_id = ep.minor_id


-- テーブルコメントと件数

use d_test;
go

SELECT
    t.name    as TABLE_NAME
  , ep.value  as COMMENT
  , ind.rows  as ROW_COUNT
  , ep.minor_id
FROM
  sys.tables t 
  LEFT JOIN sys.extended_properties ep ON t.object_id = ep.major_id
  LEFT JOIN sys.sysindexes ind ON t.object_id = ind.id AND ind.indid < 2
WHERE
  ep.minor_id = 0 OR ep.minor_id is null