Now () Function in SQL Server

Now () Function in SQL Server 2005

Logs, Status Reports and et al., Now() function is common. Here is the way to accomplish the same in SQL Server

Select @Today = GetDate() should give you the same

DatePart - Get Part of the Date

-- Selecting the Current Year/Get Current Year/Separate Year Part from Date
Select DatePart(YY, GetDate()) as Current_Year

-- Selecting the Current Quarter/Get Current Quarter/Separate Quarter Part from Date
Select DatePart(QQ, GetDate()) as Current_Quarter

-- Selecting the Current Month/Get Current Month/Separate Month Part from Date
Select DatePart(MM, GetDate()) as Current_Month

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(month, GetDate()) as Current_Month_Name

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(day, GetDate()) as Current_Day

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(wk, GetDate()) as Current_Week

Posted by Shasur at 5:14 PM 0 comments

Link Triggers & Tables

Triggers and thier corresponding Tables

-- Sybase
select * from systrigger

-- SQL Server
select * from sys.triggers

-- Triggers and Tables
If you want to know the triggers for each table or trigger for a
corresponding table use the following:

-- Sybase
Select stab.table_name, strig.trigger_name from systable stab, systrigger strig
where stab.table_id = strig.table_id

-- SQL Server
Select stab.name, strig.name from sys.tables stab, sys.triggers strig
where stab.object_id = strig.parent_id

Stored Procedures & Parameters

SQL Server / Sybase Stored Procedures & Parameters

-- Sybase Syntax to Select Parameters used in All Available Stored Procedures
select * from SYSPROCPARM

-- SQL Server Syntax to Select Parameters used in All Available Stored Procedures
select * from sys.parameters


-- Sybase Syntax to Select All Available Stored Procedures
select * from sysprocedure

-- SQL Server Syntax to Select All Available Stored Procedures
select * from sys.procedures


-- To get the list of both input and output parameters used in every
stored procedure use the following syntax

-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
order by sp.proc_name

--SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
order by sp.name


-- Procedures and their Input Parameters

-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
and parm_mode_in = 'Y'
order by sp.proc_name


-- SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
and spa.is_output = 0
order by sp.name



-- Procedures and their Output Parameters
-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
and parm_mode_out = 'Y'
order by sp.proc_name

-- SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
and spa.is_output = 1
order by sp.name

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

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

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

Maximum Level of Nesting in SQL Server Stored Procedure

Maximum Level of Nesting in SQL Server Stored Procedure

A Maximum of 32 levels of Nesting is allowed in SQL Server Stored Procedure. When the maximum of 32 is exceeded, the transaction is terminated.

Select @@NestLevel

Returns the nesting level of the current stored procedure execution (initially 0) on the local server

Keywords: @@NestLevel, Maximum Level of Nesting

First day of the week - @@datefirst

The first day of the week will be 7 - Sunday (default for US English)

Select @@datefirst as FirstDay

You can set it by :
Set datefirst 1;

Current Database - SQL Server

What is the Current Database?

Select DB_NAME() as Curr_DB

Monitoring SQL SErver with SP_WHO

MONITORING SQL SERVER WITH SP_WHO

Exec sp_who

will provide information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine

Use the login to filter results for a particular user
Exec sp_who 'Login21'