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.
| Author |
Topic |
|
AlanS
Starting Member
28 Posts |
 Posted - 2007-09-19 : 12:31:11
|
| 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) |
|
|
AlanS
Starting Member
28 Posts |
Posted - 2007-09-19 : 14:12:05
|
| The problem is that the reference to sysobjects is qualified by prefixing the selected database name, while the reference to syscomments is not, so syscomments will resolve to whatever database is the current context. Adding the qualification to syscomments solved the problem. |
 |
|
|
AlanS
Starting Member
28 Posts |
Posted - 2007-09-19 : 14:38:42
|
| 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 ideas? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-19 : 14:59:31
|
| Have you tried using the INFORMATION_SCHEMA views instead of system tables?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
AlanS
Starting Member
28 Posts |
Posted - 2007-09-19 : 15:28:38
|
| Unfortunately, the INFORMATION_SCHEMA view have two limitations that make them unsuitable for my purposes (extracting the complete text of stored procedures and UDFs): (1) they don't include the object [id] value, which I need in order to join with related tables; and [2] if the stored procedure or function body is more than 4000 characters, the excess is truncated. |
|
|
|
|
|
|
|
|
system table problems