DEFINITIONS

Definitions More Info.
Definition ID2.021
TitleSQL
CategoryNOTES
Definitionsirali temp tablo silme scripti guzel
Definition DescriptionBEGIN
truncate table DPAudit.dbo.TempTabloList

insert into DPAudit.dbo.TempTabloList (sil, create_date, modify_date)
select
concat( 'drop table ', 'ElasticTransaction.',
case when schema_id = 5 then 'elastic2'
when schema_id = 1 then 'dbo'
end,
'.', name) as sil
,create_date, modify_date
from ElasticTransaction.sys.tables
where
/*
Barış kapanmasını talep etti.
Indexler uçuyormuş 20190527 13:34
*/
(schema_id = 5
and name LIKE '%Temporary%'
and modify_date < getdate()-1)
or
(
schema_id = 1
and name LIKE 'Temp_Mesaj%'
and modify_date < getdate()-1)
or
(
schema_id = 1
and name LIKE 'FV1tab%'
and modify_date < getdate()-1)
or
(
schema_id = 1
and name LIKE 'FVtab%'
and modify_date < getdate()-1)
or
(
schema_id = 1
and name LIKE 'AVtab%'
and modify_date < getdate()-1)
or
(
schema_id = 1
and name LIKE 'tmpCVT%'
and modify_date < getdate()-1)
END

-- Bölüm 2
-- Tabloların kullanım ve erişim bilgileri

BEGIN

declare @CountDataBase as int
set @CountDataBase = 0
set @CountDataBase = (SELECT count(sil) FROM DPAudit.dbo.TempTabloList)

declare @StartCounter as int
set @StartCounter = 1

while(@StartCounter <= @CountDataBase)
begin

declare @Query1 nvarchar(Max)


SELECT @Query1 = sil
FROM DPAudit.dbo.TempTabloList
where RowID= @StartCounter

exec (@Query1)
print @Query1

set @StartCounter = @StartCounter + 1

end
end
RecordBycunay
Record Date30-04-2020 17:29:28
Düzenle
Kopyala
Sil