Archive

Archive for January, 2012

How to find a specific string from stored procedures, triggers and functions in database

January 12, 2012 Leave a comment

There are several tables and views containing stored procedures, functions and triggers definitions

Views – SYS.SYSCOMMENTS, SYS.SQL_MODULES

Tables – INFORMATION_SCHEMA.ROUTINES

I recommend following query it shows all SPs and Fns that contains any specific text.If we want to search only procedure then  filter in where clause “AND obj.Type=’P’”

SELECT DISTINCT obj.name AS Object_Name,obj.type_desc 
FROM sys.sql_modules sm INNER JOIN sys.objects obj ON sm.object_id=obj.object_id 
WHERE sm.definition Like ‘%search text%’

If we want to search specific function or objects names we can find dependencies using following system stored procedure

EXEC sp_depends @objname =searchobjectname

There is another way to find specific text using INFORMATION_SCHEMA.ROUTINES. But for me it didn’t give me all the results.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE
‘%search text%’

AND ROUTINE_TYPE=’PROCEDURE’ (for procedures only)

And  also SYS.SYSCOMMENTS view can use to same usage but I got duplicates from some results.

SELECT SO.NAME, SC.TEXT
FROM SYS.SYSOBJECTS SO

JOIN SYS.SYSCOMMENTS SC ON SO.ID = SC.ID

WHERE SC.TEXT LIKE ‘%search text%’
–SO.TYPE = ‘P'(for procedures)

Hope this post helps you.Feel free to give your suggestion on this post.Until next post cheers..!!!

Categories: TSQL Tags: , ,

How to get Twitter ID

January 5, 2012 Leave a comment

Before I start I wish you all a very happy new year 2012.It took long time after my last since I got new job and many changes happened in recent months.

I was learning WPF(Windows Presentation Foundation) and I tried to create RSS Feed reader.I tried to get twitter feeds from my twitter account and I searched in google and I found a code but it needed twitter id for that.I found it can be also replace by username.But this code is also useful.

I open http://api.twitter.com/users/show/rasikalb.json {this is for me common url http://api.twitter.com/users/show/%5Breplace username].json}url and it shows some useful json code that we can get many twitter account details.last line contains  twitter id.

image

We can access our twitter feed from http://twitter.com/statuses/user_timeline/%5BuserId or username].rss it shows as follows.Using this rss file we can read tweets.

image

Hope to post about SQL, C# useful tips in future.Until next post cheers ..!!!

Categories: Uncategorized Tags: ,