Get list of all databases that have a view named &

问题:

I have a few servers that have a bunch of databases in them. Some of the databases have a view called 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.

I know I can find all the databases containing that view using

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.

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?

回答1:

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)

回答2:

To use a temp table in SSIS, you'll need to use a global temp table (##TABLE).

On the properties for the connection, I'm pretty sure you'll need to set RetainSameConnection to TRUE.

On the SQL task after you create the temp table, you'll need to set DelayValidation to TRUE.