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

how to identify the objects that are commented in the procedure Expand / Collapse
Author
Message
Posted Tuesday, August 3, 2010 8:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:00 AM
Points: 117, Visits: 460
I am working with SQL Server 2000. I am using the syscomments table to extract the procedure into string. In my project there are some conventions for objects so using string operations I am able to identify the objects used in the procedure. The issue is some of the objects are lying in the commented area of the procedure. Is there a way in which I can identify the code that is commented in the procedure so that I can identify the objects that are lying in the commented area.


Thanks,
Tushar
Post #962879
Posted Tuesday, August 3, 2010 9:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:00 AM
Points: 117, Visits: 460
I need to remove the commented part from the string extracted from syscomments.
Post #962908
Posted Tuesday, August 3, 2010 9:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 5,565, Visits: 24,699
Lines can be comented by using -- (for a single line of text) or /* */

The @Script holds the text you retrieved from the syscomments entry and is used here to demonstrate the code

Here is some code to handle the -- case
SET @Script = 'DECLARE @S INT /* used to test for comment lines */ 
-- Another method to comment line
SELECT * FROM logentries'

DECLARE @Pos1 INT
DECLARE @Pos2 INT
SET @Pos1 = 0
SET @Pos2 = 0
SET @Pos1 = CHARINDEX('--',@Script,1)
IF @Pos1 > 0
BEGIN
SET @Pos2 = CHARINDEX(CHAR(10),@Script,@Pos1 + 1) --CHAR(10) = line feed
END

--Follwing code is to demonstrate what was retrieved
SELECT SUBSTRING(@Script,@Pos1,@Pos2 - @Pos1

Result:
-- Another method to comment line

In a similiar fashion you can use /* to find @Pos1 and for @Pos2 use '*/'
instead of CHAR(10)


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #962944
Posted Tuesday, August 3, 2010 10:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:00 AM
Points: 117, Visits: 460
Thanks Ron...
Post #963220
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse