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

Откуда:
Сообщений: 2083
Имеется такой велосипед
Create procedure dropAllIndex as
Begin
  set nocount on;
  declare @s nvarchar(max);

  raiserror('fulltext index...',0,0) with nowait;
  set @s = char(13) + char(10);
  select @s = @s + 'alter fulltext index on ' + quotename(s.name) +'.'+ quotename(o.name) + ' disable;' + char(13)+char(10)+
  'drop fulltext index on '+ quotename(s.name) +'.'+ quotename(o.name) +';' + char(13)+char(10)
  from sys.fulltext_indexes fti
  inner join sys.objects o on fti.[object_id] = o.[object_id]
  inner join sys.schemas s on o.[schema_id] = s.[schema_id]
  exec (@s);

  raiserror('fulltext catalog...',0,0) with nowait;
  set @s = char(13) + char(10);
  select @s = @s + 'drop fulltext catalog ' + quotename(name) from sysfulltextcatalogs
  exec (@s);

  raiserror('drop constraint 1...',0,0) with nowait;
  set @s = char(13) + char(10);
  select @s = @s + 'alter table ' + quotename(ctu.table_catalog) + '.' + quotename(ctu.table_schema) + '.' + quotename(ctu.Table_Name) +
  ' drop constraint ' + quotename(rc.constraint_name) + ';' + nchar(13) + nchar(10)
  from information_schema.referential_constraints rc
  inner join information_schema.constraint_table_usage ctu
  on rc.constraint_catalog = ctu.constraint_catalog and rc.constraint_schema = ctu.constraint_schema and rc.constraint_name = ctu.constraint_name
  exec(@s);

  raiserror('drop constraint 2...',0,0) with nowait;
  set @s = char(13) + char(10);
  select @s = @s + 'alter table ' + quotename(ctu.table_schema) + '.' + quotename(ctu.Table_Name) +
  ' drop constraint ' + quotename(con.constraint_name) + ';' + nchar(13) + nchar(10)
  from information_schema.table_constraints con
  inner join information_schema.constraint_table_usage ctu
  on con.constraint_catalog = ctu.constraint_catalog and con.constraint_schema = ctu.constraint_schema and con.constraint_name = ctu.constraint_name
  exec (@s);

  raiserror('drop index...',0,0) with nowait;
  set @s = nchar(13) + nchar(10);
  select @s = @s + 'drop index ' + quotename(i.Name) +' on '+ quotename(o.Name) + nchar(13) + nchar(10)
  from sys.indexes i
  inner join sys.objects o on i.[object_id] = o.[object_id]
  where i.Name is not null and o.[type] = 'U'
  exec (@s);
End

На базе, где есть fuultext и прочие сложности, отработал нормально. Хочется универсальный вариант на все случаи жизни. Может чего пропустил?

И еще попутный вопрос. Можно ли эту процедуру создать в база master и выполнять действия в контексте другой базы? Что-то вроде этого:
use Base1;
exec master.dbo.dropAllIndex at Base1;
go

use Base2;
exec master.dbo.dropAllIndex at Base2;

Версия сервера Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)   Mar 26 2015 21:18:04   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 
8 июн 16, 13:10    [19270989]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37202
Осталось понять, зачем это вообще нужно.

Сообщение было отредактировано: 8 июн 16, 13:18
8 июн 16, 13:18    [19271042]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
user89
Member

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

есть весьма сложный проект, индексы надо удалить (это часть техзадания)
А это
use Base1;
exec master.dbo.dropAllIndex at Base1;
go

use Base2;
exec master.dbo.dropAllIndex at Base2;
чисто академический интерес.
8 июн 16, 13:26    [19271080]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
o-o
Guest
user89
Хочется универсальный вариант на все случаи жизни.

чего уж мелочиться, DROP DATABASE
правда, универсальность споткнется на базе master.
а так вряд ли вы еще универсальнее напишете
8 июн 16, 13:34    [19271128]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
invm
Member

