Saturday, February 27, 2010

Collapse all Solution Explorer items in Visual Studio 2010

Ever wanted to be able to collapse all items within Visual Studio's solution window? This is a nifty little Visual Studio macro that I came across a few years ago and have been using successfully in Visual Studio 2005, Visual Studio 2008 and now in the Visual Studio 2010 RC.

I'll overview how to install it below in case you're unsure how to do it but I have this bound to the key combination Ctrl+Shift+` as ReSharper now uses my previous key combination of Ctrl+` for it's new bookmark explorer.

Anyway, here's the Visual Studio Solution Explorer item Collapse All macro:

Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports System.Diagnostics
'-----------------------------------------------------------
' CollapseAll Module
'-----------------------------------------------------------
' Simple macro that fully collapses all items in the 
' Solution Explorer rather than just the top level node
'
' To make live easier, bind it to a keyboard setting such
' as Ctrl+Shift+` which by default has no bindings (Ctrl+` is
' now used by Resharper
'
' Tested and works with:
' Visual Studio 2005
' Visual Studio 2008
' Visual Studio 2010
'
' Originally from: http://bit.ly/bmRu3W
'-----------------------------------------------------------
Public Module CollapseAll

    Sub CollapseTree()
        ' Get the the Solution Explorer tree
        Dim solutionExplorer As UIHierarchy
        solutionExplorer = DTE.Windows.Item(Constants.vsext_wk_SProjectWindow).Object()

        ' Check if there is any open solution
        If (solutionExplorer.UIHierarchyItems.Count = 0) Then
            Return
        End If

        ' Get the top node (the name of the solution)
        Dim rootNode As UIHierarchyItem = solutionExplorer.UIHierarchyItems.Item(1)
        rootNode.DTE.SuppressUI = True

        ' Collapse each project node
        Collapse(rootNode, solutionExplorer)

        ' Select the solution node, or else when you click 
        ' on the solution window
        ' scrollbar, it will synchronize the open document 
        ' with the tree and pop
        ' out the corresponding node which is probably not what you want.
        rootNode.Select(vsUISelectionType.vsUISelectionTypeSelect)
        rootNode.DTE.SuppressUI = False
    End Sub

    Private Sub Collapse(ByVal item As UIHierarchyItem, ByRef solutionExplorer As UIHierarchy)
        For Each innerItem As UIHierarchyItem In item.UIHierarchyItems
            If innerItem.UIHierarchyItems.Count > 0 Then
                ' Re-cursive call
                Collapse(innerItem, solutionExplorer)
                ' Collapse
                If innerItem.UIHierarchyItems.Expanded Then
                    innerItem.UIHierarchyItems.Expanded = False
                    If innerItem.UIHierarchyItems.Expanded = True Then
                        ' Bug in VS 2005
                        innerItem.Select(vsUISelectionType.vsUISelectionTypeSelect)
                        solutionExplorer.DoDefaultAction()
                    End If
                End If

            End If
        Next
    End Sub
End Module

In case you've never installed a Visual Studio macro before, here's a couple of instructions:

1. In Visual Studio, press Alt+F11 to load up the Visual Studio Macro editor (or View > Other Windows > Macro Explorer > Double Click on "Module1" in "My Macros")
2. Either create a new module of it it's not in use, you can edit Module1 and past in the code above
3. Save and close the Visual Studio Macro editor
4. You should be back in Visual Studio so click "Tools > Options > Environment > Keyboard"
5. In the "Show commands containing" text box, enter "CollapseTree" and the macro you just created should be shown.
6. Make sure "Global" is selected in the "Use new shortcut in:" drop down list
7. Press Ctrl+Shift+` in the "Press shortcut keys:" text box
8. Click Assign
9. Click OK

You're done :)

Update via Graeme: Make sure your module name (in the above it's Module1) is the same as your file name otherwise Step 5 minght not work.

Collapse all Solution Explorer items in Visual Studio 2010

Useful Links:            Comments [15]  Trackback

Tags: Development | Productivity | Visual Studio

 Friday, February 19, 2010

Search every table and field in a SQL Server Database Updated

As some of my blog posts are a little out of date, I thought I would spend some time updating the most popular ones. As I use this script on a regular basis and there was an error with the original posting, I thought I'd update it with a "corrected" version to get things started.

If you want to see the original script, you can refer to How to search every table and field in a SQL Server Database. This one's just fixed :)

As a stand alone script

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'


    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Updated and tested by Tim Gaunt
    -- http://www.thesitedoctor.co.uk
    -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
    -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
    -- Date modified: 03rd March 2011 19:00 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL
            
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

As a re-usable stored procedure

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS

BEGIN

    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Updated and tested by Tim Gaunt
    -- http://www.thesitedoctor.co.uk
    -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
    -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
    -- Date modified: 03rd March 2011 19:00 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL
            
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
    DROP TABLE #Results
END

Search every table and field in a SQL Server Database Updated

Useful Links:            Comments [7]  Trackback

Tags: Helpful Script | SQL | SQL Server | Stored Procedure