COMPLETE ASP NET TUTORIAL

Showing posts with label Sql Tutorial. Show all posts

Tuesday, February 11, 2014

Drop all tables, stored procedure, views and triggers

Sometimes, there is a case, when we need to remove all tables, stored procedure, views and triggers completely from the database. If you have around 100 tables, stored procedure and views in your database, to remove these, completely from database became a tedious task. In this article, I would like to share the script by which you can remove tables, stored procedure, views and triggers completely from database.
Remove all Tables
 -- drop all user defined tables
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" 
Remove all User-defined Stored Procedures
 -- drop all user defined stored procedures
Declare @procName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'p' 
Open cur 
Fetch Next From cur Into @procName 
While @@fetch_status = 0 
Begin 
 Exec('drop procedure ' + @procName) 
 Fetch Next From cur Into @procName 
End
Close cur 
Deallocate cur 
Remove all Views
 -- drop all user defined views
Declare @viewName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'v' 
Open cur 
Fetch Next From cur Into @viewName 
While @@fetch_status = 0 
Begin 
 Exec('drop view ' + @viewName) 
 Fetch Next From cur Into @viewName 
End
Close cur 
Deallocate cur 
Remove all Triggers
 -- drop all user defined triggers
Declare @trgName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'tr' 
Open cur 
Fetch Next From cur Into @trgName 
While @@fetch_status = 0 
Begin 
 Exec('drop trigger ' + @trgName) 
 Fetch Next From cur Into @trgName 
End
Close cur 
Deallocate cur

Thursday, January 30, 2014

SQL SERVER – 2008 – Creating Full Text Catalog and Full Text Search

Create table content 
(
Pagename varchar(20) not null primary key,
URL varchar(30) not null,
Description text null,
Keywords varchar(4000) null
)
INSERT content values ('home.asp','home.asp','This is the home page','home,SQL')
GO
INSERT content values ('pagetwo.asp','/page2/pagetwo.asp','NT Magazine is great','second')
GO
INSERT content values ('pagethree.asp','/page3/pagethree.asp','SQL Magazine is the greatest','third')
GO

To create an Index, follow the steps:

1. Create a Full-Text Catalog

2. Create a Full-Text Index

3. Populate the Index

1) Create a Full-Text Catalog

Following Screenshot shown as AdventureWorks2008 - follow the same in ur Db.

Full – Text can also be created while creating a Full-Text Index in its Wizard.

2) Create a Full-Text Index

3) Populate the Index


SELECT Description, Keywords
FROM content
WHERE FREETEXT(*, 'second');
SELECT Description, Keywords
FROM content
WHERE FREETEXT((Description,Keywords), 'SQL');
SELECT Description, Keywords
FROM content
WHERE CONTAINS(*, 'SQL');
SELECT Description, Keywords
FROM content
WHERE CONTAINS(*, 'SQL');
(Note : If you dont have a primary key,Full text Index cannot be created).

Monday, January 27, 2014

SQL CASE Function

DECLARE @Type varchar(20)
SET @Type = 'Programming'
SELECT 
   CASE @Type 
      WHEN 'Sql' THEN 'sqltutorials.blogspot.com' 
      WHEN 'Programming' THEN 'programmingschools.blogspot.com' 
      WHEN 'Travel' THEN 'travelyourself.blogspot.com' 
      ELSE 'Not yet categorized' 
      END

Value = programmingschools.blogspot.com

If SET @Type = 'Picture', then Return value = Not yet categorized

link : http://sqltutorials.blogspot.in/2007/06/sql-case-function.html

Sunday, January 26, 2014

Dynamic Query in sql server

Create table #info (id int,Sname varchar(50))
Create table #Post (Post_id int, id int,post varchar(50))
Create table #city (city_id int,id int,city varchar(100))

   insert into #info(id,Sname) values(1,'sandip.')
   insert into #info(id,Sname) values(2,'sandip_SP')
   insert into #info(id,Sname) values(3,'sandip_Ak')
   insert into #info(id,Sname) values(4,'sandip_Sk')
   insert into #info(id,Sname) values(5,'sandipG')
   insert into #Post(Post_id ,id,post) values(1,1,'Software Dev')
   insert into #Post(Post_id ,id,post) values(2,2,'Database admin')
   insert into #Post(Post_id ,id,post) values(3,3,'tester')
   insert into #Post(Post_id ,id,post) values(4,4,'HR')
   insert into #Post(Post_id ,id,post) values(5,5,'Team Lead')
   insert into #city (city_id,id,city) values (1,1,'kolhapur')
   insert into #city (city_id,id,city) values (1,1,'sangli')
   insert into #city (city_id,id,city) values (1,1,'satara')
   insert into #city (city_id,id,city) values (1,1,'karad')
   insert into #city (city_id,id,city) values (1,1,'maharastra')

     declare @main_str varchar(max)
     declare @query_str varchar(max)
     declare @InnerJoin_str varchar(max)
     declare @Where_str varchar(max)
     set @query_str =' select * from #temp a ' 
     set @InnerJoin_str =' inner join #post b on a.id=b.id '
     set @Where_str= 'Where a.id=4 '
     set @main_str=''
     set @main_str=@query_str+@InnerJoin_str+@Where_str
     exec(@main_str)
    --Add Dynamacally where clause in sql query
    set @Where_str=@Where_str+ ' or b.post=''Software Dev'''
    set @main_str=''
    set @main_str=@query_str+@InnerJoin_str+@Where_str
    exec(@main_str)
 --Add Dynamacally joins in sql Query
   set @InnerJoin_str=@InnerJoin_str+ ' inner join #city c on a.id=c.id '
   set @main_str=''
   set @main_str=@query_str+@InnerJoin_str+@Where_str
   exec(@main_str)

link:http://sandipgsql.blogspot.in/2013/05/dynamic-query-in-sql-server.html

select Group_by Comma Separated

Hi ,
In this article we will see how to get a group by comma separated values sql server.

Check Below example ::

step1:: Create table and fill some data in that table.


Create table #Group_by_CommaSeprated(id int ,Hosp_name varchar(50),City varchar(50))
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'sandip','mumbai')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'raj','pune')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'ajit','mumbai')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(1,'aditya','nashik')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'rachit','bhopal')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'rakesh','bihar')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'shrikant','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(2,'chandan','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'dishal','MP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'saket','UP')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'pranav','sangli')
insert into #Group_by_CommaSeprated (id,Hosp_name,City )values(3,'aditya','kolhapur')

step 2:: Check your data .

select * from #Group_by_CommaSeprated

Step 3:: Select Query for get a group by comma separated values sql server.


SELECT id, Hosp_name = 
STUFF((SELECT ', ' + Hosp_name
FROM #Group_by_CommaSeprated b
WHERE b.id = a.id 
FOR XML PATH('')), 1, 2, ''), City = 
STUFF((SELECT ', ' + City
FROM #Group_by_CommaSeprated b 
WHERE b.id = a.id 
FOR XML PATH('')), 1, 2, '')
FROM #Group_by_CommaSeprated a
GROUP BY id

Step 4:: OUTPUT

link: http://sandipgsql.blogspot.in/search/label/data%20in%20comma%20separated%20string

Sql - Comma separated string into table


CREATE TABLE tblEmployee(
EmployeeId          INT,
Name                VARCHAR(30),
Gender              VARCHAR(30),
City                VARCHAR(4000))
GO
DELETE FROM tblEmployee
GO
INSERT INTO tblEmployee(EmployeeId, Name, Gender, City) VALUES(1, 'Mark', 'Male', 'London')
GO
INSERT INTO tblEmployee(EmployeeId, Name, Gender, City) VALUES(2, 'John', 'Male', 'Chennai')
GO
INSERT INTO tblEmployee(EmployeeId, Name, Gender, City) VALUES(3, 'Mary', 'FeMale', 'New york')
GO
INSERT INTO tblEmployee(EmployeeId, Name, Gender, City) VALUES(4, 'Mike', 'Male', 'Sydney')
GO

Function:
------------
CREATE FUNCTION [dbo].[CsvToIntTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [Id] int )
AS
BEGIN
    DECLARE @String    VARCHAR(20)
    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))
        INSERT INTO @OutputTable ( [Id] )
        VALUES ( cast(@String as int) )
    END
    
    RETURN
END


Declare @EmpId nvarchar(100);
Set @EmpId = '1,2'
Select * 
from dbo.tblEmployee
where EmployeeId IN (SELECT * FROM [dbo].[CsvToIntTable](@EmpId))

Saturday, July 13, 2013

sql dynamic order by

--Dynamic Order By

create table Bankers(
    BankerID             Integer,
    BankerName           VARCHAR(20),
    BankerContactLName   VARCHAR(20),
    BankerContactFName   VARCHAR(20),
    BankerCity           VARCHAR(20),
    BankerState          VARCHAR(20),
        BankerZipCode        VARCHAR(20),
    BankerPhone          VARCHAR(20)
 )
GO


insert into Bankers values (1, 'ABC Inc.','Joe','Smith','Vancouver','BC','11111','111-111-1111');
insert into Bankers values (2, 'DEF Inc.','Red','Rice', 'New York', 'DE','22222','222-222-2222');
insert into Bankers values (3, 'HJI Inc.','Kit','Cat',  'Paris',    'CA','33333','333-333-3333');
insert into Bankers values (4, 'QWE Inc.','Git','Black','Regina',   'ER','44444','444-444-4444');
insert into Bankers values (5, 'RTY Inc.','Wil','Lee',  'Toronto',  'YU','55555','555-555-5555');
insert into Bankers values (6, 'YUI Inc.','Ted','Larry','Calgary',  'TY','66666','666-666-6666');
insert into Bankers values (7, 'OIP Inc.','Yam','Act',  'San Franc','FG','77777','777-777-7777');
insert into Bankers values (8, 'SAD Inc.','Hit','Eat',  'Orland',   'PO','88888','888-888-8888');
 insert into Bankers values (9, 'DFG Inc.','Sad','Lee',  'Wisler',   'PL','99999','999-999-9999');
 insert into Bankers values (0, 'GHJ Inc.','Bit','Lee',  'Ticker',   'MN','00000','000-000-0000');
 
 


 DECLARE @ORDERBY  VARCHAR(20)
 SET @ORDERBY =  1
 
 SELECT BankerName,
  BankerCity + ', ' + BankerState + ' ' + BankerZipCode AS ADDRESS
 FROM Bankers
ORDER BY CASE
WHEN @OrderBY = 1 THEN BankerName 
WHEN @OrderBY = 2 THEN BankerCity 
WHEN @OrderBY = 3 THEN BankerState 
WHEN @OrderBY = 4 THEN BankerZipCode END DESC

Monday, August 13, 2012

SQL Write CASE STATEMENT IN WHERE CONDITION

Write CASE STATEMENT IN WHERE CONDITION
--------------------------------------------
create table Billings (
     BankerID           INTEGER,
     BillingNumber      INTEGER,
     BillingDate        datetime,
     BillingTotal       INTEGER,
    TermsID            INTEGER,
     BillingDueDate     datetime ,
    PaymentTotal       INTEGER,
     CreditTotal        INTEGER
 );
 GO
 
 INSERT INTO Billings VALUES (1, 1, '2005-01-22', 165, 1,'2005-04-22',123,321);
 INSERT INTO Billings VALUES (2, 2, '2001-02-21', 165, 1,'2002-02-22',123,321);
 INSERT INTO Billings VALUES (3, 3, '2003-05-02', 165, 1,'2005-04-12',123,321);
 INSERT INTO Billings VALUES (4, 4, '1999-03-12', 165, 1,'2005-04-18',123,321);
 INSERT INTO Billings VALUES (5, 5, '2000-04-23', 165, 1,'2005-04-17',123,321);
 INSERT INTO Billings VALUES (6, 6, '2001-06-14', 165, 1,'2005-04-18',123,321);
 INSERT INTO Billings VALUES (7, 7, '2002-07-15', 165, 1,'2005-04-19',123,321);
 INSERT INTO Billings VALUES (8, 8, '2003-08-16', 165, 1,'2005-04-20',123,321);
 INSERT INTO Billings VALUES (9, 9, '2004-09-17', 165, 1,'2005-04-21',123,321);
 INSERT INTO Billings VALUES (0, 0, '2005-10-18', 165, 1,'2005-04-22',123,321);
 
DECLARE @BANKERID varchar(100)
Set @BANKERID = 'test'
SELECT BankerID,BillingNumber, BankerID * (SELECT COUNT(BankerID) FROM Billings) 
FROM Billings
WHERE     
BANKERID = CASE(@BANKERID)
  WHEN 'test' THEN 1
 -- WHEN 2 THEN 2
 END 
AND BillingNumber = 2
------------------------------------------------------------------------------------------------------
DECLARE @BANKERID varchar(100)
Set @BANKERID = 'test'
SELECT BankerID,BillingNumber, BankerID * (SELECT COUNT(BankerID) FROM Billings) 
FROM Billings
WHERE    
 BillingNumber = 2 AND
 
 @BANKERID =
CASE @BANKERID
  WHEN 'test' THEN 'test'
 -- WHEN 2 THEN 2
 END 
 
 
 --------------------------------------------------------------------------------------------------------
 
 DECLARE @BANKERID varchar(100)
Set @BANKERID = 'test'
SELECT @BANKERID,BankerID,BillingNumber, BankerID * (SELECT COUNT(BankerID) FROM Billings) 
FROM Billings
WHERE    
 BillingNumber = 2 AND
 
 @BANKERID =
CASE @BANKERID
  WHEN 'test' THEN 'test'
 -- WHEN 2 THEN 2
 END

Pages

COMPLETE ASP NET TUTORIAL

Tuesday, February 11, 2014

Drop all tables, stored procedure, views and triggers

Remove all Tables

Remove all User-defined Stored Procedures

Remove all Views

Remove all Triggers