IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UpdateNewSayacSirano]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE UpdateNewSayacSirano
GO

CREATE PROCEDURE dbo.UpdateNewSayacSirano
AS
BEGIN
    DECLARE @SAYAC2 NVARCHAR(MAX);

    -- Cursor tanımlanıyor
    DECLARE SayacBakim CURSOR FOR
    SELECT TABLENAME 
    FROM (
        SELECT *, 
               (SELECT DATA_TYPE 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = TABLENAME  
                  AND COLUMN_NAME = PRIMARYKEY) AS DATATYPE 
        FROM (
            SELECT *, 
                   (SELECT COLUMN_NAME 
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC    
                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU 
                        ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY'    
                        AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME    
                        AND KU.TABLE_NAME = SAYACLAR.TABLENAME 
                        AND ORDINAL_POSITION = 1) AS PRIMARYKEY
            FROM SAYACLAR
        ) AS SAYACLARSQL
    ) AS PRIMARYINTKONTROL
    WHERE PRIMARYKEY IS NOT NULL 
      AND DATATYPE = 'int';

    OPEN SayacBakim;
    FETCH NEXT FROM SayacBakim INTO @SAYAC2;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @TABLEKONTROL BIT;
        DECLARE @PRIMARYINTKONTROL BIT;

        -- Tablo kontrolü yapılıyor
        SELECT @TABLEKONTROL = CASE 
                                 WHEN COUNT(TABLE_NAME) > 0 THEN 1 
                                 ELSE 0 
                               END 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_NAME = @SAYAC2;

        IF @TABLEKONTROL = 1
        BEGIN
            DECLARE @sql NVARCHAR(MAX);
            
            -- Dinamik SQL oluşturuluyor
            SET @sql = (SELECT 'UPDATE SAYACLAR SET SIRANO = (' + SQLSTRING + ') WHERE TABLENAME = ' + '''' + @SAYAC2 + '''' 
                        FROM (
                            SELECT *, 'SELECT ISNULL(MAX(' + PRIMARYKEY + '), 0) + 1 FROM ' + TABLENAME AS SQLSTRING 
                            FROM (
                                SELECT *, 
                                       (SELECT TOP 1 COLUMN_NAME 
                                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC    
                                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU 
                                            ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY'    
                                            AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
                                            AND KU.TABLE_NAME = SAYACLAR.TABLENAME 
                                            AND ORDINAL_POSITION = 1) AS PRIMARYKEY 
                                FROM SAYACLAR
                            ) AS SAYACLARSQL
                        ) AS MAG 
                        WHERE TABLENAME = @SAYAC2);

            -- Dinamik SQL çalıştırılıyor
            EXEC sp_executesql @sql;

            -- Cursor ile bir sonraki tabloya geçiliyor
            FETCH NEXT FROM SayacBakim INTO @SAYAC2;
        END
    END

    -- Cursor kapatılıyor
    CLOSE SayacBakim;
    DEALLOCATE SayacBakim;
END;
