Incomplete data from information schema

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Incomplete data from information schema

Author

Topic

thenearfuture
Starting Member

35 Posts

Posted - 2007-12-03 : 07:44:56

We're currently using SQL Server 2000 and are preparing to upgrade to 2005. Part of the preparation involves updating the stored procedures and functions that use old style joins.

We've been using information schema to find the offending code, but it hasn't worked perfectly. My colleague happened to know that two particular stored procedures used the old left joins, but these two didn't show up in the information schema query.

I dug into the stored procedures and ran the following query a number of times, each time with a different part of the code inserted into the where clause.

Query:


SELECT	specific_name
FROM	information_schema.routines
WHERE	routine_definition LIKE '%*=%'

It turned out that information_schema only had part of the code stored for those two procedures. Whenever I tried to query anything past a particular point, the procedure would disappear from the results.

So, to use a really simple example, let's assume that part of the stored procedure code looked like this and that the problem point starts at 'EmployeeId':

Sample excerpt from imaginary stored procedure

SELECT	*
FROM	Employees
WHERE	EmployeeId = 34

If I ran the following query, this stored procedure would not show up in the results. In fact, the only time it would show up in the results is if I queried for something above the line 'EmployeeId'.

SP doesn't show up:


SELECT	specific_name
FROM	information_schema.routines
WHERE	routine_definition LIKE '%EmployeeId%'

SP does show up:


SELECT	specific_name
FROM	information_schema.routines
WHERE	routine_definition LIKE '%FROM Employees%'

Additional information:

The two procs have nothing in common.

The problems start at lines 69 and 81, respectively.

One proc uses standard SQL, the other dynamic SQL.

Does anybody have any ideas as to what might be happening here?

Thank you for reading.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-03 : 07:55:20

Yes. It is a known problem when searching data with INFORMATION_SCHEMA.ROUTINES view. It happens when your procedure text crosses 8000 character limit.

Check this link: http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

thenearfuture
Starting Member

35 Posts

Posted - 2007-12-03 : 08:03:43

Thank you for the quick response and for taking the time to find the link. I'm off to do some reading.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources