March 30, 2011 at 6:49 am
Is there a way to run a query against certain databases and have the results come back as one result set like UNION would do? I want to create a view for SSRS and I need my query to run only against certain databases. The number of databases may grow but the naming convention will always be the same. Does this make sense?
March 30, 2011 at 6:58 am
You'll need to use dynamic sql to build the sql statement with all the union alls. This assumes that the # of db can change at any time and you don't want to recode all the reports.
March 30, 2011 at 7:01 am
I was thinking of that but I would always have a UNION at the end of my statement which would blow everything up. How do I get around that?
March 30, 2011 at 7:11 am
Not sure I get your question.
Are you having trouble building a valid select statement in dynamic sql?
DECLARE @Exec VARCHAR(MAX)
SET @Exec = ''
SELECT
@Exec = @Exec + 'SELECT * FROM [' + name + '].sys.objects UNION ALL '
FROM
[master].dbo.sysdatabases
WHERE
--Use your parameter in hear instead. --Make sure to protect against sql injection
name NOT IN ( 'master' , 'tempdb' , 'model' , 'msdb' )
SET @Exec = STUFF(@Exec , DATALENGTH(@Exec) - 10 , 10 , '')
PRINT @Exec
EXEC (
@Exec
)
ROLLBACK
BTW you need union ALL in this case, union performs a distinct to flush out duplicates. I assume you don't need to do this in this particular case.
March 30, 2011 at 7:57 am
Didn't know about the STUFF command, that helps. Another question, using that code how can I get this to work using it? I've been trying and I can't quite get it working.
SELECT FullUrl AS 'Site URL', TimeCreated, DATEADD(d,
DayLastAccessed + 65536,
CONVERT(datetime, '1/1/1899', 101)) AS
lastAccessDate
FROM [DATABSE_NAME].dbo.Webs
WHERE (DayLastAccessed <> 0)
March 30, 2011 at 8:22 am
Never mind, I got everything working. Thank you very much for your help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply