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

Можно:

[sql]
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
[/sql]

Здесь пример запроса получение 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;

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

[sql]
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
[/sql]

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

[sql]
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
[/sql]

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

[sql]
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

[/sql]