Useful T-SQL Sripts

Search all columns in every table for a value


Have you ever known a value in a database but had no clue what column or table it resided in? If so, you understand how frustrating it can be to spend great amounts of time searching through masses of information for the proverbial needle in a haystack. To help identify locations holding a given value, a search script can be used to spotlight all of the areas containing the needed character string.
The below script references the SQL Server metadata view, "information_schema.columns". As this provides a listing of all columns and tables within a database, it can be used as a foundation of a search. The script uses a common table expression query to populate a temporary table full of individual queries (one for each column in the database -- so it can get large and may take a few minutes). After that a "while" loop is used to execute these statements. The final result is a data set displaying all of the tables and columns matching the information searched for.
To use this script, simply place it within a query window and change the @searchValue parameter. I find it very helpful when used with an application UI - I can enter a value into the interface and use this script to find where in the database the value was stored.
Note: A few data types, such as image, cannot be converted to varchar and thus are ignored. The varchar data type was used as the base type (all data is converted to this) as it produced the fastest standardized value for comparison.
Here is the script:


--initialize transaction
set transaction isolation level read uncommitted
set nocount on
--initial declarations
declare @rowID int, @maxRowID int
declare @sql nvarchar(4000)
declare @searchValue varchar(100)
declare @statements table (rowID int, SQL varchar(8000))
create table #results (tableName varchar(250), tableSchema varchar(250), columnName varchar(250))
set @rowID = 1
set @searchValue = 'test'
--create CTE table holding metadata
;with MyInfo (tableName, tableSchema, columnName) as (
select table_name, table_schema, column_name from information_schema.columns where data_type not in ('image','text','timestamp','binary','uniqueidentifier')
)
--create search strings
insert into @statements
select row_number() over (order by tableName, columnName) as rowID, 'insert into #results select distinct '''+tableName+''', '''+tableSchema+''', '''+columnName+''' from ['+tableSchema+'].['+tableName+'] where convert(varchar,['+columnName+']) like ''%'+@searchValue+'%''' from myInfo
--initialize while components and process search strings
select @maxRowID = max(rowID) from @statements
while @rowID <= @maxRowID
 begin
 select @sql = sql from @statements where rowID = @rowID
 exec sp_executeSQL @sql
 set @rowID = @rowID + 1
 end
--view results and cleanup
select * from #results
drop table #results

The five most over-hyped tech products of 2010

The pace of technology innovation quickened in 2010 but there were still plenty of over-hyped products floating around.


5. Apple iPad-  The truth about iPad: It’s only good for two things, it is a very good effort for a 1.0 product.

4. Microsoft Kinect- Gates said the real innovation would be when you could play a tennis video game with your own racket in your hand instead of a game controller. To Microsoft’s credit, the company has almost entirely brought that vision to life with the Microsoft Kinect, a new add-on for Xbox 360 that is flying off the shelves this holiday season.

3. Samsung Galaxy Tab- The most innovative thing about the Galaxy Tab is that Samsung was the first vendor to finally bring an Android tablet to the mass market.

2. Google TV- The most disappointing technology of 2010 of Google TV.

1. 3DTV- It started at CES 2010 in January and carried all the way through to this holiday season. The TV vendors have bombarded the world with the message that the next big step in television is 3DTV and that you can have it today by buying their new premium TVs and polarized glasses. The problem is that neither the tech press nor the public is buying it.

First Windows Phone 7 Unlock tool- ChevronWP7

ChevronWP7 is the brainchild of Rafael Rivera, Long Zheng and Chris Walsh, and it allow a WP7 handset owner to side load applications that aren’t allowed in the Marketplace due to the use of private APIs.

Download links:
http://walshie.me/ChevronWP7.exe
http://www.multiupload.com/4T7AYFWLSZ


Something to do while the turkey settles …

50 Free Applications

50 Free Apps We're Most Thankful For

Rackspace Cloud

Check out this site and give it some thought coz' this is where we're heading !

Rackspace Cloud: Cloud Hosting & Cloud Computing Products

Favourite Shopping apps for your Smartphone

ShopSavvy is one of the most polished shopping apps I came across, and it's 

available for both iOS and Android. Scan any bar code and find online stores 

and nearby retailers selling any particular product, along with competing prices 

from all locations. ShopSavvy can calculate the location of the nearest retailers 

carrying a desired item and provide maps and directions. It can also tell you 

whether or not the product is actually in stock at a given location. Additionally,

there's a Deals feature that offers up shipping promos, coupon codes, rebates,

and weekend sales.

Barcode Hero is an iOS app that lets you use the camera of the iPhone or iPod Touch

to scan the bar codes of products that you see on store shelves. It then provides 

instant recommendations, product details, and price comparisons from a variety of 

online and brick-and-mortar retailers.In addition to the scanning functionality, 

you can browse and search for products by category, share items with friends 

or family to get real-time feedback, and share wish lists. You can also save 

items as a reminder to purchase at a later time.

If you want to take the stress out of shopping for the handymen (or -women) 

in your life, Aisle411 is a great place to start. This feature-packed and 

easy-to-use app is designed to help you navigate large retail locations, 

such as Home Depot and Lowe's.The app has many functions, but what stands 

out are its list and location features.

TheFind's Beat This Price app for iOS lets you search for a particular item at a given 

price and then populates a list of competing prices from other vendors. It also uses 

LBS to help you find the nearest retailer selling the product.

Dump Windows for Chrome OS

Google exec: 60% of businesses could dump Windows for Chrome OS