我现在写了个小工具。将视图导出成sql语句。但是视图的依赖关系没有解决。
谁有好的办法。
--------------
SELECT DISTINCT
dbo.sysobjects.name, sysobjects_1.name AS name2,
sysobjects_1.xtype,depnumber,depid,sysdepends.id,sysobjects.type
FROM dbo.sysobjects sysobjects_1 INNER JOIN
dbo.sysdepends ON sysobjects_1.id = dbo.sysdepends.id RIGHT OUTER JOIN
dbo.sysobjects ON dbo.sysdepends.depid = dbo.sysobjects.id
WHERE ((dbo.sysobjects.type = 'v'or dbo.sysobjects.type = 'P'or dbo.sysobjects.type = 'u') and dbo.sysobjects.STATUS>=0 and sysobjects.name <> 'syssegments' and sysobjects.name <> 'sysconstraints')
order by depnumber descTABLE1 tttt P 2 1253579504 1653580929 U
TABLE1 VIEW1 V 2 1253579504 1621580815 U
TABLE2 VIEW2 V 2 1589580701 1637580872 U
VIEW1 VIEW3 V 2 1621580815 1685581043 V
VIEW2 VIEW3 V 2 1637580872 1685581043 V
TABLE1 tttt P 1 1253579504 1653580929 U
TABLE1 VIEW1 V 1 1253579504 1621580815 U
TABLE2 VIEW2 V 1 1589580701 1637580872 U
tttt ttt P 1 1653580929 1701581100 P
VIEW1 VIEW3 V 1 1621580815 1685581043 V
VIEW2 VIEW3 V 1 1637580872 1685581043 V
TABLE3 NULL NULL NULL NULL NULL U
ttt NULL NULL NULL NULL NULL P
VIEW3 NULL NULL NULL NULL NULL V
不知道这个有没有用。如果有用的话。该如果来程序来实现先后关系。可以用JAVA或。NET
谁有好的办法。
--------------
SELECT DISTINCT
dbo.sysobjects.name, sysobjects_1.name AS name2,
sysobjects_1.xtype,depnumber,depid,sysdepends.id,sysobjects.type
FROM dbo.sysobjects sysobjects_1 INNER JOIN
dbo.sysdepends ON sysobjects_1.id = dbo.sysdepends.id RIGHT OUTER JOIN
dbo.sysobjects ON dbo.sysdepends.depid = dbo.sysobjects.id
WHERE ((dbo.sysobjects.type = 'v'or dbo.sysobjects.type = 'P'or dbo.sysobjects.type = 'u') and dbo.sysobjects.STATUS>=0 and sysobjects.name <> 'syssegments' and sysobjects.name <> 'sysconstraints')
order by depnumber descTABLE1 tttt P 2 1253579504 1653580929 U
TABLE1 VIEW1 V 2 1253579504 1621580815 U
TABLE2 VIEW2 V 2 1589580701 1637580872 U
VIEW1 VIEW3 V 2 1621580815 1685581043 V
VIEW2 VIEW3 V 2 1637580872 1685581043 V
TABLE1 tttt P 1 1253579504 1653580929 U
TABLE1 VIEW1 V 1 1253579504 1621580815 U
TABLE2 VIEW2 V 1 1589580701 1637580872 U
tttt ttt P 1 1653580929 1701581100 P
VIEW1 VIEW3 V 1 1621580815 1685581043 V
VIEW2 VIEW3 V 1 1637580872 1685581043 V
TABLE3 NULL NULL NULL NULL NULL U
ttt NULL NULL NULL NULL NULL P
VIEW3 NULL NULL NULL NULL NULL V
不知道这个有没有用。如果有用的话。该如果来程序来实现先后关系。可以用JAVA或。NET
DECLARE @SQL_CREATE_VIEW varchar(8000)
SELECT @SQL_CREATE_VIEW=VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='titleview'
PRINT @SQL_CREATE_VIEW--自动生成建立视图的脚本,如:
/*
CREATE VIEW titleview
AS
select title, au_ord, au_lname, price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id
*/
FROM dbo.sysdepends a
left JOIN dbo.sysobjects b ON a.id=b.id
WHERE a.depid=object_id('输入你的视图名') and b.status>0
我不是把视图转换成SQL语句吗。我就是想要他们之间的那个依赖关系。也是说先创建哪个。然后在创建哪个。
SELECT DISTINCT OBJECT_NAME(id) AS ViewName,OBJECT_NAME(depid) AS DepName FROM
sysdepends WHERE id=OBJECT_ID('vwgEmployeeSalary')
是于别的视图约束,得看实际的情况,在帮助里面,一些字段未提供详细说明,所以得自己测试。
因为depid来自sysobjects中的id,按depid排序就可以知道哪一对象建立,哪一对象后建立。
as
select top 10 name from sysobjects
go
create view v_1_1
as
select * from v_1
go
create view v_1_1_1
as
select * from v_1_1
go
create view v_1_2
as
select * from v_1
go
create view v_2
as
select top 10 name from sysobjects
goselect b.name,a.id,a.depid
from dbo.sysdepends a,dbo.sysobjects b
where a.id=b.id
and b.name in ('v_1','v_2','v_1_1','v_1_2','v_1_1_1')
and b.status>0
/*
name id depid
------------------------------------
v_1 45243216 1
v_1_1 61243273 45243216
v_1_1_1 109243444 61243273
v_1_2 77243330 45243216
v_2 93243387 1
*/
先建立depid=1的视图,再按递归依次建立它的下级