Hola:
Tengo una vista que me da información de las 'relaciones' existentes en una base de datos, pero no 'veo' donde saber si una relación está activa (ALTER TABLE ... WITH
CHECK ADD CONSTRAINT ...) o inactiva (ALTER TABLE ... WITH
NOCHECK ADD CONSTRAINT ...). Un ejemplo para probar lo que quiero:
Creamos las tablas y las relaciones:
CREATE TABLE [dbo].[PARENTTBL](
[PK] [int] NOT NULL,
CONSTRAINT [PK_PARENTTBL] PRIMARY KEY CLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CHILDTABLE](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FK1] [int] NOT NULL,
[FK2] [int] NOT NULL,
CONSTRAINT [PK_CHILDTABLE] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CHILDTABLE] WITH CHECK ADD CONSTRAINT [FK_CHILDTABLE_PARENTTBL_1] FOREIGN KEY([FK1])
REFERENCES [dbo].[PARENTTBL] ([PK])
GO
ALTER TABLE [dbo].[CHILDTABLE] CHECK CONSTRAINT [FK_CHILDTABLE_PARENTTBL_1]
GO
ALTER TABLE [dbo].[CHILDTABLE] WITH NOCHECK ADD CONSTRAINT [FK_CHILDTABLE_PARENTTBL_2] FOREIGN KEY([FK2])
REFERENCES [dbo].[PARENTTBL] ([PK])
GO
ALTER TABLE [dbo].[CHILDTABLE] NOCHECK CONSTRAINT [FK_CHILDTABLE_PARENTTBL_2]
GO
Hacemos una vista para obtener las relaciones existentes:
/* !!!
COMO PUEDO SABER QUE 'FK_CHILDTABLE_PARENTTBL_2' TIENE LA OPCION 'NOCHECK'
!!!
*/
SELECT TBL_ORIGEN.CONSTRAINT_SCHEMA + '.' + TBL_ORIGEN.CONSTRAINT_NAME AS RELATION
, TBL_REL.TABLE_SCHEMA + '.' + TBL_REL.TABLE_NAME AS PARENT_TBL
, TBL_ORIGEN.TABLE_SCHEMA + '.' + TBL_ORIGEN.TABLE_NAME AS CHILD_TBL
, TBL_REL.CONSTRAINT_SCHEMA + '.' + TBL_REL.CONSTRAINT_NAME AS CHILD_KEY
, REL_FK.UPDATE_RULE, REL_FK.DELETE_RULE
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS TBL_ORIGEN
LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS REL_FK ON
REL_FK.CONSTRAINT_CATALOG = TBL_ORIGEN.CONSTRAINT_CATALOG AND
REL_FK.CONSTRAINT_SCHEMA = TBL_ORIGEN.CONSTRAINT_SCHEMA AND
REL_FK.CONSTRAINT_NAME = TBL_ORIGEN.CONSTRAINT_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS TBL_REL ON
TBL_REL.TABLE_CATALOG = REL_FK.UNIQUE_CONSTRAINT_CATALOG AND
TBL_REL.TABLE_SCHEMA = REL_FK.UNIQUE_CONSTRAINT_SCHEMA AND
TBL_REL.CONSTRAINT_NAME = REL_FK.UNIQUE_CONSTRAINT_NAME
WHERE NOT TBL_REL.CONSTRAINT_NAME IS NULL AND
TBL_ORIGEN.TABLE_NAME = 'CHILDTABLE'
La pregunta es ¿Como puedo en una vista, saber si una 'relación' está activa o no?
Un saludo
