有没有免费的工具可以编写MSSQL表数据的脚本？我很愿意自己写一个，但是希望已经有人做了，并且应用程序已经成熟一些。
表格数据脚本化工具
7
- ProfK
4
那句话“脚本化MSSQL表数据”是什么意思？ - Evan Carroll
@EvanCarroll 这个问题的赞数和四个非常有用的答案表明你在理解它的意思方面处于明显的少数，但是为了你，我来解释一下。它的意思是使用表格数据生成脚本，将该数据插入到另一个表格中，例如不同数据库中的同一张表格。 - ProfK
那么这只是这个问题的重复：https://dev59.com/xXNA5IYBdhLWcg3wYMx- - Evan Carroll
@EvanCarroll 那个问题是在2009年6月11日提出的，而我的是在2008年10月8日。如果有任何一个问题是重复的，那就是那个问题。 - ProfK
5个回答
19
以下是我为反向工程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
- Nigel Campbell
1
谢谢，但那并不是我的意图。我想为表中的数据生成插入语句，但那个令人印象深刻的脚本很值得一试。 - ProfK
8
Microsoft SQL Server数据库发布向导是一个友好的图形用户界面，可以将SQL Server 2000/2005数据库的结构、数据和存储过程脚本化。
希望这能有所帮助。
- Dave Mateer
1
1是的，非常感谢。我刚在 MS SQL 2000 上尝试了一下，效果很好。在此之前，我一直在使用 sp_generate_inserts。 - Bratch
3
一次快速的谷歌搜索和跳转，我找到了一个存储过程可以帮助你。请查看我的代码库，更具体地说是文件generate_inserts.txt，看看它是否能够帮助你。
虽然不是一个完整的工具，但这是一个好的开始！ :)
虽然不是一个完整的工具，但这是一个好的开始！ :)
- FryHard
5
我有这样的印象，他想要某种逆向工程模式来处理模式（但回想起来，问题有些含糊不清）。然而，您的代码库绝对值得一加。 - ConcernedOfTunbridgeWells
这不是我的代码库……网站只是那样叫的 :) 希望这不会让我失去我的+1 :P - FryHard
我会让你通过 ;-} - 这个链接仍然指向一个非常有用的地方。 你可能想编辑你的帖子并放置一个免责声明。 - ConcernedOfTunbridgeWells
我不擅长逆向工程模式，不知道这是什么意思。是指从数据中推断出模式吗？ - ProfK
请见我下面的评论（您已经看过了） - 通过反向工程，我的意思是阅读系统数据字典，并为数据库模式生成创建表脚本。 - ConcernedOfTunbridgeWells
1
如果要加载和卸载表数据，您也可以使用bcp、Integration Services或BULK INSERT（用于加载部分）。本文描述了一种从存储过程中调用bcp的方法。
- Nigel Campbell
-1
TOAD for Oracle可以做到，所以我认为TOAD for SQL Server也能够做到。
- cagcowboy
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接