Thank you, NBSteve,

Two points:

• First - in fact NULLIF was more for a confusion. The punch point is that SQL Server converts empty string to 0 if implicit/explicit conversion is...

• This reply was modified 3 years, 1 month ago by  Peter Petrov.
• This reply was modified 3 years, 1 month ago by  Peter Petrov. Reason: Typo

Actually the explanation of the behavior of the fist SELECT statement is not very accurate:

"because DATETIME has a higher precedence than INT 2 is converted to a DATETIME which results...

Nice finding! Good to know...

The script could be simplified (2005 and 2008):

SELECTOBJECT_NAME(object_id) AS proc_name

,object_id

,definition

FROMsys.sql_modules

WHEREPATINDEX('%CREATE%PROCEDURE%',definition) > 0

ANDPATINDEX('%' + OBJECT_NAME(object_id) + '%', definition) = 0

ORDERBY OBJECT_NAME(object_id);

The next step is to peek up a word (more than three or four letters of course) and Google it:-D

I tried with this one - Kimatos :w00t:

We use it in our Peer-to-Peer replications and they are Transactional replications, too. And it is very useful... 🙂 IMO the question is incorrectly asked.

jcrawf02 (9/2/2009)

---

"Quickly and Painlessly" doesn't seem to fit the method, when SELECT COUNT(*) FROM myTable is just four little words (ok, a function ain't a word, whatever) long, and the...

Thanks, Oded.

Unfortunately the CTE method does not work with SQL 2000. Last year I needed to cleanup hundreds tables in order to introduce primary keys, before 2000 - 2005 conversion...

Lowell,

Thank you for your response. I brought my example just for simplicity. The viewes that I am using looks something like this one (there are some more user tables involved):

CREATE

Thank you both for your postings. May be my description of the problem wasn't clear. I don't have any problems creating views under INFORMATION_SCHEMA in SQL 2000 at all. The...