Wednesday, July 13, 2005

How to find Perticuler Words or Sentences in Procedure/Function

How to find dependent Objects (Sproc, Function or View) or How to find Perticuler Words or sentences in Procedure/Function
 
For Example : If you want to Search the words "Modified By : Ritesh Kesharwani" in All Used Objects like Function, Procedure, View or Triggrs.
 
Execute below Procedure in your SQL Server. to Run that Procedure User must have Administrator rights.
 
--------------------------------------------------------------------------------------------------------------------------------------
Create proc ALL_sp_depends 
@p_c1 nvarchar(100), 
@p_c2 nvarchar(100) = '', 
@p_c3 nvarchar(100) = '' 
 
as 
 
begin 
set nocount on 
print @p_c2 
print @p_c3 
 declare @l_objname nvarchar(100), @l_Objid int, @l_type as nvarchar(10) 
 declare @l_text varchar(8000), @l_fnd1 bit, @l_fnd2 bit, @l_fnd3 bit 
 declare @l_objCnt int, @l_commCnt int 
 declare @l_totObjCnt int, @l_TotCommCnt int 
 select @l_objCnt = 1, @l_commCnt = 1 
 select @p_c1 = '%' + @p_c1 + '%', @p_c2 = case when @p_c2 in('',null) then '' else '%' + @p_c2 + '%' end,  
    @p_c3 = case when @p_c3 in('',null) then '' else '%' + @p_c3 + '%' end 
 
 declare @output table(ObjectName nvarchar(200), Type nvarchar(50)) 
 declare @sysobjs table (tid int identity(1,1) primary key, objname nvarchar(200), objid int, type nvarchar(10)) 
 create table #sysComments(tid int identity(1,1) primary key, ObjText varchar(8000)) 
 
 insert into @sysobjs select name, id, Type from sysobjects 
 set @l_totObjCnt = @@rowcount 
 
 while @l_objCnt <= @l_totObjCnt 
 begin 
  select @l_objname = objname, @l_Objid = objid,@l_type = type  from @sysobjs where tid = @l_objCnt  
 
  set @l_commCnt = 1 
  delete from #sysComments 
  DBCC CHECKIDENT (#sysComments, RESEED, 0) 
  insert into #sysComments select text from syscomments where id = @l_Objid 
  set @l_TotCommCnt = @@rowcount 
 
  while @l_commCnt <= @l_TotCommCnt 
  begin 
   set @l_text = '' 
   select @l_text = ObjText from #sysComments where tid = @l_commCnt 
 
   if patindex(@p_c1, @l_text) > 0  
    set @l_fnd1 = 1 
 
   if @p_c2 = ''  
    set @l_fnd2 = 1 
   else if patindex(@p_c2, @l_text) > 0  
    set @l_fnd2 = 1 
 
   if @p_c3 = ''  
    set @l_fnd3 = 1 
   else if patindex(@p_c3, @l_text) > 0  
    set @l_fnd3 = 1 
    
   if @l_fnd1 = 1 and @l_fnd2 = 1 and @l_fnd3 = 1 break 
 
   set @l_commCnt = @l_commCnt + 1 
  end 
  if @l_fnd1 = 1 and @l_fnd2 = 1 and @l_fnd3 = 1 
  begin 
   insert into @output values(@l_objname,  
    case @l_type  when 'C' then 'CHECK constraint' 
         when 'D' then 'Default or DEFAULT constraint' 
         when 'F' then 'FOREIGN KEY constraint' 
         when 'FN' then 'Scalar function' 
         when 'IF' then 'Inlined table-function' 
         when 'K' then 'PRIMARY KEY or UNIQUE constraint' 
         when 'L' then 'Log' 
         when 'P' then 'Stored procedure' 
         when 'R' then 'Rule' 
         when 'RF' then 'Replication filter stored procedure' 
         when 'S' then 'System table' 
         when 'TF' then  'Table function' 
         when 'TR' then 'Trigger' 
         when 'U' then 'User table' 
         when 'V' then 'View'  
         when 'X' then 'Extended stored procedure' 
         else 'Other' end  
    ) 
  end 
  select @l_fnd1 = 0, @l_fnd2 = 0, @l_fnd3 = 0 
  set @l_objCnt = @l_objCnt + 1 
 end 
 drop table #sysComments 
 select * from @output 
end  
 
-------------------------------------------------------------------------------------------------------------------------------------- 
 
How To Use This Function:
 
Exec ALL_sp_depends 'Modified By : Ritesh Kesharwani'
It will give you ObjectName and Type of the procedure/ view/function where that strings used.
 
this function can take three parameter like table name , column name and other , last two parameter is not mandatory.
 


Have a Nice Day
 
Ritesh Kumar Kesharwani
Software Professional (MCAD)
Cell  :  011-91-9845657636


Start your day with Yahoo! - make it your home page

How to split the String in SQL Server like IndexOf function

Some time you need to split the String in SQL Server. for example If you are sending Concate String to Store Procedure that time you need to split the string inside the procedure use thisfunction to split each string with perticuler dissimulator (like , ! @ # $ % ^ & * ( )).
This function will return you String which you want. and it will return null if dissimulator not present.
 
Flexibility : You can use this function as IndexOf Function as Other Language
 
Execute below Function in your SQL Query Analyser
-------------------------------------------------------------------------------
Create function dbo.Function_split( 
 @p_value  varchar(4000),  --Pass String
 @p_pos  int,   --Position
 @p_deli  varchar(1)) returns varchar(500)  --Delimiter
as 
begin 
 declare @l_value varchar(500),@l_pati int,@l_pos int,@l_avalue varchar(2000) 
 set @l_avalue=@p_value; 
 if @p_pos<=0 
  return null 
 set @l_pos=0 
 while len(@p_value)<>0 
  begin 
   select @l_pati=charindex(@p_deli,@p_value),@l_pos=@l_pos+1 
   if @l_pati<=0 or @l_pos=@p_pos 
    begin 
     if @l_pati<=0 
      set @l_value=@p_value 
     else 
      set @l_value=substring(@p_value,1,@l_pati-1)       
     break 
    end 
   set @p_value=stuff(@p_value,1,@l_pati,'') 
  end 
 if @p_pos>@l_pos 
  return '' 
 else 
  return isnull(@l_value,'') 
 return null 
end 
----------------------------------------------------------------------
Example : How to Use this function.
  
Declare @A varchar(100)
Set @A = dbo.Function_split ('Ritesh,Kumar,Kesharwani', 3 , ',')
print @A
 
Return Value : Kesharwani
 
Declare @A varchar(100)
Set @A = dbo.Function_split ('Ritesh,Kumar,Kesharwani', 1 , ',')
print @A
 
Return Value : Ritesh
 
Declare @A varchar(100)
Set @A = dbo.Function_split ('Ritesh,Kumar,Kesharwani', 1 , '*')
print @A
 
Return Value : Null
 
Declare @A varchar(100)
Set @A = CAST(dbo.Function_split ('100,200,300', 1 , '*') AS Integer)
print @A
 
Return Value : 100
 
 


Have a Nice Day
 
Ritesh Kumar Kesharwani
Software Professional (MCAD)
Cell  :  011-91-9845657636

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

How To Add Scroll Bar in ASPX Server Control

Use Division Tag to Add ScrollBar in any server Control in ASPX Page.

<div style=" OVERFLOW: auto; WIDTH: 800px; HEIGHT: 152px">

<!-- You can add any Contol here which you want to add Scroll Bar
Like : DataGrid , TextBox, Panel ..Etc -->

</div>

Note:One common problem comes with Datagrid the header part will also scroll
with scrollbar to fix that create one division for HTML table which contain header
only another division for Datagrid where header will be visible false (ShowHeader="False")
Have a Nice Day
Ritesh Kumar Kesharwani
Software Professional
Cell : 011-91-9845657636


Start your day with Yahoo! - make it your home page

Monday, July 04, 2005

Date Validation ,Date Conversion

This Function will take All kind of Date Format and Finally it convert to "MM/DD/YYYY"
 
Format to be Acceptted  mm/dd/yyyy
                                    m/d/yy
                                    mm/dd\yyyy
                                    mm-dd-yyyy
                                    m-d-yyyy   
                                    mm/yyyy   
                                    m/yy     ....etc
 
Function for VB.NET
 

Public Shared Function ConvertToDate(ByVal dateString As String, ByRef result As DateTime) As Boolean

If dateString.Trim = String.Empty Then

Return False

End If

Try

dateString = dateString.Trim

dateString = dateString.Replace("\", "/")

dateString = dateString.Replace(":", "/")

dateString = dateString.Replace("-", "/")

Dim year, month, day As Int16

If dateString.IndexOf("/") = dateString.LastIndexOf("/") Then

' MM/YY format.

Dim index1 As Int16 = dateString.IndexOf("/")

Dim index2 As Int16 = dateString.LastIndexOf("/")

year = dateString.Substring(index2 + 1)

month = dateString.Substring(0, index1)

day = DateTime.DaysInMonth(year, month)

ElseIf dateString.IndexOf("/", dateString.IndexOf("/") + 1) = dateString.LastIndexOf("/") Then ' Check if the second "/" is the last "/"

' MM/DD/YY format.

Dim index1 As Int16 = dateString.IndexOf("/")

Dim index2 As Int16 = dateString.LastIndexOf("/")

year = dateString.Substring(index2 + 1)

month = dateString.Substring(0, index1)

day = dateString.Substring(index1 + 1, index2 - index1 - 1)

Else

Return False

End If

If year < 100 Then

year = year + 2000 ' Convert to 4 digit format.

End If

Dim expDate As String = MonthName(month) & day & ", " & year

If Not IsDate(expDate) Then

Return False

Else

result = CType(expDate, DateTime)

Return True

End If

Catch ex As Exception

Return False

End Try

End Function


Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.