Jump to content

Eliminar todos Constraints de uma tabela


martim07
 Share

Recommended Posts

Olá a todos,

eu preciso eliminar todos os Constraints de uma tabela e não sei como fazer. Eu não queria eliminar um a um a partir do seu nome, pois este nome varia conforme as instâncias que tenho instaladas.

Estou a usar vb6 e SQL Server 2005 Express.

Cumprimentos a todos.

Link to comment
Share on other sites

Experimenta:

Alter Table xxx nocheck constraint all

Oracle Certified Professional - AdministraçãoOracle Certified Professional - Pl/sqlMCPD - Microsoft Certified Professional DeveloperMCTS - Microsoft Certified Technology Specialist

Link to comment
Share on other sites

Eu percebi que querias só desligar as constraints...

Então tens de fazer código T-Sql

DECLARE @database NVARCHAR(50)

DECLARE @table NVARCHAR(50)

DECLARE @sql NVARCHAR(255)

SET @database = ‘DB_MyDatabase’

SET @table = ‘Employee’

WHILE EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE constraint_catalog=@database AND table_name=@table)

BEGIN

SELECT @sql = ‘ALTER TABLE ‘ + @table + ‘ DROP CONSTRAINT ‘ + CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE constraint_catalog=@database AND table_name=@table

EXEC sp_executesql @sql

END

Oracle Certified Professional - AdministraçãoOracle Certified Professional - Pl/sqlMCPD - Microsoft Certified Professional DeveloperMCTS - Microsoft Certified Technology Specialist

Link to comment
Share on other sites

Já resolvi.

utilizei parte desta query para chegar lá:

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,

SCHEMA_NAME(schema_id) AS SchemaName,

OBJECT_NAME(parent_object_id) AS TableName,

type_desc AS ConstraintType

FROM sys.objects

WHERE type_desc LIKE '%CONSTRAINT'

Obrigado pela ajuda.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.