Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как получить список полей во вьюшке с псевдонимами?  [new]
Opus Magnum
Member

Откуда: 77 Rus
Сообщений: 2205
Доброго дня!

Практически не имею опыта работы с MS SQL, но тут понадобилось.

Задача: получить список исходных полей и список псевдонимов, по которым построили VIEW.

Подробнее: имеется набор VIEW вида
create view [Имя_Вьюшки] as
select
123_field as Идентификатор,
124_field as Комментарий
from 1234_table


Проблема в том, что наименования полей не дают ничего по ним понять (к примеру, 122233_field)
Получить список полей и список вьюшек я умею (information_schema, syscolumns), а вот сочетание - нет.

Подскажите пожалуйста, можно ли как-то получить такое сочетание (имя_таблицы - имя_вьюшки и имя_поля - имя_псевдонима), не распарсивая DDL?

Заранее благодарю
23 янв 17, 13:22    [20135000]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Только парсингом текста view.
23 янв 17, 13:23    [20135005]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
Opus Magnum
Member

Откуда: 77 Rus
Сообщений: 2205
Гавриленко Сергей Алексеевич,

грустно-то как...
Спасибо большое за ответ!
23 янв 17, 13:33    [20135049]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Opus Magnum,

а что надо показывать когда вычисляемое поле?? вот такая ещё есть INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
23 янв 17, 13:51    [20135111]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3984
Opus Magnum
Гавриленко Сергей Алексеевич,

грустно-то как...
Спасибо большое за ответ!


а так?

select * from INFORMATION_SCHEMA.COLUMNS
where table_name=N'Имя_Вьюшки'
23 янв 17, 14:12    [20135223]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
Opus Magnum
Member

Откуда: 77 Rus
Сообщений: 2205
TaPaK,

вычисляемых полей быть не должно, все вычисления происходят в надстройках, в том числе интерфейсных.
23 янв 17, 14:14    [20135231]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
Opus Magnum
Member

Откуда: 77 Rus
Сообщений: 2205
Ролг Хупин,

так вижу список полей и их типов, списка источников не вижу.
23 янв 17, 14:22    [20135280]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
лолл
Member

Откуда:
Сообщений: 450
Гавриленко Сергей Алексеевич,

Нет, не только :) Вам ли не знать)

CREATE TABLE dbo.TestTable
(
  [Id]    Int NULL,
  [Value] VarChar(10) NULL
)
GO

CREATE VIEW dbo.TestView
AS
  SELECT
    [Идентификатор] = [Id],
    [Значение]      = [Value]
  FROM dbo.TestTable
GO

SELECT
  [Поле таблицы] = E.[referenced_minor_name],
  [Псевдоним]    = C.[name]
FROM sys.dm_sql_referenced_entities('dbo.TestView', 'OBJECT') E
INNER JOIN sys.columns C ON C.[object_id] = OBJECT_ID('dbo.TestView', 'V') AND C.[column_id] = E.[referenced_minor_id]
23 янв 17, 14:31    [20135326]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
лолл,

А теперь поменяйте порядок столбцов в представлении, или сделайте представление на основе нескольких таблиц.
23 янв 17, 15:00    [20135514]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
лолл,

фигня всё это, озвученное не получишь, потому что и самому серверу это нафиг не надо. вроде кто-то делал похожее через план запроса, но там не та задача была...
23 янв 17, 15:33    [20135741]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
Opus Magnum,

select object_schema_name(o.object_id),o.name, c.name, o.type_desc
from sys.columns c
join sys.objects o
    on o.object_id = c.object_id
    and o.type='V'
order by 1,2, c.column_id
23 янв 17, 15:38    [20135782]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
Opus Magnum,

По сути да... какое нибудь поле вычисляемое... типа [Разность]
CREATE VIEW dbo.TestView
AS
  SELECT
    [Идентификатор] = [Id],
    [Разность] = (id-lead(Id,1,0) over (order by Id))/count(*) over()
    [Значение]      = [Value]
  FROM dbo.TestTable
GO

Имеет место быть, и какие связи здесь должны отображаться?
23 янв 17, 15:46    [20135837]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
zasandator,

ну ладно, а теперь прочитайте что ТС просит
23 янв 17, 15:48    [20135851]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
TaPaK
zasandator,

ну ладно, а теперь прочитайте что ТС просит

Да не прочитал сразу. список полей и псевдонимов нужен ))). С псевдонимами все понятно, а вот с полями понимания нет:
- вычисляемые поля
- поле из одной таблице но с разного псевдонима типа
select s.name as saler, c.name as client
from sales sl join person s on sl.salerId = s.id join person c on sl.clientId = c.Id
здесь даже вычисляемых полей нет, у одного имя это person.name через salerID у другого тоже только через другое поле связь...
23 янв 17, 16:43    [20136173]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
Как-то так
+
use tempdb;
go

create table dbo.t1 (id int primary key, v1 int);
create table dbo.t2 (id int primary key, v2 int);
go

create view dbo.v1
as
select
 t1.id as c1,
 t1.v1 as c2,
 t2.v2 as c3,
 t1.v1 + t2.v2 as c4
from
 dbo.t1 join
 dbo.t2 on t2.id = t1.id;
go

if object_id('tempdb..#t', 'U') is not null
 drop table #t;
go

declare @x xml, @m nvarchar(50) = N'/*' + cast(newid() as nvarchar(36)) + N'*/';
declare @s nvarchar(max) =  @m + N'select * into #t from dbo.v1;';
exec sp_executesql @s;

select
 @x = p.query_plan
from
 sys.dm_exec_cached_plans cp cross apply
 sys.dm_exec_query_plan(cp.plan_handle) p cross apply
 sys.dm_exec_sql_text(cp.plan_handle) t
where
 t.text like N'%' + @m + N'%'
option
 (recompile);

with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select
 c.ReferencingColumn,
 c.ReferencedDatabase,
 c.ReferencedSchema,
 c.ReferencedTable,
 c.ReferencedColumn,
 e.ReferencedExpression
from
 @x.nodes('//RelOp[@PhysicalOp="Table Insert" and not(empty(.//Object[@Table="[#t]"]))]') a(n) cross apply
 a.n.nodes('//Assign') b(n) cross apply
 (
  select
   b.n.value('ColumnReference[1]/@Column', 'sysname'),
   b.n.value('(ScalarOperator/Identifier/ColumnReference)[1]/@Database', 'sysname'),
   b.n.value('(ScalarOperator/Identifier/ColumnReference)[1]/@Schema', 'sysname'),
   b.n.value('(ScalarOperator/Identifier/ColumnReference)[1]/@Table', 'sysname'),
   b.n.value('(ScalarOperator/Identifier/ColumnReference)[1]/@Column', 'sysname')
 ) c(ReferencingColumn, ReferencedDatabase, ReferencedSchema, ReferencedTable, ReferencedColumn) outer apply
 (
  select
   d.n.value('@ScalarString', 'nvarchar(max)')
  from
   a.n.nodes('//DefinedValue[not(empty(ColumnReference/@Column=sql:column("c.ReferencedColumn")))]/ScalarOperator') d(n)
  where
   c.ReferencedTable is null
 ) e(ReferencedExpression);
go

drop view dbo.v1;
drop table dbo.t1, dbo.t2;
go
Кому интересно, может самостоятельно допилить до универсального варианта:
23 янв 17, 16:50    [20136228]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
лолл
Member

Откуда:
Сообщений: 450
invm,

действительно, лажа... нет связи между [referenced_minor_id], куда пишется индекс поля таблицы, и [referencing_minor_id], куда почему-то не пишется индекс поля представления... сопоставить нельзя даже с привязкой к схеме...
23 янв 17, 16:51    [20136242]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
Нужно заменить
declare @s nvarchar(max) =  @m + N'select * into #t from dbo.v1;';
на
declare @s nvarchar(max) =  @m + N'select top (0) * into #t from dbo.v1;';
23 янв 17, 18:08    [20136729]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
Opus Magnum
Member

Откуда: 77 Rus
Сообщений: 2205
Спасибо большое за предложенные варианты, завтра утром попробую все у себя (уже еду домой).

Пока что могу сказать, что
select * from sys.dm_sql_referenced_entities('dbo.TestView', 'OBJECT')

выдает пустоту (да, я не умею пользоваться, даже гуглеж не помог понять, что я делаю не так).

Вычисляемых полей не предвидится, да и, если надо будет, рассчитаем еще раз.
23 янв 17, 19:28    [20137023]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
лолл
Member

Откуда:
Сообщений: 450
Opus Magnum,

Вариант с раскручиванием плана рабочий, хотя, конечно, решение по сути от безысходности. Неплохо было бы понять, почему sys.dm_sql_referenced_entities не содержит связей между полями таблиц и полями представлений... Может у кого-то есть комментарии на эту тему?
24 янв 17, 09:54    [20138274]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
лолл
Opus Magnum,

Вариант с раскручиванием плана рабочий, хотя, конечно, решение по сути от безысходности. Неплохо было бы понять, почему sys.dm_sql_referenced_entities не содержит связей между полями таблиц и полями представлений... Может у кого-то есть комментарии на эту тему?

если вы придумаете зачем это надо серверу, обязательно напишите
24 янв 17, 10:09    [20138348]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
Opus Magnum
Member

Откуда: 77 Rus
Сообщений: 2205
лолл,

а может так быть из-за отсутствия прав?
В виду того, что схема создавалась не моим подразделением, с правами может быть сложно.
24 янв 17, 10:09    [20138350]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
лолл
Member

Откуда:
Сообщений: 450
TaPaK,

У сервера есть вся эта информация, часть которой попадает в план, по которому invm предложил свое решение.
Просто он не делится ею с нами по каким-то своим причинам.
24 янв 17, 10:25    [20138434]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
лолл
TaPaK,

У сервера есть вся эта информация, часть которой попадает в план, по которому invm предложил свое решение.
Просто он не делится ею с нами по каким-то своим причинам.

перейдём к изучению как строится план?
24 янв 17, 10:28    [20138445]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
лолл
Member

Откуда:
Сообщений: 450
TaPaK,

продолжайте, чего ж вы остановились :)
24 янв 17, 10:33    [20138467]     Ответить | Цитировать Сообщить модератору
 Re: Как получить список полей во вьюшке с псевдонимами?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
лолл
TaPaK,

продолжайте, чего ж вы остановились :)
оно мне надо?
24 янв 17, 10:35    [20138474]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить