Comments on snippet: 'Sql Queries'

hairajeshk said on 11/Jan/2011

Tue, 11 Jan 2011 16:20:26 UTC

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

hairajeshk said on 11/Jan/2011

Tue, 11 Jan 2011 16:22:51 UTC

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

hairajeshk said on 02/Feb/2011

Wed, 02 Feb 2011 20:23:03 UTC

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

hairajeshk said on 18/Feb/2011

Fri, 18 Feb 2011 19:32:17 UTC

Check for both NULL and Empty in one statement:: IF ISNULL(@text, '') = ''

hairajeshk said on 15/Mar/2011

Tue, 15 Mar 2011 18:05:01 UTC

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 )

hairajeshk said on 15/Mar/2011

Tue, 15 Mar 2011 20:15:14 UTC

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 au_lname, au_fname, phone FROM authors WHERE au_lname LIKE '[CK]ars[eo]n' ORDER BY au_lname ASC, au_fname ASC GO