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

Difference Between Stored Procedure and Function

Fundamental difference between Stored procedure vs User Functions:

1. Procedure may return none or more values.Function must always return one value, either a scalar value or a table.
2. Procedure have input,output parameters.Functions have only input parameters.
3. procedures are called independently whereas Functions are called from within SQL statement.
4. Functions can be called from procedure.Procedures cannot be called from function.
5. Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
6. Transaction management possible in procedure but not in function.
7. Procedure are compiled for first time and compiled format is saved and executes compiled code when ever it is called. But function is compiled and executed every time it is called.
8. Procedures can affect the state of the database by using insert,delete,update and create operations.Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.
9. Procedures can change server environment whereas Function can not change server environment.
10. Cannot JOIN a Procedure in a SELECT statement.Can JOIN a Table values UDF in a SELECT statement.

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