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

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.

How to Bypass Websense Twitter block

The company I work for has recently introduced Websense to filter out certain sites which they believe to be non-conducive to a working environment. This is not such a bad this as they block certain sites which are deemed to hold malware and other things.

However Websense is quite bad in that it restricts what users can view and this I disagree with.  It’s more than a Malware site filter it also blocks social networking sites and many blogs. Twitter is not a bad site and I wanted to check a tweet of a user who had posted something interesting regarding testing and website security.

But because we have websense it was blocked all I got was the message .

This Websense category is filtered: Social Networking and Personal Sites.
URL:

http://twitter.com/

The other thing I think is bad regarding the use of Websense is that users will attempt to bypass it. Its in their nature especially if you have a team of Web-testers.

So if you don’t want a bunch of proxies and ssh tunnels installed in your network it’s better to just leave it for users to be responsible enough to decide what is a site worthy of visiting in work time.

So now on to the quick and dirty hack to get your twitter feeds on a network that is using Websense.

You can either load up http://www.twittergadget.com/ which is a IGoogle Plugin – It allows tweeting and reading of other tweets from the your personalised Google Homepage.

TIP: I advise that you turn off the thumbnails in twittergadget or your browser will still attempt to make calls which will be logged by Websense.  Once thumbnails are tuned off Websense never logs any twitter traffic.  so you still can make and receive feeds without Websense being any the wiser.

or you can use the below link which allows reading only

Bypass Websense for all Tweets which have the Term Test

You could just as easilly insert the peoples names who tweets you follow and that will work.

Hope you found it useful

****UpDate – 28th Jan-2010****

Google is running a trial or Twitter Feeds in the main results page on normal searches.

Its only on certain “Hot Topics”

the below link is for the new Ipad from Apple.

Google Apple Ipad results with Twitter Feed

see the screenshot.

Screenshot