Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Right click (modify / alter / drop and create) correct vs. query on syscomments.text or sp_helptext return in-correct object definition Expand / Collapse
Posted Tuesday, October 11, 2011 12:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 11, 2011 12:41 AM
Points: 1, Visits: 0
In a SQL 2000, I have 2 stored procedures
and using sp_helptext to see the SQL definition of that object

exec sp_helptext sproc1
create procedure sproc1

exec sp_helptext sproc2
create procedure sproc1 -- That is not a typo ....sproc1

I also tried a query like:
syscomments inner joined with sysobjects to return:
There are a few rows of syscomments.text
create procedure sproc1
for both stored procedure object.

yet when I use SSMS & right-click modify / alter / drop and create
... procedure sproc1 or sproc2 -- depending where the right click was done

BTW-the same result happened in SQL 2008 (not R2) SQL Server.

Let me please ask the questions:
How could the right click (modify / alter / drop and create) be correct yet a query on the system tables or system stored procedures return in-correct?
Does this mean that the system tables of the user database are corrupt?
Is there a way to fix this? DBCC CheckDB?

Thank you for your time and any ideas to fix this system table corruption?
Post #1188226
Posted Tuesday, October 11, 2011 4:22 AM



Group: General Forum Members
Last Login: Today @ 1:15 PM
Points: 13,784, Visits: 35,427
i remember that SQL 2000, via enterprise manager, it is possible to use the GUI to change the text of the procedure, but without executing that new definition , so you could have a definition that is different than the compiled body of the proc.

so you are seeing effectively the same issue in 2008? i thought that was addressed by SSMS always scripting it out? or are you looking at a 2000 server that that effect may have occurred?


--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1188316
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse