Espero que te sirva.
CREATE PROCEDURE [dbo].[DropConstraints] @tablename nvarchar(500),
@columnname nvarchar(500)
AS
SELECT CONSTRAINT_NAME, 'C' AS type
INTO #dependencies
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME =
@tablename AND COLUMN_NAME = @columnname
INSERT INTO #dependencies
select d.name, 'C'
from sys.default_constraints d
join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id =
d.parent_object_id
join sys.objects o ON o.object_id = d.parent_object_id
WHERE o.name = @tablename AND c.name = @columnname
INSERT INTO #dependencies
SELECT i.name, 'I'
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.index_id = i.index_id and
ic.object_id=i.object_id
JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id
JOIN sys.objects o ON o.object_id = i.object_id
where o.name = @tableName AND i.type=2 AND c.name = @columnname AND
is_unique_constraint = 0
DECLARE @dep_name nvarchar(500)
DECLARE @type nchar(1)
DECLARE dep_cursor CURSOR
FOR SELECT * FROM #dependencies
OPEN dep_cursor
FETCH NEXT FROM dep_cursor
INTO @dep_name, @type;
DECLARE @sql nvarchar(max)
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql =
CASE @type
WHEN 'C' THEN 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT
[' + @dep_name + ']'
WHEN 'I' THEN 'DROP INDEX [' + @dep_name + '] ON dbo.[' +
@tablename + ']'
END
print @sql
EXEC sp_executesql @sql
FETCH NEXT FROM dep_cursor
INTO @dep_name, @type;
END
DEALLOCATE dep_cursor
DROP TABLE #dependencies
Saludos
*Pablo I. Rausch*
*[email protected] <[email protected]>*
El 3 de diciembre de 2014, 10:56, Info (Sinergia Software Soluciones) <
[email protected]> escribió:
> Hola Gente,
>
>
>
> Buenos Días!
>
>
>
> Los contacto para obtener una ayudita.
>
>
>
> Necesito hacer un proceso que DROPEE automáticamente las contrainst de una
> tabla (Clientes) porque hay que cambiar el PK.
>
>
>
> El proceso de agregado de la nueva PK en las tablas hijas y el reemplazo
> no es un problema, pero si el dropeo porque es un proceso a ejecutar en
> distintas bases y los FK podrían tener distintos nombres.
>
>
>
> Muchas Gracias.
>
>
>
> Saludos…
>
>
>
> Augusto
>
>
>
