May 8, 2013 at 7:58 am
I am trying to write a query that will search for databases created in the last week. Once I get that info I need to query all the databases returned for information in a table that they all will have.
I know I can write the first part by querying sys.databases and using datediff to pull the ones created.
I don't know how to take that list and query them all for information maintained in the specific table.
exp..
db1.table1
db2.table1
db3.table1
all 3 tables would have info in one column that I'm trying to return.
thanks for any help
May 8, 2013 at 8:08 am
Newbie Jones (5/8/2013)
I am trying to write a query that will search for databases created in the last week. Once I get that info I need to query all the databases returned for information in a table that they all will have.I know I can write the first part by querying sys.databases and using datediff to pull the ones created.
I don't know how to take that list and query them all for information maintained in the specific table.
exp..
db1.table1
db2.table1
db3.table1
all 3 tables would have info in one column that I'm trying to return.
thanks for any help
You could use a cursor using the select statement you used against against sys.databases.
There is definately a much better solution for this, this is just off the top of my head..
May 8, 2013 at 10:14 am
Here is one way to do this without a cursor. It has the additional advantage of putting all the results into a single table instead of 1 table for each database.
declare @SQL nvarchar(max)
select @SQL = STUFF((
select 'UNION ALL select ''' + name + ''' as DBName, * from ' + name + '.dbo.table1 '
from sys.databases
where DATEDIFF(week, create_date, GETDATE()) < 1
for xml path('')), 1, 0, ' ')
select @SQL = stuff(@SQL, 1, 11, '') --This removes the UNION ALL at the beginning.
select @SQL
--sp_executesql @SQL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply