Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 генерация таблиц  [new]
андр2233
Guest
Есть бд с несколькими сотнями таблиц
Надо для каждой таблицы из этой бд сделать клон с постфиксом в названии и несколькими дополнительными полями
Постфикс и дополнительные поля - одинаковые для всех таблиц
Как это сделать не руками?
20 мар 12, 15:32    [12281983]     Ответить | Цитировать Сообщить модератору
 Re: генерация таблиц  [new]
ybill
Member

Откуда:
Сообщений: 109
андр2233,
Написать скрипт
20 мар 12, 16:01    [12282287]     Ответить | Цитировать Сообщить модератору
 Re: генерация таблиц  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
SSMS script table as create + SQL Profiler отловить и доработать скрипты на создание таблицы,индексов,внешних ключей
А так просто для примера
+

declare @aadfields varchar(max) = 'add_fild1 varchar(11),add_fild2 varchar(12)';
declare @sufix varchar(50)='_zzz';
with  zz
        as ( select schema_name(tbl.schema_id) as [Table_Schema]
                  , tbl.name as [Table_Name]
                  , clmns.column_id as [ID]
                  , clmns.name as [Name]
                  , clmns.is_ansi_padded as [AnsiPaddingStatus]
                  , clmns.is_computed as [Computed]
                  , isnull(cc.definition, N'''') as [ComputedText]
                  , usrt.name as [DataType]
                  , isnull(baset.name, N'''') as [SystemType]
                  , sclmns.name as [DataTypeSchema]
                  , cast(clmns.is_rowguidcol as bit) as [RowGuidCol]
                  , cast(case when baset.name in ( 'nchar', 'nvarchar' )
                                   and clmns.max_length <> -1 then clmns.max_length / 2
                              else clmns.max_length
                         end as int) as [Length]
                  , cast(clmns.precision as int) as [NumericPrecision]
                  , clmns.is_identity as [Identity]
                  , cast(isnull(ic.seed_value, 0) as bigint) as [IdentitySeed]
                  , cast(isnull(ic.increment_value, 0) as bigint) as [IdentityIncrement]
                  , isnull(clmns.collation_name, N'') as [Collation]
                  , cast(clmns.scale as int) as [NumericScale]
                  , clmns.is_nullable as [Nullable]
                  , cast(clmns.is_filestream as bit) as [IsFileStream]
                  , isnull(ic.is_not_for_replication, 0) as [NotForReplication]
                  , ( case when clmns.default_object_id = 0 then N''''
                           when d.parent_object_id > 0 then N''''
                           else d.name
                      end ) as [Default]
                  , ( case when clmns.default_object_id = 0 then N''''
                           when d.parent_object_id > 0 then N''''
                           else schema_name(d.schema_id)
                      end ) as [DefaultSchema]
                  , ( case when clmns.rule_object_id = 0 then N''''
                           else r.name
                      end ) as [Rule]
                  , ( case when clmns.rule_object_id = 0 then N''''
                           else schema_name(r.schema_id)
                      end ) as [RuleSchema]
                  , isnull(xscclmns.name, N'''') as [XmlSchemaNamespace]
                  , isnull(s2clmns.name, N'''') as [XmlSchemaNamespaceSchema]
                  , isnull(( case clmns.is_xml_document
                               when 1 then 2
                               else 1
                             end ), 0) as [XmlDocumentConstraint]
                  , cast(isnull(cc.is_persisted, 0) as bit) as [IsPersisted]
                  , cast(isnull(( select top 1
                                          1
                                  from    sys.foreign_key_columns as colfk
                                  where   colfk.parent_column_id = clmns.column_id
                                          and colfk.parent_object_id = clmns.object_id
                                ), 0) as bit) as [IsForeignKey]
                  , cast(clmns.is_sparse as bit) as [IsSparse]
                  , cast(clmns.is_column_set as bit) as [IsColumnSet]
             from   sys.tables as tbl
             inner join sys.all_columns as clmns on clmns.object_id = tbl.object_id
             left outer join sys.computed_columns as cc on cc.object_id = clmns.object_id
                                                           and cc.column_id = clmns.column_id
             left outer join sys.types as usrt on usrt.user_type_id = clmns.user_type_id
             left outer join sys.types as baset on ( baset.user_type_id = clmns.system_type_id
                                                     and baset.user_type_id = baset.system_type_id
                                                   )
                                                   or ( ( baset.system_type_id = clmns.system_type_id )
                                                        and ( baset.user_type_id = clmns.user_type_id )
                                                        and ( baset.is_user_defined = 0 )
                                                        and ( baset.is_assembly_type = 1 )
                                                      )
             left outer join sys.schemas as sclmns on sclmns.schema_id = usrt.schema_id
             left outer join sys.identity_columns as ic on ic.object_id = clmns.object_id
                                                           and ic.column_id = clmns.column_id
             left outer join sys.objects as d on d.object_id = clmns.default_object_id
             left outer join sys.objects as r on r.object_id = clmns.rule_object_id
             left outer join sys.xml_schema_collections as xscclmns on xscclmns.xml_collection_id = clmns.xml_collection_id
             left outer join sys.schemas as s2clmns on s2clmns.schema_id = xscclmns.schema_id
           ) ,
      zz1
        as ( select distinct
                    [Table_Name]
             from   zz
           ) ,
      zz2 ( [Table_Name], fileds )
        as ( select *
                  , ( select  [Name] + ' '
                              + case when datatype in ( 'nchar', 'nvarchar', 'char', 'varchar' )
                                     then datatype + '(' + cast([Length] as varchar(50)) + ')'
                                     else datatype
                                     + case [Identity] when 1 then ' identity('+cast([IdentitySeed] as varchar(50))+',' 
                                     +cast([IdentityIncrement] as varchar(50))
                                     +')' else '' end 
                                end + ',' as 'text()'
                      from    zz z1
                      where   z1.[Table_Name] = z.[Table_Name]
                      order by id
                    for
                      xml path('')
                        , type
                    ) as src
             from   zz1 z
           )
  select  *
        , 'Create table ' + [Table_Name] + @sufix + '(' + zz2.fileds.value('.[1]', 'varchar(max)')
          + @aadfields + ')'
  from    zz2

20 мар 12, 16:50    [12282761]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить