Query to get dependent objects

-- Get SP names which references the tables with specified schema

SELECT DISTINCT sd.object_id "object_id",

OBJECT_NAME(sd.object_id)
"OBJECT_NAME",

OBJECT_NAME(referenced_major_id)
"Ref_OBJECT_NAME", referenced_major_id "Ref_OBJECT_Id"

into #table1

FROM sys.sql_dependencies sd

where referenced_major_id in

(

select object_id

from sys.tables

where schema_name(schema_id) = 'dbo'

)order by Ref_OBJECT_Id

-- Now, get sps with all dependent tables.

SELECT distinct sd.object_id "object_id",

OBJECT_NAME(sd.object_id)
"OBJECT_NAME",

OBJECT_NAME(referenced_major_id)
"Ref_OBJECT_NAME", referenced_major_id "Ref_OBJECT_Id"

FROM sys.sql_dependencies sd

where OBJECT_NAME(sd.object_id)

in

(

select [object_name] from #table1

)

drop table #table1

Data Difference of two identical Tables

This script was written to answer a question on
sqlservercentral.com. The original poster wanted to have a dynamic stored
procedure which can tell you the columns with different values in two tables.

Here is the code;

ALTER Procedure uspTableDiff

   @pTableName1 varchar(100), -- Name of First Table

   @pTableName2 varchar(100), -- Name of 2nd Table

   @pJoinCondition varchar(Max), -- Join Condition. I am assuming that

                                 -- you will use alias a for table in

                                 -- @pTableName1 and b for

                                 -- table in @pTableName2

   @pIDColumns varchar(Max) -- Valid ID columns to diplay

As

Begin

   Declare @vSQL varchar(max)

   Declare @vCols varchar(max)

   Set @vCols = ''

    -- Check if the specified tables areidentical or

   -- not as you will match schema wise
   -- identical tables.

   if Exists(

      Select *
      from

      (

      Select [name] from sys.columns

      where Object_id = Object_id(@pTableName1)

      ) c1

      Full Outer Join

      (

      Select [name]

      from sys.columns

      where Object_id = Object_id(@pTableName2)

     ) c2 on c2.[name] = c1.[name]

     Where c2.[name] is Null or c1.[Name] is Null

   )

   begin

      Select 'Tables Not Identical'

      Return

   end 

   Select @vCols =

      Stuff((Select ',case when IsNull(a.' + [name] +

                               ','''') = IsNull(b.' + [name] + ','''')

                       then 1 else 0 end

                       as [SIM_' +   @pTableName1 + '.' + [name] + ']'

   from sys.columns
   where Object_id = Object_id(@pTableName1) for XML Path('')),1,1,'')

   -- Concatenate the @vCols with main sql

   Set @vSQL = ' Select ' + @pIDColumns + ',' + @vCols

   + ' From '+ @pTableName1 +  ' a Inner Join '

   + @pTableName2 +

   ' b on ' + @pJoinCondition

   Print @vSQL

   Exec (@vSQL)

end

Trim Non-Alpha characters from string

Here is the code to trim non-alpha characters
from the string. This script uses tally table approach.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go 

--=============================================

-- Author: Atif Sheikh

-- Create date: 28-05-2010

-- Description: Trim Non Alpha Characters

--=============================================

--Select dbo.fnTrimNonAlphaCharacters('2131231Atif123123 234234Sheikh6546')

CREATE FUNCTION [dbo].[fnTrimNonAlphaCharacters]

(

   @pString varchar(max)

)

RETURNS varchar(max)

AS

BEGIN

   Declare @vRetString varchar(max)

   Set @vRetString = ''

   ;with
   wcte as (

      Select Top(len(@pString)) *

      from (Select row_number() over (order by a.object_id) N

            from sys.columns a, sys.columns b

           ) Main

    )SELECT @vRetString = @vRetString + SUBSTRING(@pString,N,1)

     FROM wcte a

     WHERE N <= LEN(@pString)

     And (Ascii(SUBSTRING(@pString,N,1)) between 97 and 122

     Or Ascii(SUBSTRING(@pString,N,1)) between 65 and 90

     Or Ascii(SUBSTRING(@pString,N,1)) = 32)

     ORDER BY N

  Return @vRetString

END

Database Mirroring Automating Failover for Multiple SQL Server Databases With No Witness

If Someone is performing Mirroring in SQL Server 2005, he should look at the following articles...


Database Mirroring Automating Failover for Multiple SQL Server Databases


Also


Database Mirroring Automating Failover for Multiple SQL Server Databases With No Witness


Enjoy...

Parsing / breaking character separated values in a table in one query

Consider a table having following data,
Declare @vTable Table(id int,val varchar(100))

Insert into @vTable Values (1,'Atif Sheikh2 Sheikh123')

Insert into @vTable Values (2,'Asif Sheikh1 Sheikh2 Sheikh3')

Now if you want to break
the space separated values in the abovetable as;

ID     Val
1       Atif
1       Sheikh2
1       Sheikh123
2       Asif
2       Sheikh1
2       Sheikh2
2       Sheikh3

Here is the query;
;with wcte (id,val,newpos) as

(
Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as val,charindex(' ',val,0) + 1 newpos from
@vTable

Union all

Select b.id,substring(b.val,newpos,case when charindex(' ',b.val,newpos) = 0 then Len(b.val) else charindex(' ',b.val,newpos) -newpos end) as val

,charindex(' ',b.val,newpos) + 1 newpos

from @vTable b

Inner Join wcte on wcte.id = b.ID

and wcte.newpos <> 1

)Select ID,Val from wcte order by id

Let me explain the above mentioned query. As you can see that i have used a
recursive function CTE.

The first query in the CTE query is;
Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as val,charindex(' ',val,0) + 1 newpos from
@vTable
This creates the main record for parsing the string. This
loads the first value in the required data set and to use in the second part.
Here I used the newpos column to get the position to
parse the next value in the character separated string. It is just the length
of the previous parsed value. The second part is;

Select b.id,substring(b.val,newpos,case when charindex(' ',b.val,newpos) = 0 then Len(b.val) else charindex(' ',b.val,newpos) -newpos end) as val

,charindex(' ',b.val,newpos) + 1 newpos

from @vTable b

Inner Join wcte on wcte.id = b.ID

and wcte.newpos > 1

This query uses the posintion in newpos column and parses the next value. And as is is recursive, it goes on until the newpos
> 1. newpos, as you can
see in the first part of the query, is the charindex of the character which is
used to separate the values in the string.

Thats it...