Voice of the DBA

Author: way0utwest

  • Editorial Republish: The Value of Data

    I’m busy today, in the Redgate office, rehearsing and practicing for the SQL Privacy Summit.

    Today I’m republishing The Value of Data.

  • Getting All Users

    I saw someone that wanted to get all users from all databases on their instance. Seems like that ought to be simple, right?

    The user wanted to use sp_MSforeachdb to query users, but wanted a single result set for all databases.  Why you need this, I’m not sure. I guess some auditing report. Or maybe looking to clean up and remove unnecessary users?

    In any case, this turned out to be easy with a caveat. It’s not in one single statement..

    DECLARE @MyUsers TABLE ( dbname VARCHAR(200), principalname VARCHAR(200), principalSID VARBINARY(MAX))
      INSERT @MyUsers
    
EXEC sp_MSforeachdb 'select ''?'', name, sid from [?].sys.sysusers'

    SELECT top 1000
      *
      FROM @myusers

    Use the ? in the query to get the users from every db. Then store this in a table that you can pull back all the data.

    This worked for me, though since I have some database names with a hypen (-) in them, I needed the brackets to get this to work.

  • Reaching the Refactoring Redoubt with #SQLPrompt

    I enjoy themes, and when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it. I wrote about Code Snippet Cove first, and this post finishes my movement around the map. The other items I’ve read about are:

    Refactoring is Hard

    One of the things that I struggle with, and I think many others do as well, is refactoring. Not that refactoring or changing code is hard, but remembering to get all the various places changed can be a challenge. It seems that any refactoring might change an item in three or four places, but then forget one more, which leads to bugs and embarrassment.

    Fortunately, SQL Prompt has a few things to help. Smart Rename is one of these, which will let you know about all of the dependencies and places where code will change. This makes quick work of renames, which is something I’ve appreciate in other languages, but not T-SQL before this.

    Note that renaming objects is still disruptive and can break connected applications, so this still should be undertaken with care, and in synchronization with renaming the objects in application code.

    There are also the renaming of variables, which can be helpful when you realize that your short or obscure names aren’t helpful to others on your team.

    Perhaps my favorite refactoring feature is the Encapsulate as New Stored Procedure. I think more people should be using stored procedures, and while we might experiment and develop code as a batch or query, we want to move this to a stored procedure. This feature makes this simple for developers, and it’s one you should ensure they know how to use.

    There are other items in the treasure map, many of which are useful, but you’ll want to practice with them and decide which ones are most useful and helpful in your daily work.

    If you haven’t tried SQL Prompt, download an eval today and give it a go. I bet you will love it and ask for your own copy.

  • Interviewing Avatars

    Imagine that you go to interview a candidate over Skype or even a phone call and the first thing you see or hear is: “Hi, my name is Hank, and I am an avatar. I will answer all your questions about Nick factually and completely, as if you’re talking directly to Nick. Are you still looking to fill this position?”

    That’s a quote from a blog post about HR and hiring issues. In this post, the author talks about a new system that allows a job seeker to use an avatar as a way to quickly apply for thousands of jobs. While this might be nice for the applicant, would any of you want to interview an avatar? Maybe you would a few times, or maybe you’d even start to like this as a way to get some basic information about a candidate. Of course, if the candidate has loaded up all sorts of technical facts from Inside SQL Server or How Would You Move Mount Fuji, then maybe you’re less interested.

    One one hand this is scary. On the other it’s awesome. I’d love to be able to answer some questions and provide some data about my career and goals and let an avatar go through initial screens. I wouldn’t want this to be used anywhere past the initial interview, but certainly it would help me to present a little more of myself in an interactive format. It could be a nice brand, and how fun would it be to interact with Steve’s avatar?

    In some sense, we might move in this direction. After all, this just levels the playing field between candidates and employers. Plenty of recruiting and HR people use their own software or algorithms to make quick decisions about a large number of applicants. I certainly know that you need to stand out with your brand and ensure your resume and online profile attract attention and this could help with that.

    Think about this. Is there a way to better present the data and information about ourselves, using technology to respond and help ensure that the position is a good fit for each of us? Hiring managers might use this, why not us? Actually, they are using Alex and Mya. Would this be a better way for you to control and show off some of your work? Even have the avatar show how you solved problems or responded to different situations? While this seems silly or science fiction, I wouldn’t be surprised to see this type of avatar-avatar interaction being used early in the process to decide if both parties should move forward.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.