挑选出只依赖表的视图， 并得到相应的创建脚本

---------------- 测试表及视图 BEGIN ----------------
USE tempdb
GO
IF OBJECT_ID('dbo.t') IS NOT NULL
DROP TABLE dbo.t
GO
CREATE TABLE dbo.t(id INT PRIMARY KEY,userName NVARCHAR(20))
GO
IF OBJECT_ID('dbo.view_grand_father') IS NOT NULL
DROP VIEW dbo.view_grand_father
GO
IF OBJECT_ID('dbo.view_father') IS NOT NULL
DROP VIEW dbo.view_father
GO
IF OBJECT_ID('dbo.view_son') IS NOT NULL
DROP VIEW dbo.view_son
GO
IF OBJECT_ID('dbo.view_test') IS NOT NULL
DROP VIEW dbo.view_test
GO
CREATE VIEW dbo.view_son
AS
SELECT * FROM dbo.t
GO
CREATE VIEW dbo.view_father
AS
SELECT * FROM view_son
GO
CREATE VIEW dbo.view_grand_father
AS
SELECT * FROM view_father
GO
CREATE VIEW dbo.view_test
AS
SELECT * FROM view_son
UNION
SELECT * FROM dbo.t
GO
---------------- 测试表及视图 END ----------------

--1. 查出所有的视图的情况
SELECT v.name AS viewName,OBJECT_NAME(depid) depName,ISNULL((SELECT TOP 1 1 FROM sys.tables AS t WHERE t.[object_id]=s.depid),0) AS depTable,ISNULL((SELECT TOP 1 1 FROM sys.views AS v WHERE v.[object_id]=s.depid),0) AS depView 
FROM sys.[views] AS v INNER JOIN sys.sysdepends AS s ON v.[object_id]=s.id
WHERE s.depnumber=1
ORDER BY depTable DESC
/*
viewName depName		depTable depView
view_son	t			1			0
view_test	t			1			0
view_test	view_son	0			1
view_father	view_son	0			1
view_grand_father	view_father	0	1
*/

--2. 得到所有只依赖表的视图的脚本
;WITH cte AS (
	SELECT v.[object_id],0) AS depView 
	FROM sys.[views] AS v INNER JOIN sys.sysdepends AS s ON v.[object_id]=s.id
	WHERE s.depnumber=1	
)
select OBJECT_NAME(s.id) AS viewName,text 
from sys.syscomments AS s
WHERE s.id IN (
	SELECT a.object_id FROM cte a WHERE depTable=1 AND NOT EXISTS(
		SELECT * FROM cte b WHERE a.object_id=b.object_id AND b.depView=1	
	)
)
/*
viewName    text
view_son	CREATE VIEW dbo.view_son  AS  SELECT * FROM dbo.t  
*/
--余下就是多重依赖的视图， 略

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!