system table problems
-
I need to extract data from the sysobjects and syscomments tables for all of the user defined functions and user defined stored procedures in a given database. I am using the code below, which mostly works fine (the name of the selected database has been previously SET into @CurrentDb). However, I have noticed that recently created stored procedures in recently created databases are not being captured by this code, even though their records are present in the sysobjects and syscomments tables. I've also noticed that these "missing" stored procedures have sysobjects.status set to 0. I cannot find any documentation for that field, nor will SQL Server allow me to manually edit it. Does anyone know how I can get around this problem, so that my routine will capture all of the user defined functions and user defined stored procedures?
TRUNCATE TABLE _EmbeddedSearchRoutines
SET @SQL =
'INSERT INTO _EmbeddedSearchRoutines
SELECT O.[id], C.number, C.colid, O.[name], O.xtype, O.crdate,
ObjectType =
CASE
WHEN O.xtype = ''P'' THEN ''Stored Procedure''
ELSE ''Function''
END,
LEN(C.[text]) AS LenText, LenReal = DATALENGTH(c.[Text]), [Text] = CAST(c.[Text] AS VARCHAR(4000))
FROM ' + @CurrentDb + '..sysobjects AS O
INNER JOIN syscomments AS C
ON O.[id] = C.[id]
WHERE (O.xtype IN (''P'', ''FN'', ''IF'', ''TF''))
AND (OBJECTPROPERTY(O.ID,''IsMsShipped'') = 0)
ORDER BY ObjectType DESC, O.[name], O.[id], C.number, C.colid'
EXEC (@SQL)
-
Where does the code run from? Since there is no "USE" in the @SQL string, I'm guessing that this runs from a single location, and if that's the case, I see at least one issue, your join on syscomments. While you fully qualify sysobjects, you don't do the same with syscomments.
-
You've once again proved the value of a second pair of eyes. As soon I added the qualification to syscomments, the code picked up the "missing" stored procedure. Thank you, David!
-
No problem at all.
You probably want to redo the ones you've already done, as the comments and objects, even though they found a match, aren't necessarily a correct match.
-
Oops - I spoke too soon - making both tables qualified did cause a stored procedure to show up, but on closer examination it is NOT the user defined one I was looking for, but "dt_whocheckedout_u" which is apparently created by the system. So, I'm back to square one. Any other ideas?
-
Are these databases, including the new ones, all on the same server? If so, is that server 2000 or 2005? Also, are these stored procedures owned by "dbo"?
-
At this point, I'll be thrilled if I can get this working on a single server. All of our servers are SQL 2000. As far as I know (I'm pretty new here), all objects are owned by dbo.
-
Take one of the stored proc that's not returning from your query, and find it in sysobjects and syscomments. Post the two rows here (you can trim the two columns with the proc text in them if you'd like, since they are nvarchar(4000).
-
The two rows are below. I've found a workaround mentioned several places on the web, that seems to give me correct results, but I feel uneasy about it as it relies on an undocumented column (sysobjects.category). Basically, I changed:
WHERE (O.xtype IN ('P', 'FN', 'IF', 'TF'))
AND (OBJECTPROPERTY(O.ID, 'IsMsShipped') = 0)
to this:
WHERE (O.xtype IN ('P', 'FN', 'IF', 'TF'))
AND (O.category = 0)
row from sysobjects:
SP20 1977058079 P 1 0 0 16 0 0 2007-09-19 11:36:36.573 0 16 0 P 0 4 0 2007-09-19 11:36:36.573 0 0 0 0 0 0 0
row from syscomments:
1977058079 1 1 2 0x4352454154452070726F63656475726520535032300D0A61730D0A73656C656374202A0D0A66726F6D207379736F626A65637473 2 0 0 1 CREATE procedure SP20 as select * from sysobjects
-
That's a bit strange. Just for fun, try changing the line...
(OBJECTPROPERTY(O.ID, 'IsMsShipped') = 0)
to
(OBJECTPROPERTY(O.ID, 'IsMsShipped') <> 1)
I'm wondering if somehow a Null (or other non 0/1) value is slipping into that property. Alternatively, SELECT the ones equal to 1 and see if it shows up.
-
Just curious... why names like "SP20"
... not exactly "self documenting"...--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
SP20 is just the name of a test stored procedure I threw together in about 45 seconds. Any objects not intended strictly for testing have appropriately self-documenting names.
-
David:
Changing
(OBJECTPROPERTY(O.ID, 'IsMsShipped') = 0)
to
(OBJECTPROPERTY(O.ID, 'IsMsShipped') 1)
produces the same results as before. I'm not sure what you mean by "Alternatively, SELECT the ones equal to 1", as I'm already doing that - the expression here is in the WHERE clause so it is already filtering the records to be selected.
-
I meant test if IsMSShipped is equal to 1 and see if your proc shows up. Since it's showing up when you remove the filter based on the IsMSShipped property, but doesn't show up when you test for 0 nor when you test for not equal to 1, it just about has to be set to 1 (or perhaps Null). I wanted to verify that that is the case.
-
Ok... thanks! I'll put my soapbox away
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply