SET NOCOUNT ON
DECLARE @i INT=1
DECLARE @SFRM VARCHAR(3)
DECLARE @EB_OF TABLE (FRMNR VARCHAR(3))
DECLARE @EB_SIL_SORGU TABLE(SN INT,SORGU VARCHAR(MAX))
WHILE @i<1000
BEGIN
IF NOT EXISTS (SELECT NR FROM L_CAPIFIRM WHERE NR=@i)
BEGIN
INSERT INTO @EB_OF (FRMNR) VALUES (RIGHT(('000'+CONVERT(VARCHAR(3),@i)),3))
END
SET @i=@i+1
END
DECLARE CRS_SOR CURSOR FOR
SELECT FRMNR FROM @EB_OF ORDER BY FRMNR
OPEN CRS_SOR
FETCH NEXT FROM CRS_SOR INTO @SFRM
WHILE @@FETCH_STATUS =0
BEGIN
INSERT INTO @EB_SIL_SORGU
SELECT
CASE WHEN TABLE_TYPE='BASE TABLE' THEN 1 ELSE 0 END AS SN,
CASE WHEN TABLE_TYPE='BASE TABLE' THEN 'DROP TABLE '+ TABLE_NAME
WHEN TABLE_TYPE='VIEW' THEN 'DROP VIEW '+ TABLE_NAME END AS SORGU
FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME LIKE '%'+@SFRM+'%' AND (TABLE_NAME LIKE 'L[_]%' OR TABLE_NAME LIKE 'LG[_]%' OR TABLE_NAME LIKE 'LV[_]%')
ORDER BY TABLE_TYPE DESC
FETCH NEXT FROM CRS_SOR INTO @SFRM
END
CLOSE CRS_SOR
DEALLOCATE CRS_SOR
SELECT SORGU FROM @EB_SIL_SORGU ORDER BY SN,SORGU