Herramienta para datos de tablas de secuencias de comandos

¿Hay alguna herramienta gratuita para crear scripts de datos de tabla MSSQL? Con mucho gusto escribiré uno, pero espero que se haya hecho, y la aplicación maduró un poco antes /

El Asistente de publicación de bases de datos de Microsoft SQL Server es una buena GUI que puede crear secuencias de comandos de la estructura / datos / Procs de un SQL Server 2000/2005 db.

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

Espero que esto ayude

Aquí hay algunos scripts que escribí para ingeniería inversa de esquemas de servidor SQL. Pueden ser de algún uso. Además, como un interés general, dan algunos ejemplos de cómo extraer varios bits de información del diccionario de datos. He agregado una licencia de MIT a continuación para hacer explícitos los permisos de uso y para algunos CYA de garantía básica no implícita. Disfrutar.

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

Un Google y un salto rápidos me indicaron un proc. Almacenado que debería poder ayudarte. Mira Mi biblioteca de códigos más específicamente el archivo generate_inserts.txt para ver si puede ayudarte.

¡No es realmente una herramienta, sino el comienzo de una! 🙂

Para cargar y descargar datos de tablas, también puede usar bcp , Integration Services o BULK INSERT (para la parte de carga). Este artículo describe un método para invocar bcp desde un procedimiento almacenado.

TOAD for Oracle puede hacerlo, así que sospecho que TOAD para SQL Server también podrá hacerlo.