Tool for Scripting Table Data

Question

Are there any free tools for scripting MSSQL table data? I'll gladly write one, but am hoping it's been done, and the app matured a bit before/

Answer 1

A quick google and hop pointed me to a Stored proc that should be able to help you. Look at My code library more specifically the file generate_inserts.txt to see if it can help you.

Not really a tool, but the start of one! :)

Answer 2

TOAD for Oracle can do it, so I suspect TOAD for SQL Server will be able to too.

Answer 3

Here are some scripts I wrote for reverse engineering SQL server schemas. They may be of some use. Also, as a general interest they give some examples of how to get various bits of information out of the data dictionary. I've added an MIT license below to make permission-to-use explicit and for some basic no-implicit-warranty CYA. Enjoy.

-- ====================================================================
-- === 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 Nigel Campbell
-- 
--  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

Answer 4

Hi

Microsoft SQL Server Database Publishing Wizard is a nice GUI that can script out the structure / data / Procs of a SQL Server 2000/2005 db.

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

Hope this helps

Answer 5

For loading and unloading table data you could also use bcp, Integration Services or BULK INSERT (for the loading portion). This article describes a method to invoke bcp from within a stored procedure.

Answer 6

This is an impressive script, and I can use it for everything except the views. They are all on one line. If there are any comments in the view, the whole definition is messed up.

Do you have anything that can give me a better view definition?