Откуда: Москва
Сообщений: 9688
user89
Можно ли эту процедуру создать в база master и выполнять действия в контексте другой базы?
Можно.
8 июн 16, 13:36    [19271142]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
o-o
Guest
invm
user89
Можно ли эту процедуру создать в база master и выполнять действия в контексте другой базы?
Можно.

только это не отменяет выдачу на нее прав в мастере.
т.е. по виду-то она будет системная,
и вызываться в контексте любой базы тоже будет.
но на нее нужен явный EXECUTE,
+ для каждой базы требуется доступ вызывающему, права на дропанье тоже
8 июн 16, 13:43    [19271180]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
user89
Member

Откуда:
Сообщений: 2083
o-o
чего уж мелочиться, DROP DATABASE
Топик про удаление всех индексов.


invm
Можно.
Большое спасибо!
8 июн 16, 14:10    [19271348]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
o-o
Guest
user89
o-o
чего уж мелочиться, DROP DATABASE
Топик про удаление всех индексов.
Большое спасибо![/quot]
но вы же так и не огласили цель.
если это сделать базу not operational, то мой способ гораздо дешевле.
представляю, что будет, если наш 440Гб-ый кластерный кто-то дропнет.
с кучей некластерных поверх.
хотя скорее лог диск переполнит или просто сервер помрет
8 июн 16, 14:16    [19271388]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
user89
Member

Откуда:
Сообщений: 2083
o-o,
в одной базе надо удалить кое-какие столбцы, а индексы не позволяют. Их мало, и процедура dropAllIndex удаляет их за 2 сек, ну и заодно лучше изучил системные представления.

o-o
если это сделать базу not operational, то мой способ гораздо дешевле.
А можно вкратце, что за операции надо сделать? Или ссылку какую-нибудь, а то гуглю, нахожу ерунду какую-то...
8 июн 16, 14:35    [19271498]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31824
user89
А можно вкратце, что за операции надо сделать? Или ссылку какую-нибудь, а то гуглю, нахожу ерунду какую-то...
Нужно удалять те индексы, которые реально мешают выполнению нужных модификаций, а точнее, просто с ними несовместимы. Анализировать каждый индекс персонально.
Или, если есть шаблонная операция, можно в скрипт вставить фильтр по полям - т.е. удалять все индексфы, которые содержат нужные поля (которые вы хотите удалить). Но тоже кластерные не надо, их всё равно нуджно анализировать персонально.

И ещё, как вы индексы менять и восстанавливать будете, вы их заскриптовали?
8 июн 16, 14:56    [19271629]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
o-o
Guest
user89
o-o,
в одной базе надо удалить кое-какие столбцы, а индексы не позволяют. Их мало, и процедура dropAllIndex удаляет их за 2 сек, ну и заодно лучше изучил системные представления.

o-o
если это сделать базу not operational, то мой способ гораздо дешевле.
А можно вкратце, что за операции надо сделать? Или ссылку какую-нибудь, а то гуглю, нахожу ерунду какую-то...

не, ну моя идея в порядке издевательства была.
просто у нас DWH и таблицы огромные.
когда кто-то дропает индексы (даже парочку), встает ВСЕ.
ну т.е. просто все встает и никто данные свои не получает.
я и говорю, чтобы тот же эффект достичь, можно просто все базы грохнуть,
тогда данные тоже никто не получит.
ссылку на это не надо :)
----
но и просто сам процесс дропа кластерного с кучей некластерных на огромной таблице это дорого.
ему ж надо все некластерные перестроить со ссылкой на новую кучу.
поэтому у нас скорее сервер ляжет, чем кто-то осуществит дроп кластерного на всеми любимой таблице.

вы вот, например, сперва констрэйнты дропаете, потом некластерные.
вот я себе и представляю: дроп кластерного, оформленного в виде ПК,
это перелив данных в кучу и перестроение всех некластерных.
а потом еще и некластерные удаляем.
до этого даже не дойдет, он умерет на дропе ПК.

хотя бы с некластерных начните что ли
8 июн 16, 14:57    [19271638]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
user89
Member

Откуда:
Сообщений: 2083
alexeyvg
И ещё, как вы индексы менять и восстанавливать будете, вы их заскриптовали?
Индексы в моем случае не нужны :)   Делается копия базы (изучил как xp_cmdshell под другой учетной записью, граблей много), потом все необходимые операции.
А так да, согласен. Надо удалять только те индексы, от которых зависят удаляемые столбцы.
8 июн 16, 15:15    [19271736]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
o-o
Guest
user89
alexeyvg
И ещё, как вы индексы менять и восстанавливать будете, вы их заскриптовали?
Индексы в моем случае не нужны :)  

у нас те, кототрые невзначай дропают, или переносят таблицу без индексов,
тоже считают, что в их случае "не нужны".
один деятель у нас как-то дропнул вьюху на таблицу в соседней базе,
ибо хотел расширить саму таблицу под себя.
ну и скопировал ее в нашу базу с именем, к-ое было у вьюхи.
без индексов, разумеется, ибо студией сгенерил скрипт,
а она по умолчанию ПК скриптует, а другие индексы нет.
все встало. народ фигел и недоумевал.
у меня вообще-то висит DDL-триггер, но в нем дропов индексов в тот день не было.
а вот как хитро можно всех оставить без индексов. пойди найди, кто и где нагадил
user89
Делается копия базы (изучил как xp_cmdshell под другой учетной записью, граблей много), потом все необходимые операции.

зачем для копии базы xp_cmdshell?
не бэкапите что ли?
8 июн 16, 15:52    [19271926]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4823
Есть такой давно забытый, наверное, финт

USE MASTER
GO

EXEC tempdb.dbo.sp_executesql N'SELECT DB_NAME() -- ваш запрос в контексте другой базы'
8 июн 16, 15:58    [19271951]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
user89
Member

Откуда:
Сообщений: 2083
o-o,

бэкапы есть всегда
Надо скопировать файл .bak на сервер для подрядчика. Подрядчик наш сервер не видит.
Файл .bak "заталкивается" на их сервер и потом запускаем exec('restore database...') at серверПодрядчика
Далее drop файл .bak
Потом нужные операции с этой базой.
8 июн 16, 16:05    [19271972]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
o-o
Guest
a_voronin
Есть такой давно забытый, наверное, финт

USE MASTER
GO

EXEC tempdb.dbo.sp_executesql N'SELECT DB_NAME() -- ваш запрос в контексте другой базы'

ага, очень к месту.
его процедура шарится в системных таблицах с индексами.
и в каждой базе данные там только об индексах той самой базы.
ему теперь что, каждый стейтмент процедуры в динамику запаковать?
у него и без того дропы внутри динамические,
но динамика в динамике это экстримнее, да?
---------------
пометил системной, вызвал в нужной базе, и все работает.
и читаемо.
только права никто не отменял, а так нормальное решение
8 июн 16, 16:18    [19272036]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
Mr. X
Guest
user89,

а задизейблить индексы не вариант?
8 июн 16, 23:25    [19273858]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
o-o
Guest
Mr. X
user89,

а задизейблить индексы не вариант?

А вы пробовали удалить столбец, явлюящийся частью индекса, даже задизэйбленного?
Мне даже ясно, почему ошибка все та же: дизэйблить имеет смысл, если потом обратно энейблить, а столбец уже все равно удален, и индекс с его участием это теперь просто выброшенное место на диске
9 июн 16, 08:08    [19274178]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
Mr. X
Guest
o-o,

Дело было вечером, читал по диагонали, про удаление столбцов пропустил.
ТС: зачем удалять столбцы? Скрыть от подрядчика данные?
9 июн 16, 10:23    [19274574]     Ответить | Цитировать Сообщить модератору
 Re: Удалить все индексы в базе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31824
user89
alexeyvg
И ещё, как вы индексы менять и восстанавливать будете, вы их заскриптовали?
Индексы в моем случае не нужны :)
Ок. только всё же не забывайте про кластерный инедкс - он не просто инедкс, но и сама таблица.
Удаление этого индекса приведёт как минимум к большой нагрузке.
Ну и вообще бессмысленная операция, не вижу, зачем это нужно для вашей задачи.
9 июн 16, 18:47    [19277669]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить