Get COMPUTER NAME from SQL Query

Get the HOST NAME / COMPUTER NAME

Get the Computer Name using SQL Query

Select Host_Name() as Computer_Name

Get Computer Name, Get HOst Name

Here is the Way you can get the same in Visual Basic : http://vbadud.blogspot.com/2007/05/get-computer-name.html

Here is the Way you can get the same in .Net :
http://dotnetdud.blogspot.com/2007/06/get-computer-name-in-net.html

Coalesce Function

Coalesce Function

Displays the first nonnull expression among its arguments

-- returns Iam Not a Null
Select Coalesce (Null, Null, 'Iam Not a Null', Null) as NotNull

This function will be used to skip the NULL columns and get the ones that have value. This function can be maximum utilised in situations where you need to get one value from a group of columns that could contain null.

For example, Let us assume that you have a contact table with office_phno, mobile_nos, home_phnos in it and you need to send them greetings. You can use Coalesce Function to get the non null number according to priority

Select Coalesce (Office_phno, mobile_nos, home_phnos, 'No Ph No Available) as ContactNo

CASE Function Example - Database

SQL Server CASE Function

Evaluates a list of conditions and returns one of multiple possible result expressions

Begin
Declare @Position Int;
Set @Position = 3
Select Case @Position
when 1 then 'Gold'
when 2 then 'Silver'
when 3 then 'Bronze'
Else cast(@Position as varchar)+ 'th'
End as Medal
End

You can change the value of @Position and analyse the output

SQL Server CASE Function, CASE Function, CASE Function Example

Know the version of the DB

Database Version - SQL Server/ Sybase

Select @@Version

Print @@Version

Apart from the SQL Server Version, It also provides processor architecture, build date, and operating system for the current installation

The above information can also be retrieved from the following stored procedure

Exec xp_msver

Keywords: Version, xp_msver, System Stored Procedure

Selecting Database users

Selecting Database users


The following queries will be returning the user names

Select User

-- This displays the current user
Select User_Name()

Select Current_User

-- This displays the user belongint to ID 2
Select User_Name(2)

-- Displays all users for that DB
Select uid, name from sysusers order by uid

sysusers contain the information of all users

Check database permission

Check Access to database

Check if user has access to the specified database

HAS_DBACCESS returns 1 if the user has access to the database, 0 if the user has no access to the database, and NULL if the database name is not valid

Begin
Declare @RetValue Int;
Set @RetValue = HAS_DBACCESS('AdventureWorks')
IF @RetValue = 1
Print 'User has Access';
Else
IF @RetValue = 0
Print 'User Does not have Access';
Else
Print 'Database name is not valid';
End

Check for Valid Database Name, DB Name Error, DB Name Validity, User Permissions for DB, User Access for DB, User Rights for DB, SQL Server Check Access to database

NUMBER OF ACTIVE TRANSACTIONS

NUMBER OF ACTIVE TRANSACTIONS

@@TRANCOUNT shows # of active transactions for the current connection

Select @@TRANCOUNT

If you use savepoint_name in ROLLBACK TRANSACTION @@TRANCOUNT will not be affected


Begin
Begin Transaction

Save TRAN Point1 ;
Select 'The Transaction Count is ' + cast(@@TRANCOUNT as varchar) ;

-- This roll Back will not have any impact on @@TRANCOUNT
RollBack Transaction Point1
Select 'The Transaction Count is ' + cast(@@TRANCOUNT as varchar) ;

-- This roll Back will Reset the @@TRANCOUNT
RollBack Transaction
Select 'The Transaction Count is ' + cast(@@TRANCOUNT as varchar) ;
End

Keywords: @@TRANCOUNT, savepoint_name, Number Of Active Transactions