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

Откуда:
Сообщений: 116
Задача следующая: получить таблицу индексов, полей которые в него входят, имя таблицы этого индекса. Т.е. результат должен быть следующий: ИмяИндекса, ИмяПоля, ИмяТаблицы.
Написал следующий запрос:
select
	sindexes.name as indexname 
	,systables.name as tablename
	,sys_columns.name as columnname
from sys.index_columns as indexcolumns
left join sys.columns as sys_columns on sys_columns.column_id = indexcolumns.column_id
left join sys.tables as systables on systables.object_id = indexcolumns.object_id
left join sys.indexes as sindexes on sindexes.index_id = indexcolumns.index_id

Но он мне вывел все, что только можно, т.е. поля, не входящие в индекс, а мне нужны только поля, из которых состоит индекс.
Как это сделать?
20 сен 15, 01:48    [18171495]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
зачем тут внешнее объединение?
20 сен 15, 07:44    [18171625]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
Сруль.
Member

Откуда:
Сообщений: 121
Ваша задача далека от тривиальной.
Есть ещё поля типа INCLUDE, о них Вам тоже что-то хочется знать?
Теперь послушайте старого еврея.
Мне как-то была задача поменять колейшены и не в одной а сразу в восьми, для начала.
Вобщем так, поля, занятые в индексах комманда ALTER
не меняет. Встал вопрос сбить все индексы, а потом поставить их в зад.
Куда было деться, у меня детки, им надо кушать,
Я делал диссертацию, как ставить индексы в зад.
Похоже на то, что Вам надо.
Primary Key моя хулиганка превратила в UNIQUE CLUSTERED, но в тот момент это никого не обидело.
Потом появилась леди, которая потребовала, чтобы всё стало, как было и пришлось делать заплатку но она вам щас не нужна, как и мне, тогда, по крайней мере пока.
Вот такой простой скрипт.
Переведите выход в текстовый режим
и ничего работать не будет, т.к.нужно опредлить ещё 2 функции, но это потом.
select [dbo].index_create_script_function(I.id,I.name)+char(10)+'go'+char(10) 
from sysindexes I,sysobjects O

where isnull([dbo].index_create_script_function(I.id,I.name),'')<>'' and
  I.id=O.id
  and O.type='U' 
20 сен 15, 10:10    [18171765]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
Сруль.
Member

Откуда:
Сообщений: 121
Вот Вам одна функция.

К сообщению приложен файл (f_include.sql - 732bytes) cкачать
20 сен 15, 10:12    [18171768]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
Сруль.
Member

Откуда:
Сообщений: 121
Вот вторая.

К сообщению приложен файл (index_create_script_function.sql - 10Kb) cкачать
20 сен 15, 10:13    [18171770]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
Сруль.
Member

Откуда:
Сообщений: 121
А теперь, как Микеланджело, уберите лишнее.
20 сен 15, 10:14    [18171772]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
o-o
Guest
Все давно написано:
sp_helpindex by Kimberly Tripp
20 сен 15, 12:03    [18171923]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
дайте и я свою гармошку растяну... [||||||||////\\\\\||||]

+ наджойнЕл

select
	quotename(s.name) as sch_name, 
	quotename(o.name) as obj_name, 
	quotename(i.name) as idx_name,
	i.type_desc as idx_type_desc,
	i.is_hypothetical,
	i.is_disabled,
	i.is_unique,
	i.is_primary_key,
	i.is_unique_constraint,
	i.[ignore_dup_key],
	stuff
	(
		(
			select N', ' + quotename(c.name) + N' ' + iif(ic.is_descending_key = 1, N'DESC', N'ASC')
			from
				sys.columns c
			where
				c.[object_id] = ic.[object_id]
				and c.column_id = ic.column_id
				and ic.is_included_column = 0
			order by
				ic.key_ordinal
			for xml path(N'')
		), 1, 1, N''
	)  collate Latin1_General_CI_AS_KS_WS  as key_cols,
	stuff
	(
		(
			select N', ' + quotename(c.name)
			from
				sys.columns c
			where
				c.[object_id] = ic.[object_id]
				and c.column_id = ic.column_id
				and ic.is_included_column = 1
			order by
				ic.key_ordinal
			for xml path('')
		), 1, 1, N''
	) as inc_cols,
	i.filter_definition,
	xi.xml_index_type_description ,
	xi.secondary_type_desc,
	si.spatial_index_type_desc,
	si.tessellation_scheme,
	ds.name as data_space_name,
	ds.type_desc as data_space_type_desc,
	i.[allow_page_locks],
	i.[allow_row_locks],
	i.fill_factor,
	i.is_padded
from
	sys.schemas s
		inner join
	sys.tables o on s.schema_id = o.schema_id
		inner join
	sys.indexes i on o.object_id = i.object_id
		inner join
	sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
		left join
	sys.xml_indexes xi on i.object_id = xi.object_id and i.index_id = xi.index_id
		left join
	sys.spatial_indexes si on i.object_id = si.object_id and i.index_id = si.index_id
		inner join
	sys.data_spaces ds on i.data_space_id = ds.data_space_id
order by
	sch_name,
	obj_name,
	idx_type_desc,
	idx_name

20 сен 15, 12:48    [18172011]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+ поправлено

select
	quotename(s.name) as sch_name, 
	quotename(o.name) as obj_name, 
	quotename(i.name) as idx_name,
	i.type_desc as idx_type_desc,
	i.is_hypothetical,
	i.is_disabled,
	i.is_unique,
	i.is_primary_key,
	i.is_unique_constraint,
	i.[ignore_dup_key],
	stuff
	(
		(
			select N', ' + quotename(c.name) + N' ' + iif(ic.is_descending_key = 1, N'DESC', N'ASC')
			from
				sys.index_columns ic
					inner join
				sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
			where
				ic.object_id = i.object_id
				and ic.index_id = i.index_id
				and ic.is_included_column = 0
			order by
				ic.key_ordinal
			for xml path('')
		), 1, 1, N''
	)  as key_cols,
	stuff
	(
		(
			select N', ' + quotename(c.name)
			from
				sys.index_columns ic
					inner join
				sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
			where
				ic.object_id = i.object_id
				and ic.index_id = i.index_id
				and ic.is_included_column = 1
			order by
				ic.key_ordinal
			for xml path('')
		), 1, 1, N''
	) as inc_cols,
	i.filter_definition,
	xi.xml_index_type_description ,
	xi.secondary_type_desc,
	si.spatial_index_type_desc,
	si.tessellation_scheme,
	ds.name as data_space_name,
	ds.type_desc as data_space_type_desc,
	i.[allow_page_locks],
	i.[allow_row_locks],
	i.fill_factor,
	i.is_padded
from
	sys.schemas s
		inner join
	sys.tables o on s.schema_id = o.schema_id
		inner join
	sys.indexes i on o.object_id = i.object_id
		left join
	sys.xml_indexes xi on i.object_id = xi.object_id and i.index_id = xi.index_id
		left join
	sys.spatial_indexes si on i.object_id = si.object_id and i.index_id = si.index_id
		inner join
	sys.data_spaces ds on i.data_space_id = ds.data_space_id
order by
	sch_name,
	obj_name,
	idx_type_desc,
	idx_name

20 сен 15, 13:22    [18172074]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
okwell5
Member

Откуда:
Сообщений: 116
churupaha,
Сообщение 102, уровень 15, состояние 1, строка 17
Неправильный синтаксис около конструкции "=".
Сообщение 156, уровень 15, состояние 1, строка 43
Неправильный синтаксис около ключевого слова "order".
21 сен 15, 12:20    [18174562]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
okwell5
Member

Откуда:
Сообщений: 116
Сруль., у вас процедура с выполнением действий. Я забыл уточнить: беда еще в том, что мой результат нужно получить одним запросом; увы, специфика 1С-ки такова, что в ее возможностях получить данные напрямую из SQL можно только одним запросом.
21 сен 15, 12:28    [18174602]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
o-o
Guest
okwell5
churupaha,
Сообщение 102, уровень 15, состояние 1, строка 17
Неправильный синтаксис около конструкции "=".
Сообщение 156, уровень 15, состояние 1, строка 43
Неправильный синтаксис около ключевого слова "order".

ну так версию своего сервера писать надо.
iif появился, начиная с 2012
21 сен 15, 12:32    [18174635]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
o-o
Guest
вот чурюпахино для 2008:
+
select
	quotename(s.name) as sch_name, 
	quotename(o.name) as obj_name, 
	quotename(i.name) as idx_name,
	i.type_desc as idx_type_desc,
	i.is_hypothetical,
	i.is_disabled,
	i.is_unique,
	i.is_primary_key,
	i.is_unique_constraint,
	i.[ignore_dup_key],
	stuff
	(
		(
			select N', ' + quotename(c.name) + N' ' + case when ic.is_descending_key = 1 then N'DESC' else N'ASC' end
			from
				sys.index_columns ic
					inner join
				sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
			where
				ic.object_id = i.object_id
				and ic.index_id = i.index_id
				and ic.is_included_column = 0
			order by
				ic.key_ordinal
			for xml path('')
		), 1, 1, N''
	)  as key_cols,
	stuff
	(
		(
			select N', ' + quotename(c.name)
			from
				sys.index_columns ic
					inner join
				sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
			where
				ic.object_id = i.object_id
				and ic.index_id = i.index_id
				and ic.is_included_column = 1
			order by
				ic.key_ordinal
			for xml path('')
		), 1, 1, N''
	) as inc_cols,
	i.filter_definition,
	--xi.xml_index_type_description ,
	xi.secondary_type_desc,
	si.spatial_index_type_desc,
	si.tessellation_scheme,
	ds.name as data_space_name,
	ds.type_desc as data_space_type_desc,
	i.[allow_page_locks],
	i.[allow_row_locks],
	i.fill_factor,
	i.is_padded
from
	sys.schemas s
		inner join
	sys.tables o on s.schema_id = o.schema_id
		inner join
	sys.indexes i on o.object_id = i.object_id
		left join
	sys.xml_indexes xi on i.object_id = xi.object_id and i.index_id = xi.index_id
		left join
	sys.spatial_indexes si on i.object_id = si.object_id and i.index_id = si.index_id
		inner join
	sys.data_spaces ds on i.data_space_id = ds.data_space_id
order by
	sch_name,
	obj_name,
	idx_type_desc,
	idx_name
21 сен 15, 12:42    [18174710]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
okwell5
Member

Откуда:
Сообщений: 116
o-o, конечно поля индекса не в отдельных строках, а в одной колонке через запятую, но так тоже здорово работает! Спасибо за код, он решает задачу. Еще одно важное решение появилось на SQL.RU.
21 сен 15, 13:09    [18174875]     Ответить | Цитировать Сообщить модератору
 Re: Запрос получения индексов с входящими в них полями  [new]
Glory
Member

Откуда:
Сообщений: 104751
okwell5
Еще одно важное решение появилось на SQL.RU.

Оно появилось несколько лет назад
21 сен 15, 13:10    [18174883]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить