Thursday, March 25, 2010

Multiple Seartch paremeter

When ever you work on a search procedure with optional parameter,it will be helpful

Select col1,col2,.......coln from table_name where
(nullif(@col1,0) is null or(@
col1>0 and col1 = @col1)
and (nullif(@col2,'') is null or col2=@col2)
and (nullif(@col3,'') is null or

Thursday, August 27, 2009

Grant or Revoke Permissions for all the table

To Grant or Revoke permission for all the objects at a single stroke

Exec sp_MsForEachTable 'Grant Select on ? to Guest'

Remember it will be work for Ms-sql 2005 or more, if 2000 it can be achieved by using while and dynamic sql.

Ms-sql Stored Procedure Manual

After we crate a procedure its must to give the manual to the front end team to make a use of it.

Creating the procedure manual is not a easy task for a bulk amount of procedures. I face this once, to make the work smarter just run the query

select as procedure_name, as parameter, +'('+ cast(p.max_length as varchar(5))+')' as Data_type
from sys.objects o inner join sys.parameters p on o.object_id=p.object_id
inner join sys.types t on p.system_type_id=t.system_type_id
where o.type='p'

Thursday, August 20, 2009

Check whether the time slot is availble?

We face the problem to do some time based or reservation , to check whether the time slot is available or not.

This script will help to do it better and quicker.

Declare @mytable table (id int,fromtime smalldatetime,totime smalldatetime)

insert into
@mytable values (1,'2009-08-20 08:00:00','2009-08-20 12:00:00')

if not exists (select 'x' from
@mytable where '2009-08-20 07:00:00' between fromtime and totime)

print 'Insert statement'


Saturday, August 8, 2009

Random varchar Password generation in My-Sql and Ms-Sql

The simplest way to generate random passwords




SELECT Left(MD5(RAND()),8) AS password

Wednesday, July 29, 2009

Getting the total row count of a table without count()

The count(*) or count(pk) will be slow for large tables.

The alternate way to archive this as fast


FROM sysindexes
WHERE object_name(id) = 'table_name'
AND indid =0

Mssql -2005 or more

SP_SPACEUSED table_name

Thursday, July 23, 2009

Truncate all the table in a DB (MS-Sql 2000 & MS-Sql 2005)

Ms-Sql 2000

declare @SQL varchar(8000)

set @SQL = ''

select @SQL = @SQL + ' truncate table ' + name from sysobjects where type = 'U' and name <> 'dtproperties'


Ms-Sql 2005

Exec sp_MsForEachTable 'TRUNCATE TABLE ?'