select statement - 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)

select statement

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-02-24 : 11:43:28

Hello guys,
I need help for sql to listing no. of server's name,no. of db used on each serve and no. of host connecting on each server.
Lookat at the following script which i have to execute single time.
Anybody can mearge and display on single execution.

select count(B.[name])
FROM SYS.SERVERS a,[YourServerName].[MASTER].SYS.DATABASES b, [YourServerName].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]='YourServerName' AND b.database_id=c.dbid AND b.[NAME] NOT IN ('master')

select count(c.[HostName])
FROM SYS.SERVERS a,[YourServerName].[MASTER].SYS.DATABASES b, [YourServerName].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]='YourServerName' AND b.database_id=c.dbid AND b.[NAME] NOT IN ('master')

select SUM(c.[CPU])
FROM SYS.SERVERS a,[YourServerName].[MASTER].SYS.DATABASES b, [YourServerName].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]='YourServerName' AND b.database_id=c.dbid AND b.[NAME] NOT IN ('master')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:57:18

this?


select count(B.[name]),count(c.[HostName]),SUM(c.[CPU])
FROM SYS.SERVERS a,[YourServerName].[MASTER].SYS.DATABASES b, [YourServerName].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]='YourServerName' AND b.database_id=c.dbid AND b.[NAME] NOT IN ('master')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-02-24 : 11:59:55

Thank you so much, I am looking this.

select a.[data_source],count(b.[name]),count(c.[HostName]),sum(c.[cpu])
FROM SYS.SERVERS a,[YourServerName].[MASTER].SYS.DATABASES b, [YourServerName].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]='YourServerName' AND b.database_id=c.dbid AND b.[NAME] NOT IN ('master')
Group by a.[data_source]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 12:04:37

quote:
Originally posted by rudba

Thank you so much, I am looking this.

select a.[data_source],count(b.[name]),count(c.[HostName]),sum(c.[cpu])
FROM SYS.SERVERS a,[YourServerName].[MASTER].SYS.DATABASES b, [YourServerName].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]='YourServerName' AND b.database_id=c.dbid AND b.[NAME] NOT IN ('master')
Group by a.[data_source]

but you didnt specify the data_source field at all in your posted queries??

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources