Friday, May 1, 2009

Sys on SQL

Not sharing query, but some tips that might be useful when looking for a needle in a haystack.
Am using Microsoft SQL Server 2000, but won't differ too much on later versions, I guess.

Find tables with the specified name

-----------------------------------
select name from sysobjects where name like '%table_name%' and type = 'u'



Find stored procedures with the specified name
----------------------------------------------
select name from sysobjects where name like '%sp_name%' and type = 'p'



Find the name of the tables which contain specified column
----------------------------------------------------------
select TableName = a.name, ColumnName = b.name from sysobjects a, syscolumns b
where a.id = b.id and b.name = 'column_name'



Find stored procedures which contain certain key / text
-------------------------------------------------------
select name from sysobjects where id in ( select id from syscomments where text like '%text_in_sp%')

This is also useful when you would like to search for stored procedures which uses a certain table.
e.g. If you would like to know the stored procedures which use the table named table_name,
select name from sysobjects where id in ( select id from syscomments where text like '%table_name%')
Share:

1 comment:

About

Related Posts

Cisco VPN not Working on Windows 10

Error: Cisco VPN is not working (cannot login and connect to VPN connection). Solution: 1. Right-click on the VPN client, and Run as Ad...

About Me

My photo
Is an ordinary unNoticed man, with a little knowledge to share and high dreams to achieve. I'd be glad if I can help others, 'coz the only thing for the triumph of evil is for a good man to do nothing. I like English and Chinese language, and know a little about them. Hence, for me, most things can best be expressed in Chinese, some in English, and the rest in others ^^

About Blog

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Popular Posts

Blogroll

Followers

Follow by Email