05 September 2014

Working in a large database, I cannot memorize one by one the name of database object.
Sometimes I only remember about my id (rirsan) as a comment in stored procedures, functions, views, trigger, ect. Or I only know about table name, field name, a part of query and others keyword.

Then, how could I find the database object name that contain a keyword?
This query does...
SELECT o.name 
FROM   sysobjects
       JOIN syscomments c ON o.id = c.id 
WHERE c.text LIKE '%keyword%'

As a note this blog will do, when I need to use this I'll just copy and paste.
But I need a simpler way hehe.... lets make it as a stored procedure:
CREATE PROCEDURE dbo.spFindText
    @Keyword VARCHAR(50)
AS
SELECT o.name
FROM   sysobjects o
       JOIN syscomments c ON o.id = c.id
WHERE c.text LIKE '%' + @Keyword + '%'
ORDER BY o.name


So next time I need, I'll just run this query:
EXEC spFindText 'keyword'

Click here if you like this article.


0 comments: