Query Most Databases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Most Databases

Author

Topic

foxprorawks
Starting Member

17 Posts

Posted - 2009-06-24 : 11:41:20

I have a requirement to query our SQL Server to tell who is logged on to our application.

Each customer has their own database, and each database has a table called currentlogons.

However, as well as system databases such as master, there are other databases on the server.

I'd like to generate and run a sql statement like the following:

select ipaddress COLLATE DATABASE_DEFAULT as ipaddress, logon_name COLLATE DATABASE_DEFAULT as logon_name, logon_date, session_id from adatabase.dbo.currentlogons
union all
select ipaddress COLLATE DATABASE_DEFAULT as ipaddress, logon_name COLLATE DATABASE_DEFAULT as logon_name, logon_date, session_id from anotherdatabase.dbo.currentlogons
union all
select ipaddress COLLATE DATABASE_DEFAULT as ipaddress, logon_name COLLATE DATABASE_DEFAULT as logon_name, logon_date, session_id from yetanotherdatabase.dbo.currentlogons
union all
...

The database names don't follow a particular pattern - I'd want to dynamically include all databases which contain a currentlogons table.

I've looked at

SELECT name FROM sys.sysdatabases order by name

to get a list of databases, but I get a bit lost after that.

Can anyone help?

Thanks

Matt.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 12:58:59

you can use undocumented stored procedure sp_Msforeachdb for this. something like


EXEC sp_Msforeachdb('select ipaddress COLLATE DATABASE_DEFAULT as ipaddress, logon_name COLLATE DATABASE_DEFAULT as logon_name, logon_date, session_id from ?.dbo.currentlogons)'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 12:59:41

also see

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

foxprorawks
Starting Member

17 Posts

Posted - 2009-06-24 : 14:45:01

That returns a syntax error.

I think the bracket and quote at the end are around the wrong way. However, fixing that doesn't make the query work.

As I understand it, this will attempt to run the query against every database in the system. If it does that, it will still fail because not every database has a currentlogons table.

I'll read the link you posted when I get into the office tomorrow.

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-24 : 15:59:47

I have build a TestDB to have a test on it.
I am sure you are able to adapt it for your requirements.

use TestDB
go
create table Results (ipaddress varchar(255), logon_name varchar(255), logon_date datetime, session_id varchar(255))
declare @cmd1 varchar(1000)
set @cmd1=
'if object_id(''?.dbo.currentlogons'') is not null insert TestDB..Results select ipaddress COLLATE DATABASE_DEFAULT as ipaddress, logon_name COLLATE DATABASE_DEFAULT as logon_name, logon_date, session_id from ?.dbo.currentlogons  '
exec sp_MSforeachdb @command1=@cmd1

select * from Results
drop table Results

No, you're never too old to Yak'n'Roll if you're too young to die.

foxprorawks
Starting Member

17 Posts

Posted - 2009-06-25 : 10:10:08

Thanks guys.

This is the final version of my script - I made a small change to include the name of the db in the result:

use tempDB
go
create table Results (customer varchar(255),ipaddress varchar(255), logon_name varchar(255), logon_date datetime, session_id varchar(255))
declare @cmd1 varchar(1000)
set @cmd1='if object_id(''?.dbo.currentlogons'') is not null insert tempDB..Results select ''?'' as Customer, ipaddress COLLATE DATABASE_DEFAULT as ipaddress, logon_name COLLATE DATABASE_DEFAULT as logon_name, logon_date, session_id from ?.dbo.currentlogons '
exec sp_MSforeachdb @command1=@cmd1
select * from Results
drop table Results

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-25 : 10:56:17

Yes!

Cool that was my idea too, so you can see from which db the data was retrieved.
Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 13:54:14

quote:
Originally posted by foxprorawks

That returns a syntax error.

I think the bracket and quote at the end are around the wrong way. However, fixing that doesn't make the query work.

As I understand it, this will attempt to run the query against every database in the system. If it does that, it will still fail because not every database has a currentlogons table.

I'll read the link you posted when I get into the office tomorrow.

Thanks.

yup..that was typo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources