Get all tables name of database

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

Or

SELECT *
FROM SYS.TABLES

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in) Hope you liked this and let me know your thoughts on post through your comments :)

Read More

Meaning of decimal(18,2) Ms Sql Server

The actual syntax of the datatype is decimal(p,s).
So here, p is the precision and s is the scale.


The datatype Decimal(18,2)is used to represent numbers.The length of numbers should be totally 18. The length of numbers after the Decimal point should be 2 only and not more than that.


1234567898822222.88


The numbers to the left of decimal point should not be greater than 16.


The numbers to the right of decimal point should not be greater than 2.
(.) is excluded here in the length.


So,the overall length of the number cannot exceed the length 18.

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in)

Read More

Get last inserted ID in sql server

SELECT IDENT_CURRENT(‘tablename’)

1. It returns the last IDENTITY value produced in a table, 
2. IDENT_CURRENT is limited to a specified table.
3. IDENT_CURRENT returns the identity value generated for a specific table.
   

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in/), SqlAuthority

Read More

Display Number with Commas in SQL

-- Test this function : 
-- SELECT dbo.NumericToCurrency (1116548238,'US') AS RetValue
-- SELECT dbo.NumericToCurrency (10000,'IND') AS RetValue
-- For Indian Format - 'IND', FOR US Format - 'US'

CREATE FUNCTION [dbo].[NumericToCurrency]
( 
   @InNumericValue MONEY
  ,@InFormatType  VARCHAR(10)
)

RETURNS VARCHAR(50)

AS
BEGIN

 DECLARE   @RetVal  VARCHAR(50)
    ,@StrRight  VARCHAR(5) 
    ,@StrFinal  VARCHAR(50) 
    ,@StrLength  INT
    
 SET   @RetVal = ''
 
 SET  @RetVal = @InNumericValue 
 SET  @RetVal = SUBSTRING(@RetVal,1,CASE WHEN CHARINDEX('.', @RetVal)= 0 THEN LEN(@RetVal) 
            ELSE CHARINDEX('.', @RetVal)-1 END) 
 
 IF(@InFormatType = 'US')
 BEGIN
  SET  @StrFinal = CONVERT(VARCHAR(50), CONVERT(money, @RetVal) , 1)
  SET  @StrFinal = SUBSTRING(@StrFinal,0,CHARINDEX('.', @StrFinal))
 END
 
 ELSE
 IF(@InFormatType = 'IND')
 BEGIN
  SET  @StrLength = LEN(@RetVal)
  IF(@StrLength > 3)
  BEGIN
   SET  @StrFinal = RIGHT(@RetVal,3)  
   SET  @RetVal  = SUBSTRING(@RetVal,-2,@StrLength)
   SET  @StrLength  = LEN(@RetVal)
   IF (LEN(@RetVal) > 0 AND LEN(@RetVal) < 3)
     BEGIN
      SET  @StrFinal = @RetVal + ',' + @StrFinal
     END
   WHILE LEN(@RetVal) > 2
     BEGIN
      SET  @StrRight = RIGHT(@RetVal,2)   
      SET  @StrFinal = @StrRight + ',' + @StrFinal
      SET  @RetVal  = SUBSTRING(@RetVal,-1,@StrLength)
      SET  @StrLength = LEN(@RetVal)
      IF(LEN(@RetVal) > 2) 
      CONTINUE
      ELSE
      SET  @StrFinal = @RetVal + ',' + @StrFinal
      BREAK
     END
  END
  ELSE
  BEGIN
   SET @StrFinal = @RetVal
  END

 END
 
 SELECT @StrFinal = ISNULL(@StrFinal,00)
  
 RETURN @StrFinal
END

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in/)

Read More

Undo Delete Command

While working with live data it is important that you work with full concentration, else it may cost you like anything.
Here I am sharing one of my colleagues expirence when he was working on live data.
While deleting some record with WHERE clause, he forgot to select the whole WHERE clause on executing the query... and what!!! all of the data from live data is gone..

How to rollback that data now???

What I did on my case is,
1. Find the log file path of that database by right clicking on the database > properties then Select Files from the left pane and noted the log file path (both .mdf and .ldf).
2. Downloaded a tool to read sql log file from internet which you can get from here and install the same.
3. Now to read the file, you need to first make your database offline. For that right click on database > task > Take Offline.
4. Open Kernal for SQL Database and open your log file (.mdf) and then click on recover.
5. That is you will get all your data. Now you can do what ever you like to do with the data.

Special Thanks to Pinal Dave

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in)

Read More

Get Last Executed Query on SQL Server 2005

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in) , SQL SERVER – 2005 – Last Ran Query – Recently Ran Query

Read More

Split Function

Sql Server does not have in-build Split function.
To achive the same I have created below function.

/****** Object:  UserDefinedFunction [dbo].[fn_Split] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_Split]
(
     @InputStr VARCHAR(MAX) -- List of delimited items
    ,@SplitChar CHAR -- delimiter that separates items 
)
RETURNS @Splittings TABLE
(
     Position INT
    ,Val VARCHAR(20)
)
AS
BEGIN

    DECLARE @Index INT, @LastIndex INT, @SNo INT
    
    SET @LastIndex = 0
    SET @Index = CHARINDEX(@SplitChar, @InputStr)
    SET @SNo = 0
    
    WHILE @Index > 0
    BEGIN  
  SET @SNo = @SNo + 1
        INSERT INTO @Splittings(Position, Val)
        VALUES(@SNo, LTRIM(RTRIM(SUBSTRING(@InputStr, @LastIndex, @Index - @LastIndex)))) 
 
        SET @LastIndex = @Index +1
        SET @Index = CHARINDEX(@SplitChar, @InputStr, @LastIndex)
    END
    SET @SNo = @SNo + 1
    INSERT INTO @Splittings(Position, Val)
    VALUES(@SNo, LTRIM(RTRIM(SUBSTRING(@InputStr, @LastIndex, LEN(@InputStr) - @LastIndex + 1))))
    
    RETURN
END

To Call the Function you can use below query

SELECT * FROM dbo.fn_Split('Chennai,Bangalore,Mumbai',',')  

Read More

Convert Table Column to Property

Instead of creating each and every property, below query will help you created property on single fire.
Just replace "TABLENAME" with you table name.

DECLARE @COLUMN_NAME varchar(250)
DECLARE @DATA_TYPE varchar(250)
DECLARE c1 CURSOR FOR

SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns
where table_name = 'TABLENAME'
OPEN c1
FETCH NEXT FROM c1 INTO @COLUMN_NAME, @DATA_TYPE
WHILE @@FETCH_STATUS = 0
BEGIN

IF @DATA_TYPE = 'nvarchar' OR @DATA_TYPE = 'ntext' OR @DATA_TYPE = 'varchar'
BEGIN
    SET @DATA_TYPE = 'string'
END

IF @DATA_TYPE = 'datetime'
BEGIN
    SET @DATA_TYPE = 'DateTime'
END

DECLARE @pvar  VARCHAR(100)
SET @pvar = ' _' + @COLUMN_NAME
PRINT 'private ' + @DATA_TYPE + @pvar + ' ;'
PRINT 'public ' + @DATA_TYPE + ' ' + @COLUMN_NAME + ' {get{return '+ @pvar +';} set{'+ @pvar+'=value;} }'

FETCH NEXT FROM c1 INTO @COLUMN_NAME, @DATA_TYPE

END
CLOSE c1
DEALLOCATE c1
GO

In the query above I have only added few data type. People can add more data type as per their need.

Read More

Distinct not working in Sql Server

Once I was trying to get distinct record from database, i found that my query was not working as of my expectation.
My table was like below
ProductId   ProductName   CreatedDate

----------   --------------   --------------
A001        Sample Data      2012-03-25 23:31:26.580
A001        Sample Data      2012-04-01 14:11:09.483

A002        Sample Data      2012-04-14 15:51:30.640

And the query which I wrote to fetch distinct record was as below

SELECT DISTINCT ProductId, ProductName, CreatedDate FROM tbl_Product

Reason behind the improper output was "DISTINCT removes redundant duplicate rows", to know more about DISTINCT click here

If you see my table you can find difference on CreatedDate of A001 record. Which means that row is not the duplicate.

In order to achieve proper output in such a case, use any of the below queries

SELECT P.ProductId, P.ProductName, MAX(P.CreatedDate) AS CreatedDate
FROM tbl_Product P
GROUP BY P.ProductId

OR

SELECT P1.ProductId, P1.ProductName, P1.CreatedDate
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY P.ProductId ORDER BY P.ProductId) AS RowNo,
P.ProductId, P.ProductName, P.CreatedDate
    FROM tbl_Product P
) P1
WHERE P1.RowNo = 1 

Read More

From and To Date Filtering

I have been recently creating few reports that required dates as parameters. It is quite common to have a report that must be provided with from and to date range.

When I see two dates that must be provided to a report I believe that when I provide the same date in from and to parameters it will show all data for the particular day. 

But in my case it failed. After going through different queries, I came up with a solution which is below

SELECT *
FROM tbl_Employee 
WHERE DOJ >= DATEADD(dd, 0, DATEDIFF(dd, 0, @InFromDate))
    AND DOJ < DATEADD(dd, 1, DATEDIFF(dd, 0, @InToDate))

Read More

Difference between Trigger and Stored Procedure

1. Stored procedures cannot run automatically, they have to be called explicitly by the user.But triggers are executed automatically when particular event (like insert, update, delete) associated within the database object (like table) gets fired. 
2. Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.
3. Stored procedure can accept the parameters from users whereas trigger cannot accept parameters from users.
4. We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can't use the transaction statements inside a trigger. 
5. A Trigger can call the specific Stored Procedures in it, but the reverse is not true.

Read More