Инструмент для создания сценариев табличных данных

Быстрый поиск в Google и прыжок указал мне на хранимую процедуру, которая должна вам помочь. Посмотрите на Моя библиотека кода, а точнее на файл generate_inserts.txt, чтобы узнать, может ли он вам помочь.

Не совсем инструмент, но только начало! :)

TOAD для Oracle может это сделать, поэтому я подозреваю, что TOAD для SQL Server тоже сможет.

Вот несколько сценариев, которые я написал для обратного проектирования схем SQL-серверов. Они могут быть полезны. Кроме того, в качестве общего интереса они приводят несколько примеров того, как получить различные биты информации из словаря данных. Я добавил лицензию MIT ниже, чтобы сделать разрешение на использование явным и для некоторых базовых CYA без неявной гарантии. Наслаждаться.

-- ====================================================================
-- === reverse_engineer_2005.sql ======================================
-- ====================================================================
-- 
--  Script to generate table, index, pk, view and fk definitions from
--  a SQL Server 2005 database.  Adapted from one I originally wrote 
--  for SQL Server 2000.  It's not comprehensive (doesn't extract 
--  partition schemes) but it does do defaults and computed columns
--
--  Run the script with 'results to text' and cut/paste the output into
--  the editor window.  Set the schema as described below.
--
--  Copyright (c) 2004-2008 Concerned of Tunbridge Wells
-- 
--  Permission is hereby granted, free of charge, to any person
--  obtaining a copy of this software and associated documentation
--  files (the "Software"), to deal in the Software without
--  restriction, including without limitation the rights to use,
--  copy, modify, merge, publish, distribute, sublicense, and/or sell
--  copies of the Software, and to permit persons to whom the
--  Software is furnished to do so, subject to the following
--  conditions:
--
--  The above copyright notice and this permission notice shall be
--  included in all copies or substantial portions of the Software.
--
--  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
--  EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
--  OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
--  NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
--  HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
--  WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
--  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
--  OTHER DEALINGS IN THE SOFTWARE.
--
-- ====================================================================
--

set nocount on

-- This does a specific schema.  Set the schema here
--
declare @schema varchar (max)
select @schema = 'dbo'

if object_id ('tempdb..#objects') is not null begin
    drop table #objects
end

if object_id ('tempdb..#views') is not null begin
    drop table #views
end

if object_id ('tempdb..#types') is not null begin
    drop table #types
end


-- Gets lists of tables and views belonging to the schema
--
select o.name
      ,o.object_id
  into #objects
  from sys.objects o
  join sys.schemas s
    on s.schema_id = o.schema_id
 where o.type in  ('U')
   and s.name = @schema


select o.name
      ,o.object_id
  into #views
  from sys.objects o
  join sys.schemas s
    on s.schema_id = o.schema_id
 where o.type in  ('V')
   and s.name = @schema


-- Some metadata for rendering types
--
select a.* 
  into #types
  from ((select 'decimal' as typename, 6 as format) union all
        (select 'numeric', 6) union all
        (select 'varbinary', 1) union all
        (select 'varchar', 1) union all
        (select 'char', 1) union all
        (select 'nvarchar', 1) union all
        (select 'nchar', 1)) a





