Sacar los constraints

03/02/2008 - 17:03 por Rolando | Informe spam
Es SQL Server 2005 necesito sacar de unas tablas especificas sus
constraints, especificamente sus restricciones check y sus relaciones con
otras tablas (foreigns).
Se puede hacer ? no es muy lento?
 

Leer las respuestas

#1 Alejandro Mesa
03/02/2008 - 18:34 | Informe spam
Rolando,

Esa informacion la puedes encontrar en las vistas de sistema
INFORMATION_SCHEMA. Deberas consultar los BOL para leer sobre ellas porqur
son unas cuantas.

SELECT
tc.TABLE_CATALOG,
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
tc.CONSTRAINT_CATALOG,
tc.CONSTRAINT_SCHEMA,
tc.CONSTRAINT_NAME,
ckc.CHECK_CLAUSE
FROM
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS tc
INNER JOIN
INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS ckc
ON tc.CONSTRAINT_CATALOG = ckc.CONSTRAINT_CATALOG
AND tc.CONSTRAINT_SCHEMA = ckc.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = ckc.CONSTRAINT_NAME
WHERE
tc.TABLE_CATALOG = 'Northwind'
AND tc.TABLE_SCHEMA = 'dbo'
AND tc.TABLE_NAME = 'Order Details'

UNION

SELECT
cc.TABLE_CATALOG,
cc.TABLE_SCHEMA,
cc.TABLE_NAME,
cc.CONSTRAINT_CATALOG,
cc.CONSTRAINT_SCHEMA,
cc.CONSTRAINT_NAME,
ckc.CHECK_CLAUSE
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc
INNER JOIN
INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS ckc
ON cc.CONSTRAINT_CATALOG = ckc.CONSTRAINT_CATALOG
AND cc.CONSTRAINT_SCHEMA = ckc.CONSTRAINT_SCHEMA
AND cc.CONSTRAINT_NAME = ckc.CONSTRAINT_NAME
WHERE
cc.TABLE_CATALOG = 'Northwind'
AND cc.TABLE_SCHEMA = 'dbo'
AND cc.TABLE_NAME = 'Order Details'

SELECT
tc.TABLE_SCHEMA,
tc.TABLE_NAME,
rc.CONSTRAINT_NAME,
STUFF(
(
SELECT
',' + kcu.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
WHERE
kcu.TABLE_CATALOG = tc.TABLE_CATALOG
AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND kcu.TABLE_NAME = tc.TABLE_NAME
AND kcu.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
AND kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
ORDER BY
kcu.ORDINAL_POSITION
FOR XML PATH('')
), 1, 1, '') AS [FK_CONSTRAINT_COLUMNS],
pc.TABLE_SCHEMA,
pc.TABLE_NAME,
rc.UNIQUE_CONSTRAINT_NAME,
STUFF(
(
SELECT
',' + kcu.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
WHERE
kcu.TABLE_CATALOG = pc.TABLE_CATALOG
AND kcu.TABLE_SCHEMA = pc.TABLE_SCHEMA
AND kcu.TABLE_NAME = pc.TABLE_NAME
AND kcu.CONSTRAINT_CATALOG = pc.CONSTRAINT_CATALOG
AND kcu.CONSTRAINT_SCHEMA = pc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = pc.CONSTRAINT_NAME
ORDER BY
kcu.ORDINAL_POSITION
FOR XML PATH('')
), 1, 1, '') AS [PK_UQ_CONSTRAINT_COLUMNS]
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS tc
ON rc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
AND rc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
AND rc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS pc
ON pc.CONSTRAINT_CATALOG = rc.UNIQUE_CONSTRAINT_CATALOG
AND pc.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA
AND pc.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
WHERE
tc.TABLE_CATALOG = 'Northwind'
AND tc.TABLE_SCHEMA = 'dbo'
AND tc.TABLE_NAME = 'Order Details'
GO


AMB

"Rolando" wrote:

Es SQL Server 2005 necesito sacar de unas tablas especificas sus
constraints, especificamente sus restricciones check y sus relaciones con
otras tablas (foreigns).
Se puede hacer ? no es muy lento?



Preguntas similares