Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Узнать поле Primary Key у таблицы  [new]
cha0ss
Member

Откуда: г. Димитровград, ООО "АИС Город"
Сообщений: 142
БД MS SQL 2005

Возникла следующая задача, необходимо получить имя столбца, которое является Primary Key
Делаю выборку из таблицы sysconstraints, но столбец colid заполнен только у Foreign Key, а у Primary всегда 0.

Подскажите откуда достать PK...
14 окт 09, 15:02    [7785242]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
Supra93
Member

Откуда:
Сообщений: 8174
E.G.
select * from sys.objects where type = 'PK'
14 окт 09, 15:05    [7785273]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104760
cha0ss

Делаю выборку из таблицы sysconstraints, но столбец colid заполнен только у Foreign Key, а у Primary всегда 0.

Неправда
14 окт 09, 15:06    [7785281]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
sys.key_constraints + sys.indexes + sys.index_columns

-------------------------
There’s no silver bullet!
14 окт 09, 15:11    [7785334]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
Supra93
Member

Откуда:
Сообщений: 8174
Чет я нето ляпнул
BOL

Как найти столбцы первичного ключа указанной таблицы?
Перед запуском следующего запроса замените <database_name> и <schema_name.table_name> действительными именами.

USE <database_name>;
GO
SELECT i.name AS index_name
    ,ic.index_column_id
    ,key_ordinal
    ,c.name AS column_name
    ,TYPE_NAME(c.user_type_id)AS column_type 
    ,is_identity
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic 
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c 
    ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1 
    AND i.object_id = OBJECT_ID('<schema_name.table_name>');
14 окт 09, 15:18    [7785404]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
cha0ss
Member

Откуда: г. Димитровград, ООО "АИС Город"
Сообщений: 142
Всем спасибо, последний запрос вернул именно то, что нужно =)
Не подумал в sys.indexes заглянуть :(
14 окт 09, 15:25    [7785458]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
SELECT QUOTENAME(PK.CONSTRAINT_NAME)[Primary Key], QUOTENAME(PK.COLUMN_NAME)[Field]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
ON PK.CONSTRAINT_CATALOG=C.UNIQUE_CONSTRAINT_CATALOG
AND PK.CONSTRAINT_SCHEMA=C.UNIQUE_CONSTRAINT_SCHEMA
AND PK.CONSTRAINT_NAME=C.UNIQUE_CONSTRAINT_NAME
WHERE PK.TABLE_NAME=N'ИмяТаблицы' AND OBJECT_ID(PK.CONSTRAINT_NAME,'PK') IS NOT NULL
ORDER BY PK.ORDINAL_POSITION;
14 окт 09, 15:43    [7785619]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
То, что я тут понаписал, выдаст именно констрейнт PRIMARY KEY.
Однако, если интересует, на какие группы полей может ссылаться FOREIGN KEY из другой таблицы,
то этого мало! Потому что годятся такие группы, для которых существует уникальный индекс.
Констрейнты PRIMARY KEY и UNIQUE гарантируют такой индекс автоматически. Но его можно и просто создать без констрейнта.
В последнем случае поможет только sys.indexes
14 окт 09, 16:10    [7785885]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Чего-то я перемудрил - INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS явно лишняя!
SELECT QUOTENAME(CONSTRAINT_NAME) [Primary Key], QUOTENAME(COLUMN_NAME) [Field]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME=N'ИмяТаблицы' AND OBJECT_ID(CONSTRAINT_NAME,'PK') IS NOT NULL
ORDER BY ORDINAL_POSITION;
14 окт 09, 16:32    [7786124]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
cha0ss
Member

Откуда: г. Димитровград, ООО "АИС Город"
Сообщений: 142
Да похоже задача не такая уж тривиальная. В догонку: потребовалось удалить все существующие в БД Foreign и Primary keys. Можно ли удалить всех их одним запросом, или придется прогоняться по всем таблицам и ALTER TABLE DROP CONSTRAINT?

Впринципе не влом по циклу гонять, просто интересно может так тоже можно...
15 окт 09, 09:23    [7788579]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
cha0ss
Да похоже задача не такая уж тривиальная. В догонку: потребовалось удалить все существующие в БД Foreign и Primary keys. Можно ли удалить всех их одним запросом, или придется прогоняться по всем таблицам и ALTER TABLE DROP CONSTRAINT?

Впринципе не влом по циклу гонять, просто интересно может так тоже можно...
DECLARE @SQL NVARCHAR(MAX);
SET @SQL=N'';
WITH DropConstraint(PK,SQL) AS
( 
 SELECT DISTINCT OBJECT_ID(CONSTRAINT_NAME,'PK'),N'ALTER TABLE'+QUOTENAME(TABLE_SCHEMA)+N'.'+QUOTENAME(TABLE_NAME)+N'DROP CONSTRAINT'+QUOTENAME(CONSTRAINT_SCHEMA)+N'.'+QUOTENAME(CONSTRAINT_NAME)
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 WHERE OBJECT_ID(CONSTRAINT_NAME,'PK') IS NOT NULL OR OBJECT_ID(CONSTRAINT_NAME,'F') IS NOT NULL
)
SELECT @SQL=@SQL+CASE @SQL WHEN N''THEN N''ELSE N';'END+SQL
FROM DropConstraint
ORDER BY PK;

--PRINT @SQL;

EXECUTE(@SQL);
15 окт 09, 09:51    [7788695]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
cha0ss
Member

Откуда: г. Димитровград, ООО "АИС Город"
Сообщений: 142
Спасибо большое! все работает.
Единственное подозрение, а что если PRIMARY KEY будет стоять в списке раньше чем скажем FOREIGN KEY, который на него имеет связь, тогда он не даст удалить PK.
Надо наверно еще сортировку поставить и тогда все замечательно будет.

P.S А да кстати когда первый раз запустил поругался на "." между <имя схемы>.<имя ограничения>, я "<имя схемы>." убрал
15 окт 09, 10:04    [7788780]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
DECLARE @SQL NVARCHAR(MAX);
SET @SQL=N'';
WITH DropConstraint(PK,SQL) AS
( 
 SELECT DISTINCT OBJECT_ID(CONSTRAINT_NAME,'PK'),N'ALTER TABLE'+QUOTENAME(TABLE_SCHEMA)+N'.'+QUOTENAME(TABLE_NAME)+N'DROP CONSTRAINT'+QUOTENAME(CONSTRAINT_SCHEMA)+N'.'+QUOTENAME(CONSTRAINT_NAME)
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 WHERE OBJECT_ID(CONSTRAINT_NAME,'PK') IS NOT NULL OR OBJECT_ID(CONSTRAINT_NAME,'F') IS NOT NULL
)
SELECT @SQL=@SQL+CASE @SQL WHEN N''THEN N''ELSE N';'END+SQL
FROM DropConstraint
ORDER BY PK;

--PRINT @SQL;

EXECUTE(@SQL);
Поправка:
DECLARE @SQL NVARCHAR(MAX);
SET @SQL=N'';
WITH DropConstraint(PK,SQL) AS
( 
 SELECT DISTINCT
  OBJECT_ID(CONSTRAINT_NAME,'PK') /*FOREIGN KEYs надо удалить раньше, чем PRIMARY KEYs*/
 ,N'ALTER TABLE'+QUOTENAME(TABLE_SCHEMA)+N'.'+QUOTENAME(TABLE_NAME)
 +N'DROP CONSTRAINT'+QUOTENAME(CONSTRAINT_NAME)
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 WHERE OBJECT_ID(CONSTRAINT_NAME,'PK') IS NOT NULL OR OBJECT_ID(CONSTRAINT_NAME,'F') IS NOT NULL
)
SELECT @SQL=@SQL+SQL+N';'
FROM DropConstraint
ORDER BY PK;

--PRINT @SQL;

EXECUTE(@SQL);
15 окт 09, 10:13    [7788844]     Ответить | Цитировать Сообщить модератору
 Re: Узнать поле Primary Key у таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
cha0ss
что если PRIMARY KEY будет стоять в списке раньше чем скажем FOREIGN KEY, который на него имеет связь, тогда он не даст удалить PK.
Сортировка там уже стояла
cha0ss
P.S А да кстати когда первый раз запустил поругался на "." между <имя схемы>.<имя ограничения>, я "<имя схемы>." убрал
Да, я тоже заметил - см. поправленный вариант.
15 окт 09, 10:15    [7788857]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить