ASP.Net and Sql Server Articles: SQL server funcation

Showing posts with label SQL server funcation. Show all posts

Tuesday, May 6, 2014

How to Delete All Tables-Views-Stored Procedures in SQL Server

/* Drop all non-system stored procs */

DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */ DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Thursday, May 2, 2013

How to find total salary of each employee IN SQL??

I have a department table and i find out to total salary of each employee by department Id.

DeptId       Name      Salary
1            AA        2000 
1            AA       3000 
2            BB       1000

3            cc        10003            cc        4000

sql query

SELECT E.DeptId,ISNULL(SUM(ES.Salary),0) AS Amt FROM Employee E
LEFT OUTER JOIN Employee ES ON E.DeptId=ES.DeptId
GROUP BY E.DeptId

Output

DeptId             Salary
1                   5000 
2                   1000

3                   5000

How to Convert Indian Rupee to Word format

In this Articles ,I would like to show simple function to Convert Indian Money to word format in Indian money .
Pass the input string in function and get the value in word according to Indian rupee.

For example the input value 9,00,000 (Indian Money) The output as follow:

Input value: 9,00,000
Output : 9 Lacs
Source Code

CREATE FUNCTION [dbo].[AmountToWords]

(
@TOTALAMOUNT varchar(max)
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @AMOUNTWORD varchar(500)
DECLARE @LENGTH VARCHAR(200)=0
DECLARE @SECONDWORD VARCHAR(200)
SET @LENGTH=(SELECT LEN(@TOTALAMOUNT))
IF(@LENGTH=4)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Thousand'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Thousand'
END
ELSE IF(@LENGTH=5)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + 'Thousand'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Thousand'
END
ELSE IF(@LENGTH=6)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Lacs'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Lacs'
END
IF(@LENGTH=7)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Lacs'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Lacs'
END
ELSE IF(@LENGTH=8)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00)
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Crore'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Crore'
END
ELSE IF(@LENGTH=9)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Crore'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Crore'
END
ELSE IF(@LENGTH=10)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00)
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Arab'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Arab'
END
ELSE IF(@LENGTH=11)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Arab'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Arab'
END
RETURN @AMOUNTWORD
END

Output

Select dbo.AmountToWords('900000') as Money

Output : 9 Lacs