有什么样的工具可以按文件名完全地导出所有数据库对象?

[miaomiaoga]

好像是SQL SERVER里面是不提供的.只有一个个自己来导.或者导成了一个文件.
(比如表:user.tbl, company.tbl, 比如存储过程:usp_getcompany.sql, usp_getuser.sql, 比如:视图 v_company.view, v_user.view. 等等这样的格式)

我用了一些Red Gate的工具,也不行,请问一下各位有好的工具介绍么? SQL Delta也只是做数据库和内容对比而已. :(


---------------


另外一个问题,就是我新建一个数据库,然后尝试把其它数据库里的某几个表导入来的时候,导入成功了,但主键好像不会自动导入的.是不是我设置上的问题呢?还是MS SQL SERVER里不让这样做呢?


两个问题都是小问题,我找了一下GOOGLE,好多都是答非所问的文章,而且很多网站都是一样的文章,只好来这里问了.请大家不要骂偶.感谢.

[快乐_石头]

生成脚本--执行--导数据

[miaomiaoga]

试过了,感谢楼上的.我看了一下里面有个选项."Generate Script for Dependent Objects".选了这个就可以了.

谢谢.:)


但如果可以 TABLE / VIEW / STORED PROCEDURE 可以分开扩展名就好了.:)

有这样的工具么?:)..

[让你望见影子的墙]

貌似没有

[dawugui]

[Quote=引用楼主 miaomiaoga 的帖子:]
好像是SQL SERVER里面是不提供的.只有一个个自己来导.或者导成了一个文件.
(比如表:user.tbl, company.tbl, 比如存储过程:usp_getcompany.sql, usp_getuser.sql, 比如:视图 v_company.view, v_user.view. 等等这样的格式)

我用了一些Red Gate的工具,也不行,请问一下各位有好的工具介绍么? SQL Delta也只是做数据库和内容对比而已. :(
[/Quote]

1. SqlServer数据库字典--表结构.sql

SELECT TOP 100 PERCENT --a.id, 

CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名, 

CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明, 

a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id, 

a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识, 

CASE WHEN EXISTS 

(SELECT 1 

FROM dbo.sysindexes si INNER JOIN 

dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN 

dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN 

dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK' 

WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, 

b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION') 

AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, 

CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '') 

AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间, 

CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间 

FROM dbo.syscolumns a LEFT OUTER JOIN 

dbo.systypes b ON a.xtype = b.xusertype INNER JOIN 

dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND 

d.status >= 0 LEFT OUTER JOIN 

dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN 

dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN 

dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 

ORDER BY d.name, a.colorder


----------------------------------------------------------------
2.SqlServer数据库字典--索引.sql

SELECT TOP 100 PERCENT --a.id, 

CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS 表名, 

CASE WHEN b.keyno = 1 THEN a.name ELSE '' END AS 索引名称, d.name AS 列名, 

b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending') 

WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL 

THEN '' ELSE '√' END AS 主键, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered') 

WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY(c.id, 

a.name, 'IsUnique') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一, 

CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一约束, 

a.OrigFillFactor AS 填充因子, c.crdate AS 创建时间, c.refdate AS 更改时间 

FROM dbo.sysindexes a INNER JOIN 

dbo.sysindexkeys b ON a.id = b.id AND a.indid = b.indid INNER JOIN 

dbo.syscolumns d ON b.id = d.id AND b.colid = d.colid INNER JOIN 

dbo.sysobjects c ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN 

dbo.sysobjects e ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN 

dbo.sysobjects p ON p.name = a.name AND p.xtype = 'PK' 

WHERE (OBJECTPROPERTY(a.id, N'IsUserTable') = 1) AND (OBJECTPROPERTY(a.id, 

N'IsMSShipped') = 0) AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0) 

ORDER BY c.name, a.name, b.keyno


-------------------------------------------------

3.SqlServer数据库字典--主键.外键.约束.视图.函数.存储过程.触发器.sql

SELECT DISTINCT 

TOP 100 PERCENT o.xtype, 

CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 'PK' THEN 

'主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' WHEN 'FN' 

THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' ELSE '存储过程' 

END AS 类型, o.name AS 对象名, o.crdate AS 创建时间, o.refdate AS 更改时间, 

c.text AS 声明语句 

FROM dbo.sysobjects o LEFT OUTER JOIN 

dbo.syscomments c ON o.id = c.id 

WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND 

(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0) 

ORDER BY CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 

'PK' THEN '主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' 

WHEN 'FN' THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' 

ELSE '存储过程' END DESC

[dawugui]

[Quote=引用楼主 miaomiaoga 的帖子:]
另外一个问题,就是我新建一个数据库,然后尝试把其它数据库里的某几个表导入来的时候,导入成功了,但主键好像不会自动导入的.是不是我设置上的问题呢?还是MS SQL SERVER里不让这样做呢?
[/Quote]

如果是导入导出,
1.先建立主键,再导入数据.
2.或是导入完毕后,再建立主键.