Добрый день, коллеги!
Возникла небольшая проблемка ...
Имеется таблица, у которой в расширенных свойствах полей описаны заголовки столбцов, условия ограничения и т.д.
Хочется создать таблицу, в которой каждому полю таблицы соответствовала одна строка. Например, есть столбец1 у которого определено только имя, есть столбец2 у которого определены другие свойства...
Поле1 MSCaption Код
Поле2 MSCaption Поле2
Поле2 MSDescription Это поле2
Поле2 ValidationRule >1 and < 999
Поле3 ValidationRule >#01.01.2019# AND < #01.01.2020#
Поле3 ValidationText "Сеньёр, Вы не правы!"
Что хочется?
Создать таблицу для передачи по запросу из стороннего приложения в виде:
Поле Заголовок Описание Ограничения Сообщение_об_ошибке

Поле1 Код
Поле2 Поле2 Это поле2 >1 AND < 999
Поле3 >#01.01.2019# AND < #01.01.2020# "Сеньёр, Вы не правы!"

Причем, в столбцах, для которых нет введенного ранее значения, выводилась пустая строка ""
Т.е.

Поле Заголовок Описание Ограничения Текст

Поле1 Код "" "" ""
Поле2 Поле2 Это поле2 >1 and < 999 ""
Поле3 "" "" >#01.01.2019# AND < #01.01.2020# "Сеньёр, Вы не правы!"

Причем, если в расширенных свойствах нет описания заголовка (MSCaption), выводилось бы имя столбца из описания таблицы (Field1, Field2 ...)
Прошу помощи у уважаемого сообщества в решении моей, наверное не сильно сложной задаче.
С благодарностью ....

Добрый день Владислав Колосов!
Задача стоит несколько иная. Будет приложение на С++, в котором надо отобразить таблицу.
Чтобы правильно отобразить столбцы по заголовкам и контролировать правильность ввода
на этапе формы, мне и нужно вытянуть расширенные свойства полей из таблицы.

Добрый день, коллеги!
Как описывал ранее, потребовалась функция, которая возвращала бы расширенные свойства всех полей заданной таблицы.
Делается это для того, чтобы в стороннем приложении (в частности CBuilder):
1) Все столбцы имеют русское название (у кого есть - это проблема приложения)
2) Если зачесалась моя левая пятка и я поменял название столбца с "Код" на "Счетчик", то он изменится у ВСЕХ пользователей и нет необходимости править стороннее приложение.
3) Зачесалась моя правая пятка и я изменил правило на значение, оно изменится у ВСЕХ пользователей без необходимости править стороннее приложение.
4) и т.д.

Естественно, все это происходит на этапе формирования формы стороннего приложения...

Пока гуру SQL скромно обходят своим вниманием эту тему, пришлось тщательно прошерстить тырнет и скомпилировать
примеры в работоспособный пример.


SELECT * FROM (
SELECT clmns.name AS ColumnName, p.name AS ExtendedPropertyName,
CAST(p.value AS sql_variant) AS ExtendedPropertyValue
FROM sys.objects o INNER JOIN
sys.all_columns clmns ON clmns.object_id = o.object_id INNER JOIN
sys.extended_properties p ON p.major_id = o.object_id AND p.minor_id = clmns.column_id AND p.class = 1
WHERE (o.type IN ('U', 'V')) AND (o.name = 'Имя_таблицы_в_БД') AND
(p.name = 'Name' OR
p.name = 'OrdinalPosition' OR
p.name = 'Type' OR
p.name = 'Size' OR
p.name = 'MS_Caption' OR
p.name = 'MS_Description' OR
p.name = 'ValidationRule' OR
p.name = 'ValidationText') )PropTable
PIVOT (MAX(ExtendedPropertyValue) FOR ExtendedPropertyName IN ([OrdinalPosition],
[Type],
[Size],
[MS_Caption],
[MS_Description],
[ValidationRule],
[ValidationText])) AS pvt
ORDER BY CAST(OrdinalPosition AS INT) ASC;



Что имели до и после
1) Все параметры расположены в одном столбце (исходно)
2) Развернули строки по полям (выход)

Осталось найти, где хранятся соответствие кодов типов данных и их описанием (например, 8 = datetime), а также как заменить в выходном наборе данных заменить числовой код словесным описанием.
[img=]
[img=]

Не смог сразу вставить вторую картинку
Вариант после разворачивания таблицы по столбцам

Извините, но это не я храню так, а MS SQL сервер :)
Я только пытаюсь представить их в удобоваримом виде.
Закомментируйте секцию PIVOT и убедитесь в этом...

Владислав Колосов,
поэтому я "закатал рукава" и оформил этот скрипт как хранимую процедуру.
Бизнес-логика должна хранится на сервере данных - она и храниться.
Клиентское приложение получает данные по запросу.
Управляется централизованно...
Что не так?

Спасибо, court! Обязательно посмотрю там, возможно и оттуда возможно будет вытащить данные.
Таблица была импортирована из Access, тогда возможно, тип datatime 8 - это access кодировка типов данных.
Действительно в MS SQL datetime имеет код 61. Буду думать...
Интересно, есть ли описание "стандартных" расширенных свойств полей?
Наверняка же параметр MS_Caption не от фонаря назначается.

То Владислав Колосов - я пробовал эту функцию. Мне не удалось завернуть ее в PIVOT.
Сервер отчаянно ругался, не помню уж об чем. Поэтому и пришлось искать пути через чтение таблиц.

Уважаемый StarikNavy, просвятите, если не трудно, как по-фэншую должна храниться бизнес-логика?

То Владислав Колосов - доберусь до сервера - проверю Ваше предложение. Структура запроса может меняться редко и слегка подправить приложение - не проблема, динамический запрос - тема другого разговора, если в нем возникнет необходимость.
Клиент должен съесть только то, что ему отдали. Проблемы приложения, как оно его разгребет...

То buser - простите мою некомпетентность, что есть EAV?

Уважаемые джентельмены, а есть ли утилитка, которая преобразует базу access в таблицу MS SQL? Штатными средствами и access и MS SQL сервера таблицы переносятся, но все ограничения игнорируются.
Имеется проект-прикидка для миграции на SQL сервер, не хочется все свойства полей переписывать руками.
А таблиц более 40 ...

Основная проблема, которую я хочу решить таким образом - это "автоматическое" изменение отображения данных на форме приложения (не важно на чем оно написано). Также, если я допустил где-то ляп в наложении ограничений на поля и исправил это, необходимо перекомпилировать приложения и заменить их у пользователей. Также, если ужесточились ограничения, например вместо диапазона дат 2017 - 2050 я решил ввести 2020-2050. Скомпилированное приложение, при скомпилированном коде с встроенными ограничениями честно пропустит старый диапазон дат и получит отлуп от сервера, поскольку там уже другие ограничения. Задуманное мною не ново - приложение запрашивает сервер о полях таблицы через вьюшку или процедуру. Получает в ответ как называются поля по-русски и список ограничений на поля. Прикладная программа на этапе построения формы рассовывает эти ограничения по полям. Таким образом получается "автоматическая" синхронизация правил сервер-приложение.
Согласитесь, что отлавливать ошибки на уровне ввода в форму гораздо лучше, чем отправив запрос на сервер, ожидая получить ответ от него, получаешь сообщение об ошибке.
Вторая причина не менее банальна. Я не считаю себя гуру программирования и эстетом в конструировании форм. Возможно, найдется умелец, который захочет написать свой интерфейс к БД на другом языке. И тогда мне придется каждому объяснять что как называется, чем ограничивается. А здесь все проще - запросил - получи. И делай с этим все что хочешь...