Obtener la lista de columnas de restricción única (en TSQL)?
Puedo obtener una lista de restricciones únicas con bastante facilidad con la siguiente consulta:
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='UNIQUE'
Pero, ¿cómo puedo obtener una lista de las columnas a las que se aplica cada restricción única?
29
4 answers
Véase INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
26
Author: Ed Harper,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-04-20 12:55:12
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-04-20 12:55:12
Ed es correcto, las columnas se exponen en la vista de uso de la columna de restricción, para hacer su vida más fácil, el sql krufted para ello.
select TC.Constraint_Name, CC.Column_Name from information_schema.table_constraints TC
inner join information_schema.constraint_column_usage CC on TC.Constraint_Name = CC.Constraint_Name
where TC.constraint_type = 'Unique'
order by TC.Constraint_Name
41
Author: Andrew,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-04-20 13:02:39
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-04-20 13:02:39
SELECT *
FROM sys.indexes i
JOIN sys.index_columns ic
ON i.index_id = ic.index_id
AND i.object_id = ic.object_id
WHERE i.is_unique_constraint = 1;
3
Author: Kenny Evitt,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-05-30 14:02:31
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-05-30 14:02:31
Solo para referencia de usuarios de MySQL, Lo mismo se puede lograr con las siguientes consultas:
Para encontrar cualquier restricción única en una tabla
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='UNIQUE' and table_name='db_my_table'
Para encontrar una lista única de columnas con todas las columnas
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME='cons_name' and TABLE_NAME='db_my_table'
Para encontrar una lista de columnas única con la vista requerida
select CONSTRAINT_NAME,COLUMN_NAME,TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME='cons_name' and TABLE_NAME='db_my_table'
1
Author: Sanjay Jain,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-05-14 11:30:01
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-05-14 11:30:01