Вопрос
Технические вопросы
7.x

Получение UId колонки таблицы объекта, зная имя колонки и имя таблицы, используя SQL, но не используя метаданные

trickbz
4 июня 2015 13:53

Здравствуйте,

Допустим, есть таблица UsrIssues, в ней есть лукапная колонка UsrIssueType - внешний ключ на таблицу UsrIssueType. Т.е. исходные данные для SQL - строки "UsrIssues" и "UsrIssueType". Необходимо получить UId колонки UsrIssueType, тот, что мы получаем, открывая метаданные объекта UsrIssue, и поиском по тексту ищем строку "UsrIssueType", и копируем UId сверху.

Вообщем, вопрос - можно ли забрать данные из метаданных с помощью SQL.

Спасибо!

Нравится

4 комментария
Олейник Дмитрий
4 июня 2015 14:37

Можно:

select p.name, p.value
from sys.extended_properties p
inner join sys.tables t on p.major_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id
where t.Name = 'FIAS' --таблица
and c.Name = 'Address' --колонка
and p.Name = 'TS.EntitySchema.UId' --extended property name
Адасюк Валерий Викторович
4 июня 2015 15:45

Здесь пример запроса получение UId для таблицы 'Contact'и колонки 'SalutationTypeId'

SELECT value AS [UId]
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE class = 1
and ep.name = 'TS.EntitySchemaColumn.UId'
and t.name = 'Contact'
and c.name = 'SalutationTypeId'
order by t.name;

trickbz
4 июня 2015 16:40

Спасибо обоим! Валерий, у меня получилось длиннее, спасибо за коротку версию :lol:

declare @detailObjectName nvarchar(255) = 'UsrCandNotewortev';
declare @typeColumnName nvarchar(255) = 'AnniversaryType';
declare @typeColumnNameFull nvarchar(255) = @typeColumnName + 'Id';
declare @majorId int, @minorId int;
 
select top 1 @majorId = p.major_id, @minorId = p.minor_id
FROM sys.extended_properties p
INNER JOIN sys.TABLES t ON p.major_id = t.object_id
INNER JOIN sys.COLUMNS c ON c.object_id = t.object_id
WHERE 
	t.Name = @detailObjectName
	AND c.Name = @typeColumnNameFull
	AND p.name = 'TS.ColumnName' 
	AND p.value = @typeColumnNameFull;
declare @typeColumnUId uniqueidentifier = 
(
	select top 1 CONVERT(uniqueidentifier, p.value) 
	from sys.extended_properties p 
	where 
		p.major_id = @majorId 
		and p.minor_id = @minorId 
		and p.name = 'TS.EntitySchemaColumn.UId'
);
select @typeColumnUId as TypColumnUId

Сделал функцией:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetColumnUId]
(
	@tableName nvarchar(255)
	,@columnName nvarchar(255)
)
RETURNS uniqueidentifier
AS
BEGIN
	declare @columnUId uniqueidentifier;
 
	SELECT @columnUId= CONVERT(uniqueidentifier, value)
	FROM sys.extended_properties AS ep
	INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
	INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
	WHERE 
		class = 1
		and ep.name = 'TS.EntitySchemaColumn.UId'
		and t.name = @tableName
		and c.name = @columnName
	order by t.name;
 
	return @columnUId;
END
GO
trickbz
6 июня 2015 19:27

Вариант покороче, ну точно попроще, оказывается, есть спец-таблица..

declare @tableName nvarchar(255) = 'UsrVacancies';
declare @columnName nvarchar(255) = 'UsrCustomer';
 
select
	sch.Name TableName
	,esr.ColumnName
	,esr.ColumnCaption
	,ColumnUId
from SysEntitySchemaReference esr
join SysSchema sch on sch.Id = esr.SysSchemaId
where 
	sch.Name = @tableName
	and esr.ColumnName like @columnName
Показать все комментарии