Jan
14
Written by:
Dylan Barber
1/14/2010 4:16 PM
Recently I have started helping out with some development and support for FindEightDigital.com. FindEight has a dedicated server and has smartly split up portals into databases that correspond to the level of traffic they receive.
This presents me with a bit of a problem because I need to discover what portal is in what database and its id. After looking this up in about three databases I decide to get my friend (http://zackue.com) to write me a little query so I can always find the right database. Here is the nice little query he came up with.
IF OBJECT_ID ('tempdb..##TempTemp','U') IS NOT NULL
DROP TABLE ##TempTemp
CREATE TABLE ##TempTemp (ID INT, Name NVARCHAR(MAX ), DbName NVARCHAR(MAX))
exec sp_MSforeachdb 'IF OBJECT_ID (''?.dbo.Portals'',''U'') IS NOT NULL INSERT ##TempTemp SELECT PortalId, PortalName , ''?'' FROM ?.dbo.Portals'
select * from ##TempTemp;