Search for data on any given database and on any column

By Rajib Bahar at November 18, 2009 15:47
Filed Under:
In some of my previous project, I had to find out whether a particular data existed on anywhere in any database. By that I mean, if I wanted to find out whether a particular data, for example, "manager" made in to any table. It's a trivial issue if you know the database table structure and column names. What if you did not have that benefit and wanted to do a massive manhunt for the data? I have written similar script such as the one below, and forgot to keep track of it in my library. Finally, my slowly deteriorating and dull memory served as a motivation for this post. The way it works is a) by getting list of all available databases, b) using dynamic sql to capture tables and column information, c) comparing sought after data against any column that can be converted to varchar type. If you need more explanation then comment please. 
 
Here is the snippet:
 

SET NOCOUNT ON

 

DECLARE @SoughtAfterValue VARCHAR(8000)

 

SET @SoughtAfterValue ='Manager'

 

DECLARE @Tmp TABLE

(

      ID INT IDENTITY(1,1)

    , DBName VARCHAR(255)

)

 

INSERT INTO @Tmp(DBName)

SELECT name

FROM sys.sysdatabases

 

DECLARE @i INT

DECLARE @total INT

 

SET @i = 1

 

SELECT @total = COUNT(*)

FROM @Tmp

 

DECLARE @sql Nvarchar(max)

 

WHILE @i <= @total

BEGIN

 

            set @sql= N'

            use ' +(

                SELECTDBName FROM @Tmp WHEREID = @i

            ) + '

           

            DECLARE @TmpQry TABLE

            (

                    ID INT IDENTITY(1, 1)

                  , Qry VARCHAR(8000)

            )

           

       

        if exists

                  ( SELECT

                  *

        FROM INFORMATION_SCHEMA.COLUMNS

            WHERE TABLE_CATALOG = '''

        +

            (

                SELECTDBName FROM @Tmp WHEREID = @i

            )

        + '''

            )

            BEGIN

 

            INSERT INTO @TmpQry (Qry)

        SELECT

                  ''SELECT CAST('' +COLUMN_NAME + '' AS VARCHAR(8000))[Result] '' +

                ''FROM '

            +

            (

                SELECTDBName FROM @Tmp WHEREID = @i

            )

                  + '..' + ''' + TABLE_NAME  

                  + '' WHERE CAST('' +COLUMN_NAME + '' AS VARCHAR(8000)) like ''''%' +@SoughtAfterValue + '%''''''

                   

        FROM INFORMATION_SCHEMA.COLUMNS

            WHERE DATA_TYPE NOT IN

            (

                  ''binary'',''varbinary'', ''image'', ''geography'', ''geometry'', ''timestamp'',

                  ''xml'',''hierarchyid'', ''sql_variant''

            )

 

            END

 

            DECLARE @i INT

            DECLARE @Total INT

            DECLARE @CurrentQryVARCHAR(8000)

            DECLARE @CurrentQry2VARCHAR(8000)

           

            SET @i = 1

 

            SELECT @Total = COUNT(*) FROM@TmpQry

           

            WHILE @i <= @Total

            BEGIN

                 

                  SELECT @CurrentQry = Qry     

                  FROM @TmpQry WHERE ID =@i

           

                  SET @CurrentQry2 = ''ifexists('' + @CurrentQry +  '') begin select ''''''+ REPLACE(@CurrentQry,'''''''', '''''''''''') + '''''' [Query Ran] '' + @CurrentQry + '' end''

 

                  exec(@CurrentQry2)

 

                  SET @i = @i + 1

 

                  CONTINUE

            END

           

      '

 

exec(@sql)

 

      SET @i = @i + 1

      CONTINUE   

END

  

AdventureWorks 2008R2 November CTP Beta is out

By Rajib Bahar at November 18, 2009 06:06
Filed Under: SQL

Two weeks ago I struggled to get the "AdventureWorks 2008" to work on my "SQL 2008 R2" server. I ended up building a 2005 version of it. It appears they have released a beta version of it for the R2 release at CodePlex. Help yourself at your own learning venture.

http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=24854

 I'll update this entry and/or have a followup entry as time permits to share my experience.

Interviewing Hans Christian of RASA at KFAI Radio's Sangam Program

By Rajib Bahar at October 18, 2009 18:00
Filed Under:


We aired this interview through Sangam program co-hosted by Ahmed Naumaan.

RASA is the collaboration between Hans Christian and Kim Waters. Their work is about devotional music.
----

Feel free to reach out to me at my blog:
http://www.rajib-bahar.com

Other sites of interest:
http://www.kfai.org
http://www.youtube.com/kfairadio
http://www.rasa-music.com

http://www.youtube.com/rajib2k5

Trend

By Rajib Bahar at October 11, 2009 10:37
Filed Under: SQL, Interesting, .NET
It's a challenge to keep track of things as they are constantly changing (just like my tools of the trade). Don't you wish you are always on top of all things, but, other things in life get in the way... Some time I get immersed in work and study that I forget the rest of the world. 

Now, that I have this challenge at hand, the question is how am I going to pull myself through this one? I found a tool on it's face seemed quite interesting. My work involves working with SQL server. If I want to learn the noise around sql server then it will show what everyone in the world is thinking about it. I believe it utilizes the RSS feed from twitter. The people at codemunch.com wrote this very simple and useful tool (depending upon your value on it). I found their site while trying to find the trend on sql server. 

How to use it?

Here is what the URL for the trend on "SQL Server" would look like. 


Simply change the "q=" part to your preferred query. For example, you want find the latest on sql then the query string would look like "q=sql". If you want to use words with spaces in it then place a %20 between each word. It's as simple as that.

Other trends that I may use for myself include:

I am sure other Gurus may have different opionion how to find trend. After all, there are other reliable methods such as reading the book on latest tools, online articles, blogs, visiting focus groups that provide training in similar area. My intention with this entry is to talk about what other tool is out there to complement such effort. Anyways, I do find the existing methods useful from time to time. I have been to the local sql server user group many times. Most of the times, I went their for the swags, and free softwares. Most recently, I went to the "SQL Server Summit" hosted in minnesota with sponsorship from Digineer. I attended one of the reporting services session to learn about the challenges the MVP had in his project. Toward the end, I asked a question about SSRS and sharepoint and got good feedback from several peers. The presenter moved on to take other closing questions. In the meantime, I had this urge to tinker. I took out a yo-yo which I picked up from Digineer's booth prior to that presentation. I thought I'd play with it while the presentation ends. Unfortunatley, I did not know that the yo-yo's extra feature included very loud sound, and shiny lights. Yes, it was an awkwardly funny moment and all eyes were on me. I was in a panic mode trying to silence the alarm. Luckily for me, I did not get bounced out as I worked with some of the people in there running the show. I had an occassion to collaborate with Mark V. from digineer in the past. He was sitting next to me and cracked open. He did not know that the yo-yo would do that. I am making a note to avoid yo-yo's next time. BTW, I transferred the yo-yo to my nephew, but, I digress.

ConnectionStrings - a tribute

By Rajib Bahar at September 29, 2009 07:25
Filed Under: SQL

I use the ConnectionStrings.com site many times. Whehter it's MS SQL server or Oracle or MySql, they have the useful snippet of code for the connection string. It usually saves me time from looking up the manual. I thought I would dedicate this entry just for them.

 

If you don't know what connection string is then you are lucky to be reading this entry. In short, connection string is what defines how we connect to data sources and destinations. It enables application to talk to the database so that information is persisted in the RDBMS or target data structure. 

 

A sample connection string may look like this... (Yes... I borrowed the connection string below from their site :) )

 

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Obama Health Care Speech At Target Center In Minnesota Part 1

By Rajib Bahar at September 12, 2009 18:00
Filed Under:


Obama Health Care Speech At Target Center In Minnesota Part 1

http://www.youtube.com/rajib2k5

TSQL Cheatsheet - Need more to build on this list

By Rajib Bahar at September 01, 2009 07:31
Filed Under: SQL

I use these cheatsheets related  to TSQL and/or SQL server from time to time.

 

I can not count on my memory, but, it would be nice to have a photographic one.

 

Enough of my rambling, here is the list:

TSQL Cheatsheet found at Scribd - http://www.scribd.com/doc/399147/TSQL-Cheat-Sheet

SQL Server Cheatsheet found at Scribd - http://www.scribd.com/doc/15235350/SQL-Server-TSQL-Cheatsheet

Dave Pinal or SQL Authority's Cheatsheet - http://www.pinaldave.com/sql-download/SQLServerCheatSheet.pdf 

 

Did I miss any good ones? I'll update this 

Moyna Go performed by Zeenat Ara Muna

By Rajib Bahar at August 12, 2009 18:00
Filed Under:


Moyna Go (found the lyrics at Banglalyrics.com)

Oo Amar Moyna GO
Oo Amar Moyna GOo
Kar Karone tumi ekela
Kar behone behone diba nishe tumi utola
She toh aashbe na
She toh firbe na, firbe na
Oo Amar Moyna GOo



Dur dur dur dur dur pane aanmone chahiya
Ki berage ragini jao ghaiya
Shobuje Shobuje vora bonanani
Gourobe fagun buje janoni
Hayre hayre buji ta janoni



Oo Amar Moyna GO
Oo Amar Moyna GOo
Kar Karone tumi ekela
Kar behone behone diba nishe tumi utola
She toh aashbe na
She toh firbe na, firbe na
Oo Amar Moyna GOo



Zor zor zor dunoyone zor zor zoraye
keno thako bishade mon bhoraye
Ja kishu haraye gelo jaak na
Neel akashe mele paakh na
Daar daar mele paakhna



Oo Amar Moyna GO
Oo Amar Moyna GOo
Kar Karone tumi ekela
Kar behone behone diba nishe tumi utola
She toh aashbe na
She toh firbe na, firbe na
Oo Amar Moyna GOo

"Moyna Go" performed by Zeenat Ara Muna

http://www.youtube.com/rajib2k5

My extra-curricular activities with KFAI radio

By Rajib Bahar at July 23, 2009 02:01
Filed Under:
Hello, Friends,

This post deviates from my usual postings on SQL and various geeky topics...

After volunteering my time for various IT interest groups... I also volunteer my time at KFAI radio whenever time permits. KFAI radio recently asked me to create multiple videos for their fund raising effort.

I had a lot of fun building them as a humble volunteer.

Here is the link to the 1st video which is about what KFAI symbolizes.

http://www.youtube.com/watch?v=vZrpQ8QfZDc


The 2nd video falls under comedy category and will be released some time friday.



Please visit KFAI radio http://www.kfai.org or http://www.youtube.com/kfairadio and add them as your friend and/or subscribe if you like the content.

Regards,
Rajib Bahar

July 4th Fireworks, Eagan City, Minnesota - What it really sounded like?

By Rajib Bahar at July 10, 2009 18:00
Filed Under:


The previous clip did not have the sound we experienced.... so here is the response to it.

http://www.youtube.com/rajib2k5

Tag cloud

Month List