December 2008 - Posts

Happy New Year

set nocount on

select space(7-len(replicate(char(42),no)))+ replicate(char(42),no*2-1) from
(
    select top 5 row_number() over (order by name) as no from sysobjects
) as t
union
all
select space(5)+replicate(char(124),3
)
union
all
select cast(0x4861707079204E65772059656172 as varchar(100))

Posted by Madhivanan with 2 comment(s)

NULL on joined columns

 

You know that
NULL values on joined columns are omitted from comparision when tables are joined

Consider this example

Declare @t1 table(col1 int, col2 varchar(10))

insert into @t1
select 1, 'test1' union all
select 2, 'test2' union all
select NULL,'test3' union all
select 5,' test4'

Declare @t2 table(col1 int, col2 varchar(10))

insert into @t2
select 1, 'testing1' union all
select 2, 'testing2' union all
select NULL, 'testing3' union all
select 3, 'testing4'

select t1.col1,t2.col2 from @t1 as t1 inner join @t2 as t2 on t1.col1=t2.col1

The result is

col1       col2
----------- ----------
1           testing1
2           testing2


What
if you want to compare NULL values too and get the value testing3?

The query is

select t1.col1,t2.col2 from @t1 as t1 inner join @t2 as t2 on t1.col1=t2.col1 or (t1.col1 is null and t2.col1 is null)

and the result is

col1      col2
----------- ----------
1           testing1
2           testing2
NULL      testing3

Posted by Madhivanan with no comments

Script of DDL triggers

 

How do I
view the script of the DDL triggers?

The answer is not

sp_helptext 'DDL trigger'

Because DDL triggers are defined at database/server level and not at any table level
So they are not stored in a system objects like sys.objects and the usage of sp_helptext,object_name, etc wont work

Using this

select * from sys.objects where type='tr'

You can see all triggers except DDL triggers

To know the DDL trigger, you need to use one of the following

select * from sys.triggers
where parent_class=0

select m.* from sys.sql_modules as m left join sys.objects as o
on m.object_id=o.object_id
where o.object_id is null

To script the DDL trigger, you need to use one of the following

select object_definition(object_id) from sys.triggers
where parent_class=0

select m.definition from sys.sql_modules as m left join sys.objects as o
on m.object_id=o.object_id
where o.object_id is null

Posted by Madhivanan with 1 comment(s)