获取包含名为“foo"的视图的所有数据库的列表 [英] Get list of all databases that have a view named 'foo' in them

问题描述

我有几台服务器，里面有一堆数据库.一些数据库有一个名为 vw_mydata 的视图.

I have a few servers that have a bunch of databases in them. Some of the databases have a view called vw_mydata.

我想要做的是创建一个包含名为 vw_mydata 的视图的所有数据库的列表，然后执行该视图并将其内容存储在一个表中，然后该表包含来自所有 vw_mydata 的所有数据.

What I want to do is create a list of all databases containing a view named vw_mydata and then execute that view and store it's contents in a table that then contains al the data from all the vw_mydata.

我知道我可以使用

sp_msforeachdb 'select "?" AS dbName from [?].sys.views where name like ''vw_mydata'''

但是我的记录集和数据库一样多.我如何使用它来遍历数据库?

But then I have as many recordsets as I have databases. How do I use that to loop through the databases?

我更喜欢的是一个完整的数据库名称列表，然后我可以将其存储在结果集中.然后就很简单了.

What I would preferis a single neat list of the databasenames that I then can store in a resultset. Then it would be pretty straightforward.

我曾想过在 TSQL 之上运行并将结果存储在一个表中，但我想将其全部保存在一个 SSIS 包中，而不是到处都有各种表/过程.我可以在 SSIS 的执行 SQL 任务中使用 #table 吗?

I have thought about running above TSQL and storing the results in a table but I would like to keep it all in one SSIS package and not having all kind of tables/procedures lying around. Can I use a #table in a Execute SQL Task in SSIS?

推荐答案

DECLARE @Tsql VARCHAR(MAX)
SET @Tsql = ''

SELECT @Tsql = @Tsql + 'SELECT ''' + d.name + ''' AS dbName FROM [' + d.name + '].sys.views WHERE name LIKE ''vw_mydata'' UNION '
FROM master.sys.databases d

--"trim" the last UNION from the end of the tsql.
SET @Tsql = LEFT(@Tsql, LEN(@Tsql) - 6)

PRINT @Tsql

--Uncomment when ready to proceed
--EXEC (@Tsql)

这篇关于获取包含名为“foo"的视图的所有数据库的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！