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

Откуда:
Сообщений: 1077
Всем добрый день.

Вылезла необходимость заскриптовать по куче таблиц ключи и индексы. С целью убиения и последующего пересоздания. Если у кого-то есть скриптики, поделитесь, плиз. Очень не хочется изобретать велосипед. Боюсь что-нить упустить... Делал нечто подобное несколько лет назад, но - увы - не сохранилось...
7 ноя 11, 19:30    [11560289]     Ответить | Цитировать Сообщить модератору
 Re: Заскриптовать индесы и ключи  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550


К сообщению приложен файл. Размер - 63Kb
7 ноя 11, 19:37    [11560323]     Ответить | Цитировать Сообщить модератору
 Re: Заскриптовать индесы и ключи  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Паганель
...

К сожалению, там не удается вывести только ключи и индексы без create table. Причем и описание Primary Key в ряде случаев идет в разделе create table, а не отдельно. А стоит снять галку со 'Script Create' - не выводит ничего.

Распарсивать полученную простыню, пожалуй, даже проблематичнее, чем надергать самому информацию из системных вьюшек. :)

ЗЫ. 2005-й.
7 ноя 11, 20:19    [11560447]     Ответить | Цитировать Сообщить модератору
 Re: Заскриптовать индесы и ключи  [new]
invm
Member

Откуда: Москва
Сообщений: 9823
Делал такое давно. Не оптимально и без претензий на универсальность, но можно допилить под свои нужды:
+
if not exists(select 1 from sys.schemas where name = N'tools')
 exec('create schema tools authorization dbo')
go
if not exists(select 1 from sys.schemas where name = N'schema_utils')
 exec('create schema schema_utils authorization dbo')
go

set ansi_nulls on
go
set quoted_identifier on
go

if object_id('tools.fnWT_GetQuotedObjectName', 'FN') is not null
 drop function tools.fnWT_GetQuotedObjectName
go 
create function tools.fnWT_GetQuotedObjectName
	(
    	@object_id int
	)
returns sysname
as
begin
 declare @result sysname
 
 select
  @result =
   case
    when objectproperty(@object_id, 'IsConstraint') = 1 then N''
    else quotename(object_schema_name(@object_id)) + N'.'
   end +
   quotename(object_name(@object_id))
           
 return @result
end
go

if object_id('schema_utils.fnSUTIL_InternalGetIndexOptionsList', 'FN') is not null
 drop function schema_utils.fnSUTIL_InternalGetIndexOptionsList
go 
create function schema_utils.fnSUTIL_InternalGetIndexOptionsList
	(
    	@object_id int,
        @index_id int
	)
returns nvarchar(max)
as
begin
 declare @result nvarchar(max)
 
 select
  @result = 
   case when si.fill_factor > 0 then N'fillfactor = ' + cast(si.fill_factor as nvarchar(10)) + N', ' else N'' end +
   N'pad_index = ' + case when si.is_padded = 1 then N'on' else N'off' end + 
   N', ignore_dup_key = ' + case when si.[ignore_dup_key] = 1 then N'on' else N'off' end + 
   N', allow_row_locks = ' + case when si.[allow_row_locks] = 1 then N'on' else N'off' end + 
   N', allow_page_locks = ' + case when si.[allow_page_locks] = 1 then N'on' else N'off' end + 
   case when st.no_recompute is not null then
     N', statistics_norecompute = ' + case when st.no_recompute = 1 then N'on' else N'off' end
   else
    N''
   end
 from
  sys.indexes si join
  sys.objects so on so.object_id = si.object_id join
  sys.filegroups fg on fg.data_space_id = si.data_space_id left join
  sys.stats st on st.object_id = si.object_id and st.stats_id = si.index_id
 where
  si.object_id = @object_id and
  si.index_id = @index_id and
  si.[type] in (1/*clustered*/, 2/*nonclustered*/)

 return @result
end
go

if object_id('schema_utils.fnSUTIL_InternalGetIndexDataSpace', 'FN') is not null
 drop function schema_utils.fnSUTIL_InternalGetIndexDataSpace
go 
create function schema_utils.fnSUTIL_InternalGetIndexDataSpace
	(
    	@object_id int,
        @index_id int
	)
returns nvarchar(max)
as
begin
 declare @result nvarchar(max)
 
 select
  @result = quotename(fg.[name])
 from
  sys.indexes si join
  sys.objects so on so.object_id = si.object_id join
  sys.filegroups fg on fg.data_space_id = si.data_space_id
 where
  si.object_id = @object_id and
  si.index_id = @index_id and
  si.[type] in (1/*clustered*/, 2/*nonclustered*/)

 return @result 
end
go

if object_id('schema_utils.fnSUTIL_InternalGetIndexColumnsList', 'FN') is not null
 drop function schema_utils.fnSUTIL_InternalGetIndexColumnsList
go 
create function schema_utils.fnSUTIL_InternalGetIndexColumnsList
	(
		@object_id int,
    	@index_id int,
    	@IncludedColumns tinyint = 0
	)
returns nvarchar(max)
as
begin
 declare @result nvarchar(max)
 
 select
  @result =  
   (
    select
     '"' + quotename(sc.name) +
     case when @IncludedColumns = 0 and ic.is_descending_key = 1 then N' desc' else N'' end +
     '"' as 'data()'
    from
     sys.index_columns ic join
     sys.indexes si on si.object_id = ic.object_id and si.index_id = ic.index_id join
     sys.columns sc on sc.object_id = ic.object_id and sc.column_id = ic.column_id
    where
     ic.object_id = @object_id and
     ic.index_id = @index_id and
     si.[type] in (1/*clustered*/, 2/*nonclustered*/) and
     ic.is_included_column = @IncludedColumns
    order by
     case when @IncludedColumns = 0 then ic.key_ordinal else ic.index_column_id end
    for xml path ('')
   )
  
 select
  @result = replace(replace(@result, '" "', ', '), '"', '')
 
 return @result
end
go

if object_id('schema_utils.fnSUTIL_GetShemaBoundedDependentObjects', 'TF') is not null
 drop function schema_utils.fnSUTIL_GetShemaBoundedDependentObjects
go 
create function schema_utils.fnSUTIL_GetShemaBoundedDependentObjects
	(
    	@object_id int
	)
returns @dependend_objects table ([object_id] int primary key, dep_Level int)
as
begin
 ;with dependent_objects ([object_id], dep_Level)
 as
 (
  select
   sd.[object_id], 1 as dep_Level
  from
   sys.sql_dependencies sd
  where
   sd.referenced_major_id = @object_id and
   sd.referenced_minor_id = 0 and
   sd.class = 1 /*объект или столбец (ссылки, связанные со схемами)*/
 
  union all
  
  select
   sd.[object_id], t.dep_Level + 1 as dep_Level
  from
   dependent_objects t join
   sys.sql_dependencies sd on sd.referenced_major_id = t.[object_id]
  where
   sd.referenced_minor_id = 0 and
   sd.class = 1 /*объект или столбец (ссылки, связанные со схемами)*/
 )
 insert into @dependend_objects
  ([object_id], dep_Level)
  select
   [object_id], dep_Level
  from
   dependent_objects
 
 return
end
go

if object_id('schema_utils.fnSUTIL_InternalGetReferentialConstraintCreateScript', 'FN') is not null
 drop function schema_utils.fnSUTIL_InternalGetReferentialConstraintCreateScript
go 
create function schema_utils.fnSUTIL_InternalGetReferentialConstraintCreateScript
	(
		@object_id int,
        @MandatoryNotForReplication tinyint = 0
	)
returns nvarchar(max)
as
begin

 declare @result nvarchar(max), @pcl nvarchar(max), @rcl nvarchar(max)

 select
  @pcl =  
   (
    select
     quotename(c.name) as 'data()'
    from
     sys.foreign_keys fk join
     sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id join
     sys.columns c on c.object_id = fkc.parent_object_id and c.column_id = fkc.parent_column_id
    where
     fk.object_id = @object_id
    order by
     fkc.constraint_column_id
    for xml path ('')
   )
  
 select
  @pcl = replace(@pcl, '] [', '], [')
 
 select
  @rcl =  
   (
    select
     quotename(c.name) as 'data()'
    from
     sys.foreign_keys fk join
     sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id join
     sys.columns c on c.object_id = fkc.referenced_object_id and c.column_id = fkc.referenced_column_id
    where
     fk.object_id = @object_id
    order by
     fkc.constraint_column_id
    for xml path ('')
   )
  
 select
  @rcl = replace(@rcl, '] [', '], [')

 select
  @result =
    N'alter table ' + tools.fnWT_GetQuotedObjectName(p.object_id) + 
    N' with nocheck add constraint ' + tools.fnWT_GetQuotedObjectName(fk.object_id) + 
    N' foreign key (' + @pcl + N')' +
    N' references ' + tools.fnWT_GetQuotedObjectName(r.object_id) + N' (' + @rcl + N')' +
    case when objectproperty(@object_id, 'CnstIsDeleteCascade') = 0 then N'' else N' on delete cascade' end + 
    case when objectproperty(@object_id, 'CnstIsUpdateCascade') = 0 then N'' else N' on update cascade' end + 
    case when objectproperty(@object_id, 'CnstIsNotRepl') = 1 or @MandatoryNotForReplication = 1 then N' not for replication' else N'' end
 from
  sys.foreign_keys fk join
  sys.objects p on p.object_id = fk.parent_object_id join
  sys.objects r on r.object_id = fk.referenced_object_id
 where
  fk.object_id = @object_id

 return @result
end
go

if object_id('schema_utils.fnSUTIL_GetObjectPermissionsScript', 'FN') is not null
 drop function schema_utils.fnSUTIL_GetObjectPermissionsScript
go 
create function schema_utils.fnSUTIL_GetObjectPermissionsScript
	(
    	@object_id int
	)
returns nvarchar(max)
as
begin

 declare @result nvarchar(max), @object_name sysname
 
 select
  @object_name = tools.fnWT_GetQuotedObjectName(@object_id)
 
 select
  @result =  
   (
    select
     case
      when pm.[state] in ('G', 'W') then N'grant ' + lower(pm.permission_name) + N' on object::' + @object_name + N' to ' + quotename(pr.[name]) +
                                         case when pm.[state] = 'W' then N' with grant option' else N'' end
      when pm.[state] = 'D' then N'deny ' + lower(pm.permission_name) + N' on object::' + @object_name + N' to ' + quotename(pr.[name])
      when pm.[state] = 'R' then N'revoke ' + lower(pm.permission_name) + N' on object::' + @object_name + N' from ' + quotename(pr.[name])
     end + N';' as 'data()'
    from
     sys.database_permissions pm join
     sys.database_principals pr on pr.principal_id = pm.grantee_principal_id
    where
     pm.[class] = 1 /*object*/ and
     pm.major_id = @object_id
    for xml path ('')
   )

 return @result 
end
go

if object_id('schema_utils.fnSUTIL_GetKeyConstraintDropScript', 'FN') is not null
 drop function schema_utils.fnSUTIL_GetKeyConstraintDropScript
go 
create function schema_utils.fnSUTIL_GetKeyConstraintDropScript
	(
		@object_id int
	)
returns nvarchar(max)
as
begin
 declare @result nvarchar(max)
  
 select
  @result =
    'alter table ' + tools.fnWT_GetQuotedObjectName(so.parent_object_id) + 
    ' drop constraint ' + tools.fnWT_GetQuotedObjectName(so.object_id)
 from
  sys.objects so
 where
  so.object_id = @object_id and so.type in ('PK', 'UQ')
  
 return @result
end
go

if object_id('schema_utils.fnSUTIL_GetKeyConstraintCreateScript', 'FN') is not null
 drop function schema_utils.fnSUTIL_GetKeyConstraintCreateScript
go 
create function schema_utils.fnSUTIL_GetKeyConstraintCreateScript
	(
		@object_id int
	)
returns nvarchar(max)
as
begin
 declare @result nvarchar(max)
 
 select
  @result =
    N'alter table ' + 
    tools.fnWT_GetQuotedObjectName(so.parent_object_id) +
    N' with nocheck add constraint ' + tools.fnWT_GetQuotedObjectName(so.object_id) +
    case so.type when 'PK' then N' primary key' when 'UQ' then N' unique' end +
    case when objectproperty(so.object_id, 'CnstIsClustKey') = 1 then N' clustered' else N' nonclustered' end +
    ' (' + schema_utils.fnSUTIL_InternalGetIndexColumnsList (so.parent_object_id, si.index_id, 0) + ')' +
    N' with (' + schema_utils.fnSUTIL_InternalGetIndexOptionsList(so.parent_object_id, si.index_id) + N')' +
    N' on ' + schema_utils.fnSUTIL_InternalGetIndexDataSpace(so.parent_object_id, si.index_id) + N';'
 from
  sys.objects so join
  sys.indexes si on si.object_id = so.parent_object_id and si.name = so.[name] join
  sys.filegroups sfg on sfg.data_space_id = si.data_space_id
 where
  so.object_id = @object_id and so.type in ('PK', 'UQ')
  
 return @result
end
go

if object_id('schema_utils.fnSUTIL_GetIndexCreateScript', 'FN') is not null
 drop function schema_utils.fnSUTIL_GetIndexCreateScript
go 
create function schema_utils.fnSUTIL_GetIndexCreateScript
	(
    	@object_id int,
        @index_id int
	)
returns nvarchar(max)
as
begin
 declare @result nvarchar(max)
  
 select
  @result = N'create ' +
  case when si.is_unique = 1 then N'unique ' else N'' end +
  case when si.[type] = 1/*clustered*/ then N'clustered ' else N'' end +
  N'index ' +
  quotename(si.[name]) + ' on ' + tools.fnWT_GetQuotedObjectName(so.object_id) +
  N' (' + schema_utils.fnSUTIL_InternalGetIndexColumnsList(si.object_id, si.index_id, 0) + N')' +
  isnull(N' include (' + schema_utils.fnSUTIL_InternalGetIndexColumnsList(si.object_id, si.index_id, 1) + N')', N'') +
  case when si.has_filter = 1 then N' where ' + si.filter_definition else N'' end +
  N' with (' + schema_utils.fnSUTIL_InternalGetIndexOptionsList(si.object_id, si.index_id) + N')' +
  N' on ' + schema_utils.fnSUTIL_InternalGetIndexDataSpace(si.object_id, si.index_id) + N';' +
  case when si.is_disabled = 1 then
   N' alter index ' + 
   quotename(si.[name]) + ' on ' + tools.fnWT_GetQuotedObjectName(so.object_id) +
   N' disable;'
  else
   N''
  end
 from
  sys.indexes si join
  sys.objects so on so.object_id = si.object_id join
  sys.filegroups fg on fg.data_space_id = si.data_space_id left join
  sys.stats st on st.object_id = si.object_id and st.stats_id = si.index_id
 where
  si.object_id = @object_id and
  si.index_id = @index_id and
  si.[type] in (1/*clustered*/, 2/*nonclustered*/)

 return @result 
end
go

if object_id('schema_utils.fnSUTIL_GetFulltextIndexDropScript', 'FN') is not null
 drop function schema_utils.fnSUTIL_GetFulltextIndexDropScript
go 
create function schema_utils.fnSUTIL_GetFulltextIndexDropScript
	(
    	@object_id int
	)
returns nvarchar(max)
as
begin
 
 declare @result nvarchar(max), @object_name sysname
 
 select
  @object_name = tools.fnWT_GetQuotedObjectName(@object_id)
 
 select
  @result = N'drop fulltext index on ' + @object_name
 from
  sys.fulltext_indexes fti
 where
  fti.[object_id] = @object_id
 
 return @result  
end
go

if object_id('schema_utils.fnSUTIL_GetFulltextIndexCreateScript', 'FN') is not null
 drop function schema_utils.fnSUTIL_GetFulltextIndexCreateScript
go 
create function schema_utils.fnSUTIL_GetFulltextIndexCreateScript
	(
    	@object_id int
	)
returns nvarchar(max)
as
begin
 
 declare @result nvarchar(max), @items nvarchar(max), @object_name sysname
 
 select
  @object_name = tools.fnWT_GetQuotedObjectName(@object_id)
 
 select
  @result = N'create fulltext index on ' + @object_name +
            N' key index ' + quotename(si.[name]) + N' on (' +
            quotename(ftc.[name]) + N', filegroup ' + quotename(fg.[name]) + N')' +
            N' with change_tracking = ' + 
            case
             when fti.change_tracking_state = 'A' then N'auto'
             when fti.change_tracking_state = 'M' then N'manual'
             when fti.change_tracking_state = 'O' then N'off'
            end +
            N';' +
            case
             when fti.is_enabled = 0 then N'alter fulltext index on ' + @object_name + N' disable;'
             else N''
            end
 from
  sys.fulltext_indexes fti join
  sys.fulltext_catalogs ftc on ftc.fulltext_catalog_id = fti.fulltext_catalog_id join
  sys.indexes si on si.[object_id] = fti.[object_id] and si.index_id = fti.unique_index_id join
  sys.filegroups fg on fg.data_space_id = fti.data_space_id
 where
  fti.[object_id] = @object_id
 
 select
  @items =
   (
    select
     N'alter fulltext index on ' + @object_name +
     N' add (' + quotename(sc.[name]) +
     case when ftic.type_column_id is not null then N' type column ' + quotename(sc.[name]) else N'' end +
     case when ftic.language_id <> 0 then N' language ' + cast(ftic.language_id as nvarchar(10)) else N'' end +
     N');' as 'data()'
    from
     sys.fulltext_indexes fti join
     sys.fulltext_index_columns ftic on ftic.[object_id] = fti.[object_id] join
     sys.[columns] sc on sc.[object_id] = ftic.[object_id] and sc.column_id = ftic.column_id left join
     sys.[columns] sct on sct.[object_id] = ftic.[object_id] and sct.column_id = ftic.type_column_id
    where
     fti.[object_id] = @object_id
    order by
     ftic.column_id
    for xml path ('')
   )
   
 return @result + isnull(N' ' + @items, N'')  
end
go

if object_id('schema_utils.fnSUTIL_GetCheckConstraintDropScript', 'FN') is not null
 drop function schema_utils.fnSUTIL_GetCheckConstraintDropScript
go 
create function schema_utils.fnSUTIL_GetCheckConstraintDropScript
	(
		@object_id int
	)
returns nvarchar(max)
as
begin
 declare @result nvarchar(max)
 
 select
  @result =
    N'alter table ' + tools.fnWT_GetQuotedObjectName(so.parent_object_id) +
    N' drop constraint ' + tools.fnWT_GetQuotedObjectName(so.object_id)
 from
  sys.objects so
 where
  so.object_id = @object_id and so.type in ('C')
  
 return @result
end
go

if object_id('schema_utils.fnSUTIL_GetCheckConstraintCreateScript', 'FN') is not null
 drop function schema_utils.fnSUTIL_GetCheckConstraintCreateScript
go 
create function schema_utils.fnSUTIL_GetCheckConstraintCreateScript
	(
		@object_id int
	)
returns nvarchar(max)
as
begin
 declare @result nvarchar(max)
 
 select
  @result =
    N'alter table ' + tools.fnWT_GetQuotedObjectName(so.parent_object_id) +
    N' with nocheck add constraint ' + tools.fnWT_GetQuotedObjectName(so.object_id) + 
    N' check' +
    case when objectproperty(so.object_id, 'CnstIsNotRepl') = 1 then N' not for replication' else N'' end +
    N' (' + object_definition(so.object_id) + N')'
 from
  sys.objects so
 where
  so.object_id = @object_id and so.type in ('C')
  
 return @result
end
go

if object_id('schema_utils.fnSUTIL_GetReferentialConstraintDropScript', 'FN') is not null
 drop function schema_utils.fnSUTIL_GetReferentialConstraintDropScript
go 
create function schema_utils.fnSUTIL_GetReferentialConstraintDropScript
	(
		@object_id int
	)
returns nvarchar(max)
as
begin

 declare @result nvarchar(max);

 select
  @result =
    N'alter table ' + tools.fnWT_GetQuotedObjectName(fk.parent_object_id) +
    N' drop constraint ' + tools.fnWT_GetQuotedObjectName(fk.object_id)
 from
  sys.foreign_keys fk
 where
  fk.object_id = @object_id;

 return @result;
end;
go

if object_id('schema_utils.fnSUTIL_GetReferentialConstraintCreateScriptNotForReplication', 'FN') is not null
 drop function schema_utils.fnSUTIL_GetReferentialConstraintCreateScriptNotForReplication
go 
create function schema_utils.fnSUTIL_GetReferentialConstraintCreateScriptNotForReplication
	(
		@object_id int
	)
returns nvarchar(max)
as
begin

 declare @result nvarchar(max)

 select
  @result = schema_utils.fnSUTIL_InternalGetReferentialConstraintCreateScript(@object_id, 1)

 return @result
end
go

if object_id('schema_utils.fnSUTIL_GetReferentialConstraintCreateScript', 'FN') is not null
 drop function schema_utils.fnSUTIL_GetReferentialConstraintCreateScript
go 
create function schema_utils.fnSUTIL_GetReferentialConstraintCreateScript
	(
		@object_id int
	)
returns nvarchar(max)
as
begin

 declare @result nvarchar(max)

 select
  @result = schema_utils.fnSUTIL_InternalGetReferentialConstraintCreateScript(@object_id, 0)

 return @result
end
go
7 ноя 11, 20:46    [11560533]     Ответить | Цитировать Сообщить модератору
 Re: Заскриптовать индесы и ключи  [new]
Anddros
Member

Откуда:
Сообщений: 1077
invm
Делал такое давно. Не оптимально и без претензий на универсальность, но можно допилить под свои нужды:
...


Спасиб! То, что нужно. :)
8 ноя 11, 10:53    [11562030]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить