Sql Saturday #332 Interviews at KFAI Radio

By Rajib Bahar at December 25, 2014 01:39
Filed Under: Data, Database, SQL, SQL Saturday, SQL Server

 

 

Rick Krueger, director of PASSMN stopped by KFAI radio to discuss the interviews conducted by Dan English & Brian Larson of Superior Consulting. He talked about the significance of this event & the kind of impact it makes for fellow database professionals.

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

  

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;

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 

Kevin Kline's blog entry on code of ethics for DBAs

By Rajib Bahar at April 24, 2009 09:52
Filed Under: SQL

I noticed an interesting blog entry by Kevin Kline. He is talking about code of ethics for DBA professionals. I thought some of us may find it relevant.

 Enjoy the dialogue and debate at [http://sqlblog.com/blogs/kevin_kline/archive/2009/04/21/is-it-time-for-a-professional-code-of-ethics-for-dbas.aspx]

useful blog entries on Joins... using join in update or delete statement

By Rajib Bahar at March 26, 2009 07:00
Filed Under: SQL

CSV Customization in SSRS Subscription

By Rajib Bahar at February 20, 2009 04:47
Filed Under: SQL

I will appreciate if my colleagues and peers would consider looking at this thread I opened at SQL Server Central [http://www.sqlservercentral.com/Forums/Topic661376-1063-1.aspx]. Thanks and have a good day!

 

Update [10/01/2009]:

Thanks to Luke at [SQLServerCentral.com] forum I was able to find the answer. He pointed me to a MSDN article, which in turn pointed to this article [http://msdn.microsoft.com/en-us/library/ms156281.aspx]. That helped me figure out how to do it. If I have time then I'll produce steps on how to reproduce it later.

 

 

SQL Server Interview questions - Submit yours

By Rajib Bahar at January 26, 2009 06:44
Filed Under: SQL
I get a lot of questions from my peers about finding a good place to get SQL server interview questions. I have had the privilege of being both the interviewor and interviewee. So, I thought I'd put together a collection on my blog. I look forward to hearing your comments. Please post your favorite or most challenging questions on SQL server.

Here is the list of links to blogs and/or websites that I found useful:

VLDB - What's the largest database have you worked with?

By Rajib Bahar at January 19, 2009 15:52
Filed Under:
I am asking this question to all of my SQL Server colleagues. There is no right or wrong answer to this. I am interested in learning the experience others have with VLDB systems. Please share your stories. I will compile the responses and make a newer blog entry. That new entry will have your story and link back to your blog if you have any. Feel free to send your responses via facebook, twitter, plaxo, linkedin, livespace, and email. Thanks

Tag cloud

Month List