Find the size of all databases at once

You may run into cases where you have to find the size of all the databases in a server in less time...
This will be easy and quick when you have less databases on the box.
What happens if the box has more number of databases??? Here is a quick solution for it...

Run the below Query and get your results in less time and in one shot.

EXEC sp_msforeachdb 'Use [?]
Declare @dbsize float
Declare @logsize float
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles
select ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2) + '' MB'') AS [Size of ?]'

Finding Cluster Nodes or Cluster Name

Here is a Query to find the Cluster Nodes or Cluster Name using SQL server 2005.

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

This helps in  finding which node the instance is currently running.

Just for Laughs :) :) :) Marriage invitation in SQL Server Stored Procedure Style

CREATE PROCEDURE MyMarriage
@BrideGroom Char(NotBad),
@Bride Char(Good)
AS

SELECT Bride FROM
BridesList
WHERE
FatherInLaw = 'Millionaire'
AND CarCount > 2
AND HouseStatus ='TwoStoreyed'
AND BrideEduStatus='PG_or_Above'
AND HavingBrothers='NO'
AND HavingSisters ='No'
AND AllowRelocate ='YES'

SELECT Gold ,Cash,Car,BankBalance FROM FatherInLaw

UPDATE MyBankAccout SET MyBal = MyBal + FatherinLawBal
UPDATE MyLocker SET MyLockerContents = MyLockerContents + FatherinLawGold

INSERT INTO MyCarShed VALUES ('Ford')

@@CONNECTIONS and @@MAX_CONNECTIONS

@@CONNECTIONS returns the number of attempted connections, either successful or unsuccessful since SQL Server was last started.Connections are different from users. For example, Applications can open multiple connections to SQL Server without the user observing the connections.

Syntax: - Select @@CONNECTIONS

@@MAX_CONNECTIONS is the maximum number of connections allowed simultaneously to the SQL server. The number returned is not necessarily the number currently configured. The actual number of user connections allowed also depends on the version of SQL Server installed and the limitations of your applications and hardware.

To Change the max connections, Use sp_configure

Syntax: - Select @@MAX_CONNECTIONS

@@CONNECTIONS is incremented with each login attempt, therefore @@CONNECTIONS can be greater than @@MAX_CONNECTIONS.

 

Example: -

SELECT GETDATE() AS 'Current Date and Time',

@@CONNECTIONS AS 'Login Attempts',

@@MAX_CONNECTIONS AS 'Max Connections Allowed'

Code Names of SQL server releases

         SQL Server Release          Project Code Name

 

1. SQL Server 2008                Katmai
2. SQL Server 2005                Yukon
3. SQL Server 2000 64 bit       Liberty
4. SQL Server 2000 32 bit       Shiloh
5. SQL Server 7.0                   Sphinx
6. SQL Server 6.5                   Hydra
7. SQL Server 6.0                   SQL95