Discrepancy in definitions in system tables in same database

Question

Discrepancy in definitions in system tables in same database

SQL Guy 1

SSCoach

Points: 15979
More actions
September 9, 2011 at 4:14 pm

#243526

Within the same database, if I compare definitions between INFORMATION_SCHEMA.ROUTINES and sys.sql_modules, for about 1/3 of all objects they are different.
This query ideally should not return any records:
select
OBJECT_NAME(m.object_id),
r.ROUTINE_NAME,
CHECKSUM(m.definition),
CHECKSUM(r.routine_definition)
from sys.sql_modules m join INFORMATION_SCHEMA.ROUTINES r
on OBJECT_NAME(object_id) = r.ROUTINE_NAME
where --CHECKSUM(m.definition) <> CHECKSUM(r.routine_definition)
m.definition <> r.routine_definition;
However, I got records that have completely different checksums. I tried it on several different databases, and got some records that do not match between these system tables.
Should I shange something in configuration? Does anybody else see such difference in your databases ?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Are you looking into something that is a problem or just finding something that appears strange? What are you trying to accomplish or what are you looking for?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

From Books Online:

INFORMATION_SCHEMA.ROUTINES

ROUTINE_DEFINITION
nvarchar(4000)
Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL.

sys.sql_modules

definition
nvarchar(max)
SQL text that defines this module.

So anything that's more than 4000 long will be different between the two.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 3

Hi Sean, first it was a problem and than it appears strange. I was tasked to write two scripts: to compare stored procedures, views, triggers between databases. In one my script I used sys.schemas, sys.procedures, sys.sql_modules, and in another one, in order to save on number of joins, I used information_schema.routines. Results of the scripts were different. When I debugged them, I found the problem described above.

Hi Gila, thanks, this is the reason why. I should have read B.O.L. before using this view. All S.P. that do not match, have len > 4000. Also I changed <> to = in my query, and all len of S.P. were < 4000.

The buttom line: we should not trust information_schema.routines.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The information schema views are ISO standard. There will be lots of areas where the schema views show more information than they do.