advanced code snippet search

Posted By

hairajeshk on 12/29/10


Tagged

sql all used queries frequently


Versions (?)

Sql Queries

 / Published in: SQL
 

URL: all frequently used SQL Queries

all frequently used SQL Queries

Expand | Embed | Plain Text 
  1. TO get the date difference BETWEEN two successive rows:
  2.  
  3. SELECT DATEDIFF(second,A.StartDate,B.StartDate)
  4. FROM #temp A
  5. INNER JOIN #temp B
  6. ON A.sno=B.sno-1

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: hairajeshk on January 11, 2011

Drop all stored procs using Cursor: Alter Procedure dbo.DeleteAllProcedures As 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
        if @procName  'DeleteAllProcedures'
              exec('drop procedure ' + @procName)
              fetch next from cur into @procName
  end
  close cur
  deallocate cur

Go Grant Execute On dbo.DeleteAllProcedures To Public Go

Posted By: hairajeshk on January 11, 2011

loop through all/selected databases using spMSforeachdb system stored procedure or loop through all/selected user tables using spMSforeachtable system stored procedure: http://www.mssqltips.com/tip.asp?tip=1905

Posted By: hairajeshk on February 2, 2011

To find a column in all the tables of database SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'Pricelistname' )

Posted By: hairajeshk on February 18, 2011

Check for both NULL and Empty in one statement::

IF ISNULL(@text, '') = ''

Posted By: hairajeshk on March 15, 2011

Update 0 with ones and 1 with 0 in a single query

Update #tblx SET x= ( case when x=1 then 0 when x=0 then 1
else x end )

Posted By: hairajeshk on March 15, 2011

Returns all the customer names which doesn't start with alphabet

select customername from customer where customername like '[^A-Z]%' This example finds the rows for authors with last names of Carson, Carsen, Karson, or Karsen. USE pubs GO SELECT aulname, aufname, phone FROM authors WHERE aulname LIKE '[CK]ars[eo]n' ORDER BY aulname ASC, au_fname ASC GO

You need to login to post a comment.