Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I want to know if a stored procedure or views references a given name (table, view or column), I need a version of the object definition stripped of all comments.

Is this possible by a SQL query in an usual DBMS system?

Edit:

For SQL Server I found this solution using [Microsoft.Data.Schema.ScriptDom.Sql]. Here is the minimal PowerShell Code:

$sql = @'
select * from PowerShell -- a comment
where
 psRefnr = 1
'@
$options = new-object Microsoft.Data.Schema.ScriptDom.Sql.SqlScriptGeneratorOptions

$sr = new-Object System.IO.StringReader($sql)

$sg =     new-object Microsoft.Data.Schema.ScriptDom.Sql.Sql100ScriptGenerator($options)
$parser = new-object Microsoft.Data.Schema.ScriptDom.Sql.TSQL100parser($true)

$errors = $null
$fragment = $parser.Parse($sr,([ref]$errors))

$out = $null
$sg.GenerateScript($fragment,([ref]$out))

$out

The advantage of this approach is that it uses the official Microsoft parser.

3rd Edit:

The basic methods to get the procedure definitions in T-SQL are as mentioned [here][1] are

  1. sp_help proc_name
  2. SELECT [text] FROM sys.syscomments WHERE OBJECT_NAME(id) = 'proc_name'
  3. SELECT OBJECT_DEFINITION(OBJECT_ID('proc_name'))

The second and third option return columns of type varchar(max) which can be used in LIKE clauses.

Historical Note:

When '*=' and '=*' became obsolete, syscomments returned the definition in chunks of 255 characters and the two could be placed into different rows.

But the second problem isn't resolved yet. Some faltering developers tended to keep old code as C - style comments, making scanning for code containing deprecated syntax difficult.

share|improve this question
    
This isn't formatting, it's manipulation of a big string. –  gbn Mar 10 '11 at 15:35
    
Oops I found it. It is done SQLParser.dll from Chad Miller part of SQLPSX codeplex project. It seems to use some Microsoft dll's like Microsoft.Data.Schema.ScriptDom.dll. There is some hope, that it uses the same parser, that SQL Server uses internal. –  bernd_k Mar 10 '11 at 15:51
1  
I'm not putting this as an answer because it's not a direct answer to your question; however, it seems like what you are looking for is a way to check for dependencies without being affected by comments within an object definition. Check the following article to learn about sys.dm_sql_referencing_entities and see if it meets your needs: x.co/TwoS –  Jeff Mar 13 '11 at 3:23
    
Yes dependencies is interesting, usage of deprecated syntax a second, and I would like to determine whether a procedure contains dynamic sql. –  bernd_k Mar 13 '11 at 11:54

2 Answers 2

It is certainly possible to determine at the level of table or view in Oracle. Dependencies are tracked so that the relevant objects can be invalided when DDL changes occur.

Determining column level dependencies can be more difficult as the columns need to be matched to the appropriate table. Views can change column names, so the trace needs to be deeper.

Any false positives you get with comments left in probably indicates that the comments need to be fixed.

share|improve this answer
    
+1 I admit, that it is the correct way to become familiar with the dependency views etc. of your DBMS, before trying the other options. –  bernd_k Mar 12 '11 at 9:02

You could do this using T-SQL and various string functions, but you will need to account for comments using /**/ and --.

In other words, there is no easy way to get back a stored proc definition stripped of all comments. You would essentially need to build your own parsing tool.

share|improve this answer
    
Why inventing the wheel again. At least for SQL Server there is a reliable parser included. Your approach might work heuristically good, but you never know if you cover all possible cases. –  bernd_k Mar 12 '11 at 9:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.