Untitled

mail@pastecode.io avatar
unknown
sqlserver
2 years ago
1.2 kB
2
Indexable
Never
-- Toplam kayıt sayısını bul
DECLARE @count int = (select count(*)
from sys.tables tab
    left outer join sys.indexes pk
        on tab.object_id = pk.object_id 
        and pk.is_primary_key = 1
where pk.object_id is not null --and tab.[name] like '%_old'
)

DECLARE @i int = 0

-- Tek tek tüm kayıtları dolaş
WHILE @i < @count
BEGIN

DECLARE @schema_name varchar(1024)
DECLARE @table_name varchar(1024)

	-- Tek tek isimleri al
	----------------------
		select @schema_name = schema_name(tab.schema_id), 
			@table_name = tab.[name]
		from sys.tables tab
			left outer join sys.indexes pk
				on tab.object_id = pk.object_id 
				and pk.is_primary_key = 1
		where pk.object_id is not null --and tab.[name] like '%_old'
		order by schema_name(tab.schema_id),
			tab.[name]
		OFFSET @i ROWS
		fetch next 1 ROWS ONLY;

		SELECT @schema_name, @table_name

		DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX);

		SELECT @table = @schema_name + '.' + @table_name;

		SELECT @sql = 'ALTER TABLE ' + @table 
		+ ' DROP CONSTRAINT ' + name + ';'
		FROM sys.key_constraints
		WHERE [type] = 'PK'
		AND [parent_object_id] = OBJECT_ID(@table);

		EXEC sp_executeSQL @sql

	SET @i = @i + 1
END