Query using the OBJECT_DEFINITION(OBJECT_ID)

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query using the OBJECT_DEFINITION(OBJECT_ID)

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-04-21 : 05:48:24

When i use the below query:

SELECT * FROM sys.objects where OBJECT_DEFINITION(OBJECT_ID) LIKE '%sp_Bonuses%' AND TYPE='P' ORDER BY NAME

It displays all the sps that uses the object of type '%sp_Bonuses%'

If an sp contain the object sp_BonusesTypes then it will be displayed in the result

But my requirement is if sp_BonusesTypes contains in the comment section then it should not be displayed in the result.

please advise to add any extra conditions to the below query to get the desired result:

SELECT * FROM sys.objects where OBJECT_DEFINITION(OBJECT_ID) LIKE '%sp_Bonuses%' AND TYPE='P' ORDER BY NAME

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-21 : 07:22:03

You would have to parse the text to find comments.
comments could be delimitted by /* */ or start with --
Means also finding line feeds.
Also have to deal with
/*
asdasd
/*
asdddds
*/
sda
*/
and

-- asdasd /* asdsad

select .. -- asdsad

select top 10 * from sysobjects -- /*
select top 10 * from sysobjects /*
select top 10 * from sysobjects -- */

you could also look at sys.dm_sql_referenced_entities if it's available in v2005

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-04-21 : 09:01:04

Thanks for reply..but sys.dm_sql_referenced_entities is not availabe in VS2005, how can i parse the text to find the comments please advise me...

quote:
Originally posted by nigelrivett

You would have to parse the text to find comments.
comments could be delimitted by /* */ or start with --
Means also finding line feeds.
Also have to deal with
/*
asdasd
/*
asdddds
*/
sda
*/
and

-- asdasd /* asdsad

select .. -- asdsad

select top 10 * from sysobjects -- /*
select top 10 * from sysobjects /*
select top 10 * from sysobjects -- */

you could also look at sys.dm_sql_referenced_entities if it's available in v2005

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-21 : 09:12:24

I've given the sorts of cases you need to deal with.
I wouldn't consider doing it - you probably need to go through character by character and keep a comment level count for both /* and --. If they are 0 then check the data if not don't

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-21 : 10:24:08

SELECT sys.schemas.name + '.' + OBJECT_NAME(id) AS ObjectName
FROM syscomments
INNER JOIN sys.objects ON syscomments.id = sys.objects.object_id
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE [text] LIKE N'%yourtextherer%' -- <-----
AND ( OBJECTPROPERTY(id, 'IsProcedure') = 1
--OR OBJECTPROPERTY(id, 'IsScalarFunction') = 1
--OR OBJECTPROPERTY(id, 'IsView') = 1
)
GROUP BY sys.schemas.name + '.' + OBJECT_NAME(id)

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources