自分のテーブルデータを一括削除

とりあえず、単純なものを。メモっとかないと忘れてしまう・・・

use [DB]
go

declare cur_user_tables cursor for
select
  all_obj.name
from
  sys.all_objects as all_obj
  inner join sys.schemas s on
    s.name = 'dbo' and
    s.schema_id = all_obj.schema_id
where
  all_obj.type = 'U' and
  all_obj.is_ms_shipped = 0
;

declare @table_name varchar(max)
declare @sql varchar(max)

open cur_user_tables;

fetch next from cur_user_tables
  into @table_name;

while @@FETCH_STATUS = 0
begin
  set @sql = 'delete from ' + @table_name;
  print @sql
  execute(@sql)

  fetch next from cur_user_tables
    into @table_name;
end

close cur_user_tables;
deallocate cur_user_tables;

go