any ways to check for triggers

Hi ,
Is there any SP commands to retireve all the triggers for the whole DB
instead of the sp_showtrigger which is only at the DB's table level ?
Can i actually use the syscomments table like this
select * from syscomments where text like 'Create trigger%' ?
or is there any other method ?
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1One method:
SELECT
OBJECT_NAME(parent_obj) AS TableName,
name AS TriggerName
FROM sysobjects
WHERE type = 'TR'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:5f3b73333e46b@.uwe...
> Hi ,
> Is there any SP commands to retireve all the triggers for the whole DB
> instead of the sp_showtrigger which is only at the DB's table level ?
> Can i actually use the syscomments table like this
> select * from syscomments where text like 'Create trigger%' ?
> or is there any other method ?
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||tk you
Dan Guzman wrote:
>One method:
>SELECT
> OBJECT_NAME(parent_obj) AS TableName,
> name AS TriggerName
>FROM sysobjects
>WHERE type = 'TR'
>> Hi ,
>[quoted text clipped - 7 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||This will give you the trigger code also
SELECT
sysobjects.name AS [Trigger Name],
SUBSTRING(syscomments.text, 0, 26) AS [Trigger Definition],
OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],
syscomments.encrypted AS [IsEncrpted]
FROM
sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
WHERE
(sysobjects.xtype = 'TR')
Aneesh R
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:5f3b73333e46b@.uwe...
> Hi ,
> Is there any SP commands to retireve all the triggers for the whole DB
> instead of the sp_showtrigger which is only at the DB's table level ?
> Can i actually use the syscomments table like this
> select * from syscomments where text like 'Create trigger%' ?
> or is there any other method ?
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1sql

Any advantage to using named pipes in connecting to a SQL Server?

Gurus,
Is there any advantage to running an application to use named pipes in
connecting to a SQL Server instead of TCP/IP? I would think no, b/c named
pipes is a legacy, slower form of communication, correct?
--
SpinI'm sure if you look hard enough, you could find some advantage of using
named pipes. But that's really pointless. As you said, named pipes are kind
of legacy, and TCP sockets are the way.
SQL Books Online doesn't seem to be quite up to date on this topic. For
instance, it stats that, "Generally, TCP/IP is preferred in a slow LAN, WAN,
or dial-up network, whereas named pipes can be a better choice when network
speed is not the issue, as it offers more functionality, ease of use, and
configuration options." I'm curious as to why named pipes offer more
configuration options. It seems to me that TCP is much more configurable.
Linchi
"Spin" wrote:
> Gurus,
> Is there any advantage to running an application to use named pipes in
> connecting to a SQL Server instead of TCP/IP? I would think no, b/c named
> pipes is a legacy, slower form of communication, correct?
> --
> Spin
>
>

Any advantage to using named pipes in connecting to a SQL Server?

Gurus,
Is there any advantage to running an application to use named pipes in
connecting to a SQL Server instead of TCP/IP? I would think no, b/c named
pipes is a legacy, slower form of communication, correct?
Spin
I'm sure if you look hard enough, you could find some advantage of using
named pipes. But that's really pointless. As you said, named pipes are kind
of legacy, and TCP sockets are the way.
SQL Books Online doesn't seem to be quite up to date on this topic. For
instance, it stats that, "Generally, TCP/IP is preferred in a slow LAN, WAN,
or dial-up network, whereas named pipes can be a better choice when network
speed is not the issue, as it offers more functionality, ease of use, and
configuration options." I'm curious as to why named pipes offer more
configuration options. It seems to me that TCP is much more configurable.
Linchi
"Spin" wrote:

> Gurus,
> Is there any advantage to running an application to use named pipes in
> connecting to a SQL Server instead of TCP/IP? I would think no, b/c named
> pipes is a legacy, slower form of communication, correct?
> --
> Spin
>
>

Ansi SQL92 question

I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?Versions 2000 SP4 and 2005 SP1|||dunleav1 wrote:

Quote:

Originally Posted by

I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?

Careful.. when you refer to "old join syntax" in this group readers will
assume you mean TSQL proprietary *= syntax for outer joins and not
"implicit join syntax". For implicit inner joins please see my answer in
c.d.ibm-db2.
If you in fact do outer joins using *= or Oracle style (+) syntax things
are different. Portability would be one reason to abandon proprietary
syntax for ANSI syntax.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||Yes, implicit join sytax.

I believe it's a portability issue for me accross database vendors.
It's easier to write the same sql accross vendors. But if development
want to mix and match join syntax accross vendors and products it's
fine with me. As long as it doesn't effect performance.

So for Mssql there is no performance impact for using an implict join
as opposed to the SQL92 standard outer join syntax?|||On Feb 1, 11:14 am, Serge Rielau <srie...@.ca.ibm.comwrote:

Quote:

Originally Posted by

dunleav1 wrote:

Quote:

Originally Posted by

I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?

>
Careful.. when you refer to "old join syntax" in this group readers will
assume you mean TSQL proprietary *= syntax for outer joins and not
"implicit join syntax". For implicit inner joins please see my answer in
c.d.ibm-db2.
If you in fact do outer joins using *= or Oracle style (+) syntax things
are different. Portability would be one reason to abandon proprietary
syntax for ANSI syntax.
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge,
I believe we are in agreement but let me give you an example about
what I referring to:
sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
tab1.col1 >1;
sql92: select tab1.col1 from tab1 inner join tab2 on
tab1.col1=tab2.col2 where tab1.col1 >1;
Is there a performance impact using one syntax over the other?

I agree it is a good idea to not use proprietary sql extensions such
as (tab1(+) for Oracle or Mssql *=).|||dunleav1 (jmd@.dunleavyenterprises.com) writes:

Quote:

Originally Posted by

I believe we are in agreement but let me give you an example about
what I referring to:
sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
tab1.col1 >1;
sql92: select tab1.col1 from tab1 inner join tab2 on
tab1.col1=tab2.col2 where tab1.col1 >1;
Is there a performance impact using one syntax over the other?

In SQL Server, no.

And I would find it difficult to justify to go through all code and
change it to use the newer syntax. (Note that the SQL-89 syntax is
still very much valid.)

However, I tend to rewrite into the newer syntax when I work with old
code, since I find the newer syntax much easier to read and work with.

Quote:

Originally Posted by

I agree it is a good idea to not use proprietary sql extensions such
as (tab1(+) for Oracle or Mssql *=).

If you on the other hand have lots of code with *= int, there is
all reason to rewrite it. *= is deprecated in SQL 2005, and works
only in compatibility mode 80.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>I need to justify changing the code to the new standard. Is there any performance issue related to using the old syntax that are documented? <<

Performance is not the real problem ..

Quote:

Originally Posted by

Quote:

Originally Posted by

>Are there any other issues that I use to justify a code upgrade? <<

The old OUTER JOIN syntaxes are not portable, and are being deprecated
by vendors. It does not work the same way in Sybase, SQL Server,
Oracle, Informix and Centura among other products. It is also very
limited and you will probably find that you can re-write old code to
great advantage. Here is a cut&paste on the details:

Here is how OUTER JOINs work in SQL-92. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved
table" in the query. What I am going to give you is a little
different, but equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You
also remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @. = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @.
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @.
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @.* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
--------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
--------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
--------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved
and unpreserved in the extended equality version, but it is easy in
SQL-92. For example to find the students who have taken Math 101 and
might have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;