Search Stored Procedures for string/text

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

Check if Column or Table exists in SQL DB

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

Google.CN is no more. Redirecting to Google.com.hk (Google.cn moved Offshore)

Google.CN Moved to Hong Kong

As the title states Google.CN is moving all searches offshore to Hong Kong. Google.Com.HK

However the Google Domain is still live for other things like maps = Google Maps China

Is still live. As are Google Images and Google Products and Google Q & A all for China on the .CN domain.

And most important of all the firewall of china is now turned off according to Googles owns Blog.  It will be interesting to see how China reacts especially as officially Hong Kong is still on Chinese soil.

This all resolves from the Aurora Attacks.  Many big companies got hacked in those attacks but it was not Google’s own accounts being hacked that got its goat. It was however the accounts of many prominent Chineese human rights activists who had their Gmail details hacked.

In fact according to Google the hackers were after two things, firstly the accounts and details of the activists and then secondly the source code of many internal applications. They accessed the source code via source management systems.

What will be interesting is how the Chineese Government react to this change (especially if Google remove the censorship from searches). So far they have only stated that “There will be consequences”

And just to prove that the Google US Exec’s know how bad the human rights violations in China are they try and give their resident Chinese execs a get out of jail free card so the Ministry of State Secrets (MSS - Guojia Anquan Bu [Guoanbu])won’t come and round them up.

“Finally, we would like to make clear that all these decisions have been driven and implemented by our executives in the United States, and that none of our employees in China can, or should, be held responsible for them.”

Increase All Data in an SQL DateTime Column by a Year.

I was recently testing a rarely used piece of functionality on one of the sites which belong to the company I work for. The test DB was very old as releases for this part of the system come very few and far between.

The system is written in such a way that data is only returned to the end user for data for a the last 12 months , (1 Year). The database in question had not been updated for about 18 months and it would have taken about 5 days for me to get a decent backup from the live system restored to test.

The data in the Test Database was valid it was just a little too old to be returned via the front User Interface (GUI).

What I needed was a nice and easy way to bring the date column in the database forward by year.

Obviously for good practice I also requested the live DB dump to be sent over just to completeness of testing. But to test this piece of functionality it wasn’t needed.

The following is the SQL query (Transact-SQL) I cobbled together to get the job done.

It will do a select first to see if you have any data in a particular financial year (2009 and 2010).

It will then update all 2009 data to read 2010. So 01-Feb-2009 becomes 01-Feb-2010 and 23rd June 2009 becomes 23rd June 2010.

It will then carry out the same select query it did before. If all has gone well the 2009 data should be blank and the 2010 field should be populated.

In any update script I write I always input a select before the update and the same select after the update. This way I and any other users using my script can see if the update has worked.

Now the code.

SELECT     YEAR(ColumnName) AS ColumnNameYear
FROM         TableName
GROUP BY YEAR(ColumnName)
HAVING      (YEAR(ColumnName) = 2010) OR
(YEAR(ColumnName) = 2009)

UPDATE    TableName
Set  ColumnName = dateadd(yy,1, ColumnName)
WHERE     DATEPART(year, ColumnName) = ’2009′

SELECT     YEAR(ColumnName) AS ColumnNameYear
FROM         TableName
GROUP BY YEAR(ColumnName)
HAVING      (YEAR(ColumnName) = 2010) OR
(YEAR(ColumnName) = 2009)

Hope you find it useful.

And I’m sure there are many more ways of coding this much simplier but I’m a tester and not a coder and for me it works.

Corelab – MYSql and XML = “oh my” via strange HTML encodes (Unicode)

The title of this blog post is a bit strange and its a play on “Lions – Tigers and Bears oh my”

Anyway back on track , I really like strange encodings for HTML and the way in which some characters (chars) will get interpreted by a web-app as something else.

It makes my job as a tester much more interesting. Once you know what you are doing and you have a grasp of the basics you can will find that you can detect  defects where other testers would have passed an Application as ready for production.

This issue is undocumented elsewhere on the net as far as I can see and it can easily bring down a large majority of websites. (Major ones). By bring down I mean a Dos on the home page due to non display of content.

Feel free to investigate further in to it if you wish. However please only test it on sites which you have permission to run tests against.

anyway onto the details.

The issue is caused by characters that cannot be displayed in XML. As XML is unable to render the characters it will just error and display a blank screen to all users. (so now imagine if a site allowed users to input comments which were displayed on the front page).

The character in question is  and for this defect to take place a few things are needed. As the title states the site must have a MySQL back-end (millions of those about). It must also be coded in .Net (C# tested but may also affect VB.Net and other .Net languages) and lastly it must save data from a webform or textbox into the DB using CoreLab data connectors, and then display the data to be webpage via XML.

Now usually you won’t be able to enter  into the webapp but don’t worry as you enter it as valid text. (more on that coming up)

To see an example of this happening open NotePad + Microsoft Word, and the HTML Encoder page on my site.  Now in notepad type in I’ve visited the test Managers Page and do the same in Microsoft word.

now paste them into the decoder and see the difference.

Notepad will give you %27 and microsoft word will have changed your apostrophe to a curly apostrophe %_u2019 (the underscore needs to be removed but I can’t stop wordpress from encoding without it). I and most likely you may know of this as a simple %19 = .

Now Corelab, .Net – XML and MySql can all handle curly apostrophe’s however if you carriage return and some text on the next line after the curly apostrophe then CoreLab will add in a an “r/n – carriage return”. It seems that in the default installation of Corelab it doesn’t encode chars as UTF8 but as something else. Then in the DB you then get the encoded  which XML cannot cope with as its an invalid HTML char. So when that text which now has an invalid HTML char attempts to get rendered back in XML the XML stream fails and the page will fail to display.