gsc_dba (10/26/2012)
I think you can use the JOIN function in this situation
= "SELECT * FROM " & JOIN(Parameters!DatabasesOnServer.Value) & ".sys.tables
Actually, you need a while loop which takes the parameter selection(s) and iterates over the
SELECT * FROM [DataBaseNameGoesHere].[sys].[tables] AS T
replacing the [DataBaseNameGoesHere] for each iteration.
Something like this:
DECLARE @dbnames TABLE
(
id TINYINT IDENTITY(1, 1)
NOT NULL
, dbnames VARCHAR(150) NOT NULL
)
INSERT INTO @dbnames
(
[dbnames]
)
SELECT
[name]
FROM
sys.[databases] AS D
WHERE
[database_id] > 4
DECLARE
@counter INT = 1
, @maxRecordsToProcess INT
SET @maxRecordsToProcess = (
SELECT
COUNT(*)
FROM
@dbnames
)
DECLARE @dsql NVARCHAR(MAX)
WHILE @counter <= @maxRecordsToProcess
BEGIN
DECLARE @dbname VARCHAR(150) = (
SELECT TOP 1
dbnames
FROM
@dbnames
WHERE
@counter = id
)
SELECT
@dsql = 'SELECT * FROM ' + @dbname + '.[sys].[tables] AS T'
EXEC [sys].[sp_executesql] @dsql
SET @counter += 1
END
gsc_dba