Click here to monitor SSC

Jonathan has been working with SQL Server since 1999. He enjoys performance tuning, development and using SQL Server to provide appropriate business solutions. He is the founder and leader of the PASS SQL South West user group http://www.sqlsouthwest.co.uk , is a moderator at SQL Q + A forum ask.sqlservercentral.com and is on twitter at @fatherjack. He has spoken at SQLBits and SQL in the City, SQL Saturdays and local user groups across the UK and Europe.

TSQL development

Published 5 December 2011 3:00 am

or: How I Learned to Stop Worrying and Love the Bomb Word*

Microsoft Office Word is useful. There, I have confessed it, it’s useful to me while I am working with TSQL. It’s possibly only the search and replace function that I like but it’s wrapped in Word so I have to take it like that. I use it a lot to help me capture the results of a stored procedure.

SP_WhoLet’s say we want to review what processes are connected to our server so we execute the stored procedure sp_who. This is useful but maybe we want to store this information for consumption later, or for comparison with another result set. We cant join to a stored procedure so we need to get this data into a table and then we can join to it.

We can get the results of a TSQL query into a table by executing a query along the lines of:

SELECT  1 AS [Numbers] ,      
       
'Information' AS [Words]
INTO    #temp

but this has drawbacks: if the table already exists then the code needs to take a different form and if the data collected first was not a good sample then you will get insert errors later on. Checking in TempDB for the details of the temp table created above we see that the columns are pretty restrictive:

USE [tempdb]
GO

SELECT  [t].[name] ,
       
[t].[object_id],
        [c].[name],
       
[c].[user_type_id],
       
[t2].[name],
       
[t2].[precision],
       
[c].[max_length]
FROM    [sys].[tables] AS t
       
INNER JOIN [sys].[columns] AS c ON [t].[object_id] = [c].[object_id]
       
INNER JOIN [sys].[types] AS t2 ON [c].[user_type_id] = [t2].[user_type_id]
WHERE   [t].[name] LIKE '%temp%'

The Words column is set to 11 characters in length because we inserted ‘Information’ which is 11 characters long:
TempTableProperties

sp_who02We wont know what data we will get from sp_who so creating a table on the fly is ruled out. We need to know what the data might look like and cater for that. Books OnLine is our friend here, and Word is about to justify it’s installation on your PC. Open Word and then locate the description of the stored procedure in Books OnLine and in particular the Result Sets section, next select the contents of the whole table, copy it and paste it into Word. Check the full information about the procedure here – http://msdn.microsoft.com/en-us/library/ms174313.aspx.

This will paste into Word as a table. Select the description column and delete it. Dont just hit the delete key, you need to delete the whole column. ConvertTableToTextNext select the whole table and move to the Tables, Layout menu and select Convert to Text which will in turn ask you how to delimit the columns.

Choose ‘commas’.

Commas

ShowcharactersNow make sure that paragraph marks are shown and you should then be left with something like this:

Paragraph

Let’s get to work making some edits and with Find/Replace.

  1. Take out the first line and type in “CREATE TABLE TempTable (
  2. Press Ctrl and H and in the Find What box type a single comma. In the Replace With box type ” ” that is a single space character. Make the change by clicking Replace All
  3. Press Ctrl and H and in the Find What box type a “^p”. In the Replace With box type “, ^p” that is a comma followed by a space character and then ‘^p’. Make the change by clicking Replace All.
  4. Place a closing ‘)’ at the end of the text, copy it all and paste it into an SSMS query.
  5. You will need to remove an extra comma on line 1 and possibly 2 on the last lines.

Executing this script will create the table and then this sort of query will work:

INSERT INTO temptable EXEC [sys].[sp_who] 
SELECT * FROM  temptable

If you want to store results of a stored procedure over time then simply add a column to the table creation script that has a default value of GetDate():

CollectedOn DATETIME DEFAULT GETDATE()

and then alter the INSERT script slightly:

INSERT INTO[dbo].[TempTable]
            ([spid],
             [ecid],
              [status],
              [loginame],
              [hostname],
              [blk],
              [dbname],
              [cmd],
             [request_id] 
            ) EXEC [sys].[sp_who] 

and you are logging the data along with when it was collected.

I hope this tip saves you some time working with results from stored procedures, let me know how you get on and if you have any time saving techniques you’d like to share.

 

* – Blog title is a poor play on the words of a great film title – “Dr Strangelove or: How I Learned to Stop Worrying and Love the Bomb”.

** – Be careful that you run this on a test server where there isn’t going to be a table already called temp or you will see odd results. TempDB holds all temporary tables so could have lots of objects with the same name.

16 Responses to “TSQL development”

  1. Anonymous says:

    Interesting Finds: December 5, 2011

  2. Anonymous says:

    I hate to say so, but I still use gvim (GUI descendent of the old vi) for a lot of programming-related editing. It has syntax coloring for lots of languages, and full regular expression search and replace that can handle a lot of different tasks that are very difficult in regular editors.

  3. Edafe_Onerhime says:

    Nicely done fatherjack. Now I know about the ^p replacement in Word, it’s going to come in handy.

  4. Anonymous says:

    Hi

    I normally just paste directly into Excel – SET NOCOUNT ON, set the query output results to Text, Tab Delimited and copy and paste.

    The Excel sort and filter options can then be used where needed plus additional calculations can be done if needed.

    Most of my result sets have lots of columns which Excel handles much better than Word.

    Regards

    Jim

  5. Anonymous says:

    I have recently found Notepad++ and the global replace functionality on it is spectacular! I also used to use Word for that, but I like Notepad++ better. One problem is that it doesn’t format into “tables” so you cannot “simply” delete a column, but the regex expression support for global replace has allowed me to bypass that hurdle with some creativity when I’ve run into that.

  6. Anonymous says:

    Word is great, but Notepad++ is even better for many kinds of text manipulation, because it highlights beginning and ending parentheses and will do color text formatting for many different programming languages, multiple documents open in tabbed format, create your own user-defined formats, etc.. Also it’s free.

    Available at: http://notepad-plus-plus.org/

  7. Anonymous says:

    Count this as a third vote for Notepad++. To me it is indispensable.

  8. Anonymous says:

    I too like Notepad++. BTW you can do Alt + Left mouse to select columns.

  9. Anonymous says:

    I agree with the comments about Notepad++ in general, but I think you’d be hard pressed to do the same things as outlined in this article.

  10. Anonymous says:

    I usually use Notepad, Editpad, and Excel as my “helper” applications.

    For this, I would have just copied that table and pasted into excel, deleted column c & row 1, placed a comma in c2 and dragged it down to the second last row. Copy and paste back into query analyzer and away we go

  11. Anonymous says:

    I agree with Jim, I take the results directly from the grid and put them into Excel for much of this. really easy for quick and dirty little things (like SP_Who…..)

  12. Anonymous says:

    This is a script to do what he describes in this article for the KEDIT editor. It helps to have a smart text editor.
    ** AutoEdit.kex
    ‘top’
    ‘c/Column, Data type/CREATE TABLE TempTable (/*’
    ‘c/,/ /**’
    ‘bot’
    ‘c/ /)/’

  13. Anonymous says:

    I think if you choose ‘Other’ and specify ‘space’ (instead of ‘commas’) in Convert Table to Text dialog you can get rid of step 2 in your find and replace 5-step process.

  14. Anonymous says:

    I personally loathe using Word for almost anything, and coding in particular. I use Notepad++ which has far more useful options for coders than Word has ever even considered, including highlighting various keywords in almost any language out there and doing text comparisons between versions of code.

    Notepad++ is also free, and can be installed on any machine without needing admin rights (a big problem in many organizations as machines are often locked down and IT will only install approved and packaged applications).

    All that having been said, I even prefer using SSMS’s built-in editor over Word.

  15. Anonymous says:

    Um….why? You can hit sysprocesses directly in SQL Server 2000 and prior. In SQL Server 2005 and later, you hit sys.dm_exec_requests. I haven’t run sp_who or sp_who2 for almost 15 years.

  16. Anonymous says:

    Emacs with tables mode or org mode can do this hundreds of times faster with 1/10th the memory overhead. Beyond a certain size, word starts to crap out, but with Emacs, you don’t have to worry about the document size.

Leave a Reply