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

---------------- 测试表及视图 BEGIN ----------------USE tempdbGOIF OBJECT_ID('dbo.t') IS NOT NULLDROP TABLE dbo.tGOCREATE TABLE dbo.t(id INT PRIMARY KEY,userName NVARCHAR(20))GOIF OBJECT_ID('dbo.view_grand_father') IS NOT NULLDROP VIEW dbo.view_grand_fatherGOIF OBJECT_ID('dbo.view_father') IS NOT NULLDROP VIEW dbo.view_fatherGOIF OBJECT_ID('dbo.view_son') IS NOT NULLDROP VIEW dbo.view_sonGOIF OBJECT_ID('dbo.view_test') IS NOT NULLDROP VIEW dbo.view_testGOCREATE VIEW dbo.view_sonASSELECT * FROM dbo.tGOCREATE VIEW dbo.view_fatherASSELECT * FROM view_sonGOCREATE VIEW dbo.view_grand_fatherASSELECT * FROM view_fatherGOCREATE VIEW dbo.view_testASSELECT * FROM view_sonUNIONSELECT * FROM dbo.tGO---------------- 测试表及视图 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.idWHERE s.depnumber=1ORDER BY depTable DESC/*viewName depNamedepTable depViewview_sont10view_testt10view_testview_son01view_fatherview_son01view_grand_fatherview_father01*/--2. 得到所有只依赖表的视图的脚本;WITH cte AS (SELECT v.[object_id],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.idWHERE s.depnumber=1)select OBJECT_NAME(s.id) AS viewName,text from sys.syscomments AS sWHERE 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    textview_sonCREATE VIEW dbo.view_son  AS  SELECT * FROM dbo.t  */--余下就是多重依赖的视图， 略