Posted by admin on Mar 30, 2010 in
code,
SQL,
tips 
SQL Code
OK so just like the post below about searching for columns or table names. This post is all about how to search inside Stored Procedures for strings / text.
This can be really useful especially if you know a piece of code is called but your not sure which stored proc calls it.
As before we are going to use the ‘users’ string to keep consistency.
I had searched high and low for this code on the internet but couldn’t find anything. All of the examples I found only searched in the first 255 characters of stored procedure which is OK if its a declaration your looking for however not too good if your stored proc has unions and has declarations half way down the procedure. So I decided to write my own.
Anyway on with the script. (really nice and simple)
– Author : Martin Hall
– Date 17th February 2010
– Search Stored Procedures for Text. (see Definition like)
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE ‘%users%’
Hope you Enjoy
Posted by admin on Mar 30, 2010 in
SQL,
tips 
SQL Code
This is a very useful script which I use all of the time. Especially if there are issues around Database Schema maps being kept up to date.
This SQL script will take a string as an input and output all of the column names and table names where that string is used.
So for instance you have a column called username however its not in the obvious place which would be the users table. You know the column exists as you’ve seen it trace outputs or the developer has said to you “check the users column.
You would run the below script and would get a list of any Tables or Columns with a name like ‘users’
– Author : Martin Hall
– Date 12th June 2009
– Search Table or Column for text
– To search for a TableName then just change the “where syscolumns” to “where sysobjects”)
SELECT syscolumns.name AS [Col Name], sysobjects.name AS [Table Name]
FROM syscolumns INNER JOIN
sysobjects ON syscolumns.id = sysobjects.id
WHERE (syscolumns.name LIKE N’%users%’)
Order by sysobjects.name
Hope you find it useful.
Tags: SQL search Column Names