-- This generates 'drop table' and 'drop view' statements
--
select 'if exists (select 1' + char(10) +
       '             from sys.objects o' + char(10) +
       '             join sys.schemas s' + char(10) +
       '               on o.schema_id = s.schema_id' + char(10) +
       '            where o.name = ''' + o.name + '''' + char(10) +
       '              and s.name = ''' + @schema +'''' + char(10) +
       '              and o.type = ''U'') begin' + char(10) +
       '    drop table [' + @schema + '].[' + o.name + ']' + char(10) +
       'end' + char(10) +
       'go' + char(10)
  from sys.objects o
  join #objects o2
    on o.object_id = o2.object_id
 where o.type = 'U'


select 'if exists (select 1' + char(10) +
       '             from sys.objects o' + char(10) +
       '             join sys.schemas s' + char(10) +
       '               on o.schema_id = s.schema_id' + char(10) +
       '            where o.name = ''' + o.name + '''' + char(10) +
       '              and s.name = ''' + @schema + '''' + char(10) +
       '              and o.type = ''V'') begin' + char(10) +
       '    drop view [' + @schema + '].[' + o.name + ']' + char(10) +
       'end' + char(10) +
       'go' + char(10)
  from sys.objects o
  join #objects o2
    on o.object_id = o2.object_id
 where o.type = 'V'


-- This generates table definitions
--
select case when c.column_id = 
                 (select min(c2.column_id)
                    from sys.columns c2
                   where c2.object_id = o.object_id)
            then 'create table [' + @schema + '].[' + isnull(o.name, 'XYZZY') + '] (' + char(10)
            else ''
            end +
       left('        [' +rtrim(c.name) + '] ' +
       '                                                  ', 48) +
       isnull(calc.text, 
              t.name +
              case when tc.format & 2 = 2 
                   then ' (' +convert (varchar, c.precision) +
                   case when tc.format & 2 = 2
                        then ', ' + convert (varchar, c.scale)
                        else ''
                   end + ')'
                   when tc.format & 1 = 1
                   then ' (' + convert (varchar, c.max_length) + ')'
                   else ''
              end + ' ' + 
              case when c.is_nullable <> 0 then 'null'
                   else 'not null'
              end + isnull(ident.text, isnull(con.text, ''))) +
       case when c.column_id =
            (select max(c2.column_id)
               from sys.columns c2
              where c2.object_id = o.object_id)
            then char(10) + ')' + char(10) + 'go' + char(10)
            else ','
            end
  from sys.objects o
  join #objects o2
    on o.object_id = o2.object_id
  join sys.columns c
    on c.object_id = o.object_id
  join sys.types t
    on c.user_type_id = t.user_type_id
  left join 
       (select object_id,
               column_id,
               'as ' + definition as text
          from sys.computed_columns) calc
    on calc.object_id = o.object_id
   and calc.column_id = c.column_id
  left join
       (select parent_object_id,
               parent_column_id,
               ' default ' + definition as text
          from sys.default_constraints) con
    on con.parent_object_id = o.object_id
   and con.parent_column_id = c.column_id
  left join
       (select o.object_id,
               col.column_id,
               ' identity (' + convert(varchar, ident_seed(o.name)) + ', ' +
                               convert(varchar, ident_incr(o.name)) + ')' as text
          from sys.objects o
          join sys.columns col
            on o.object_id = col.object_id
         where columnproperty (o.object_id, col.name, 'IsIdentity') = 1) as ident
    on ident.object_id = o.object_id
   and ident.column_id = c.column_id
  left join #types tc
    on tc.typename = t.name
 where o.type = 'U'
 order by o.name,
          c.column_id


-- This generates view definitions 
--
select definition + char(10) + 'go' + char(10)
  from sys.sql_modules c
  join sys.objects o
    on c.object_id = o.object_id
  join #views o2
    on o.object_id = o2.object_id



-- This generates PK and unique constraints
--

select case when ik.key_ordinal = 
            (select min(ik2.key_ordinal)
                from sys.index_columns ik2
               where ik2.object_id = ik.object_id
                 and ik2.index_id = ik.index_id)
            then 'alter table [' + rtrim (s.name) + '].[' + rtrim(t.name) + ']' + char(10) +
                 '  add constraint [' + rtrim (pk.name) + '] ' + 
                 case when pk.type = 'PK' then 'primary key'
                      when pk.type = 'UQ' then 'unique'
                      else 'foobar'
                      end + char(10) +
                 '      ('
            else '      ,'
            end +
            '[' + rtrim(c.name) + ']' +
        case when ik.key_ordinal =
             (select max(ik2.key_ordinal)
                from sys.index_columns ik2
               where ik2.object_id = ik.object_id
                 and ik2.index_id = ik.index_id)
             then ')' + char(10) + 'go' + char(10)
             else ''
             end
   from sys.objects t           -- table
   join #objects o
     on t.object_id = o.object_id
   join sys.schemas s
     on s.schema_id = t.schema_id
   join sys.objects pk          -- key
     on pk.parent_object_id = t.object_id
   join sys.columns c           -- columns
     on c.object_id = t.object_id
   join sys.indexes i           -- get index for constraint
     on i.object_id = t.object_id
    and i.name = pk.name
   join sys.index_columns ik        -- index column and name
     on ik.object_id = i.object_id
    and ik.index_id = i.index_id
    and ik.column_id = c.column_id     -- vvv Get the right index
  where c.name = index_col('[' + s.name + '].[' + t.name + ']', i.index_id, ik.key_ordinal)
    and pk.type in ('PK', 'UQ')   --probably redundant
  order by t.object_id,
           pk.object_id,
           ik.key_ordinal



-- This generates indexes
--
select case when ik.key_ordinal = 
             (select min(ik2.key_ordinal)
                from sys.index_columns ik2
               where ik2.object_id = ik.object_id
                 and ik2.index_id = ik.index_id)
            then 'create ' +
            case when is_unique_constraint = 1 then 'unique '
                 else ''
                 end +
            'index [' + rtrim(i.name) + ']' + char (10) +
            '    on [' + rtrim(t.name) + ']' + char (10) +
            '       ('
       else '       ,'
        end +
       '[' + c.name + ']' +
       case when ik.key_ordinal = 
            (select max(ik2.key_ordinal)
               from sys.index_columns ik2
              where ik2.object_id = ik.object_id
                and ik2.index_id = ik.index_id)
            then ')' + char(10) + 'go' + char(10)
            else ''
            end
  from sys.objects t           -- table
  join #objects o
    on o.object_id = t.object_id
  join sys.columns c           -- columns
    on c.object_id = t.object_id
  join sys.indexes i           -- get index for constraint
    on i.object_id = t.object_id
  join sys.index_columns ik        -- index column and name
    on ik.object_id = i.object_id
   and ik.index_id = i.index_id
   and ik.column_id = c.column_id     -- vvv Get the right index
 where c.name = index_col(t.name, i.index_id, ik.key_ordinal)
   and t.type = 'U'
   and i.name <> t.name
   and i.name not in
       (select c2.name
          from sys.objects c2
         where c2.parent_object_id = t.object_id
           and c2.type in ('PK', 'UQ'))
 order by t.name,
          i.name,
          ik.key_ordinal


-- This generates foreign keys
--
select con.constraint_text as [--constraint_text]
  from ((select case when kc.constraint_column_id = 
                     (select min(k2.constraint_column_id)
                        from sys.foreign_key_columns k2
                       where k2.constraint_object_id = k.object_id)
                     then 'alter table [' + @schema + '].[' + rtrim(t.name) + ']' + char(10) +
                          '  add constraint [' + rtrim (k.name) + '] ' + char(10) +
                          '      foreign key ('
                     else '                  ,'
                     end +
                '[' + tc.name + ']' +
                case when kc.constraint_column_id =
                     (select max(k2.constraint_column_id)
                        from sys.foreign_key_columns k2
                       where k2.constraint_object_id = k.object_id)
                     then ')' 
                     else ''
                     end as constraint_text,
                t.name as table_name,
                k.name as constraint_name,
                kc.constraint_column_id as row_order,
                t.object_id
           from sys.foreign_keys k
           join sys.objects t
             on t.object_id = k.parent_object_id
           join sys.columns tc
             on tc.object_id = t.object_id
           join sys.foreign_key_columns kc
             on kc.constraint_object_id = k.object_id
            and kc.parent_object_id = t.object_id
            and kc.parent_column_id = tc.column_id
           join sys.objects r
             on r.object_id = kc.referenced_object_id
           join sys.columns rc
             on kc.referenced_object_id = rc.object_id
            and kc.referenced_column_id = rc.column_id)
        union all
        (select case when kc.constraint_column_id = 
                     (select min(k2.constraint_column_id)
                        from sys.foreign_key_columns k2
                       where k2.constraint_object_id = k.object_id)
                     then '      references [' + rtrim(r.name) + ']' + char(10) +
                          '                 ('
                     else '                 ,'
                     end +
                '[' + rc.name + ']' +
                case when kc.constraint_column_id = 
                     (select max(k2.constraint_column_id)
                        from sys.foreign_key_columns k2
                       where k2.constraint_object_id = k.object_id)
                     then ')' + char(10) + 'go' + char(10)
                     else ''
                     end as constraint_text,
                t.name as table_name,
                k.name as constraint_name,
                kc.constraint_column_id + 100 as row_order,
                t.object_id
           from sys.foreign_keys k
           join sys.objects t
             on t.object_id = k.parent_object_id
           join sys.columns tc
             on tc.object_id = t.object_id
           join sys.foreign_key_columns kc
             on kc.constraint_object_id = k.object_id
            and kc.parent_object_id = t.object_id
            and kc.parent_column_id = tc.column_id
           join sys.objects r
             on r.object_id = kc.referenced_object_id
           join sys.columns rc
             on kc.referenced_object_id = rc.object_id
            and kc.referenced_column_id = rc.column_id)) con
  join #objects o
    on con.object_id = o.object_id
 order by con.table_name, 
          con.constraint_name, 
          con.row_order

Мастер публикации базы данных Microsoft SQL Server - это красивый графический интерфейс, который может создавать скрипты для структуры / данных / процессов базы данных SQL Server 2000/2005.

http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Надеюсь это поможет

Для загрузки и выгрузки табличных данных вы также можете использовать bcp, Службы интеграции или ОБЪЕМНАЯ ВСТАВКА (для загрузки). эта статья описывает метод вызова bcp из хранимой процедуры.