/*birinci yöntem logo ve sql server üzerinden:
hangi firma silinecekse dönemi silinir sonra firması silinir
örnek ilk önce 531li viewleri sil
sonrada tablolardan 531 sil
*/
/*İŞLEM ÖNCESİ VERİTABANININ YEDEK ALINMASI TAVSİYE EDİLİR*/
/*ÖNCE SYSDEN İLGİLİ FİRMANIN DÖNEMİ ÇIKARILIR VE SONRASINDA FİRMA ÇIKAR YAPILIR*/
/*AŞAĞIDAKİ İŞLEMLERE FİRMAYA BAĞLI VİEW,TABLO,PROSEDUR,FONKSIYONLARI SİLMEKTEDİR*/
DECLARE @firmno varchar(3)='999' /*İŞLEM YAPILMASI İSTENİLEN FİRMA NOSU YAZ*/
DECLARE @view_name SYSNAME
DECLARE @table_name SYSNAME
DECLARE @procedure_name SYSNAME
DECLARE @function_name SYSNAME
/*VIEWLERI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE 'LV_' + @firmno + '%' AND xtype='V' )
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @view_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP VIEW '+ @view_name)
FETCH NEXT FROM Logo_objs into @view_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*VIEWLERI SIL BİTİŞ*/
/*TABLOLARI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE 'LG_' + @firmno + '%' AND XTYPE = 'U')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP TABLE '+ @table_name)
FETCH NEXT FROM Logo_objs into @table_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*TABLOLARI SIL BİTİŞ*/
/*PROSEDURLERİ SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE '%' + @firmno + '%' AND XTYPE = 'P')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @procedure_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP PROCEDURE '+ @procedure_name)
FETCH NEXT FROM Logo_objs into @procedure_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*PROSEDURLERİ SIL BİTİŞ*/
/*FONKSİYONLARI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE (name like 'LG_' + @firmno + '%' AND type ='FN')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @function_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP FUNCTION '+ @function_name)
FETCH NEXT FROM Logo_objs into @function_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*FONKSİYONLARI SIL BİTİŞ*/
Tag: delete
CMD FAST DELETE
OPEN CMD AS ADMINISTRATOR
CD C:\Users\Administrator\AppData\Local\Temp
DEL /F/Q/S *.* > NUL
LOGO KULLANILMAYAN BIRIMLERI SILME SQL
DELETE FROM LG_210_UNITSETL WHERE LOGICALREF IN (
SELECT LOGICALREF FROM (
SELECT DISTINCT
LOGICALREF,
M.ANAMALZEMEKOD,
M.ANAMZELEMEAD,
M.BIRIMKODU,
M.BIRIMADI,
ISNULL(
(
SELECT DISTINCT
CONVERT(VARCHAR, UOMREF)
FROM LG_210_01_STLINE S
WHERE S.UOMREF = M.LOGICALREF
AND S.LINETYPE = 0
),
'YOK'
) STLINEDAVARMI,
ISNULL(
(
SELECT DISTINCT
CONVERT(VARCHAR, UOMREF)
FROM LG_210_01_ORFLINE S
WHERE S.UOMREF = M.LOGICALREF
AND S.LINETYPE = 0
),
'YOK'
) ORFLINEDAVARMI,
CASE
WHEN ISNULL(
(
SELECT DISTINCT
CONVERT(VARCHAR, UOMREF)
FROM LG_210_01_STLINE S
WHERE S.UOMREF = M.LOGICALREF
AND S.LINETYPE = 0
),
'YOK'
) = 'YOK'
AND ISNULL(
(
SELECT DISTINCT
CONVERT(VARCHAR, UOMREF)
FROM LG_210_01_ORFLINE S
WHERE S.UOMREF = M.LOGICALREF
AND S.LINETYPE = 0
),
'YOK'
) = 'YOK' THEN
'SIL'
ELSE
'SILME'
END SILINEBILIR
FROM
(
SELECT UNITSETL.CODE BIRIMKODU,
UNITSETL.NAME BIRIMADI,
(
SELECT CODE
FROM LG_210_UNITSETF UNITSETF
WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF
) ANAMALZEMEKOD,
(
SELECT UNITSETF.NAME
FROM LG_210_UNITSETF UNITSETF
WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF
) ANAMZELEMEAD,
LOGICALREF,
CODE,
NAME,
UNITSETREF,
LINENR,
MAINUNIT,
CONVFACT1,
CONVFACT2,
WIDTH,
LENGTH,
HEIGHT,
AREA,
VOLUME_,
WEIGHT,
WIDTHREF,
LENGTHREF,
HEIGHTREF,
AREAREF,
VOLUMEREF,
WEIGHTREF,
DIVUNIT,
MEASURECODE,
GLOBALCODE
FROM LG_210_UNITSETL UNITSETL WITH (NOLOCK)
) M ) AS T WHERE T.SILINEBILIR='SIL' )
SQL SERVER SISTEMI KASMADAN SILME WHILE
DECLARE @Rowcount INT = 1 WHILE @Rowcount > 0 BEGIN DELETE TOP (50000) FROM LG_013_01_PAYTRANS where ORGLOGOID LIKE 'TNYF%'
SET @Rowcount = @@ROWCOUNT
END
Büyük tablodan sqlden verileri silme
/*bu etkili alttakiler deil fazla*/
declare @counter int
declare @numOfRecords int
declare @batchsize int
set @numOfRecords = (SELECT COUNT(*) AS NumberOfRecords FROM Erp_InventoryReceiptItem with(nolock) where ParentItemId is not null)
set @counter = 0
set @batchsize = 2500
set rowcount @batchsize
while @counter < (@numOfRecords/@batchsize) +1
begin
set @counter = @counter + 1
Update Erp_InventoryReceiptItem set ParentItemId = null;
end
set rowcount 0
----------------------------------------------
/*alttakiler:*/
USE LIVE_UNIMAR_YENI
GO
-- Set to simple mode
ALTER DATABASE LIVE_UNIMAR_YENI SET RECOVERY SIMPLE;
GO
-- Get count of records
SELECT COUNT(*) AS Total FROM Erp_InventoryReceiptItem
GO
-- Delete in batches
DECLARE @VAR_ROWS INT = 1;
WHILE (@VAR_ROWS > 0)
BEGIN
DELETE TOP (100000) FROM Erp_InventoryReceiptItem
SET @VAR_ROWS = @@ROWCOUNT;
CHECKPOINT;
END;
GO
-- Set to full mode
ALTER DATABASE LIVE_UNIMAR_YENI SET RECOVERY FULL;
GO
Blog of Sem 