Monthly Archives: June 2014

Code

Search entire SQL Server database and all tables for a specified string

Having a neatly organized datalayer, perhaps a great ORM will make any developers life easier. But there are times when the best architecture simply isn’t good enough to answer a simply question like: “Are there any occurences of ‘a-specific-peace-of-text’ in the database?”. Sometimes you just need to look through all the tables and views to answer this.

Trivial, but inevitable.

Luckily, using the stored procedure defined below, originally by Narayana Vyas Kondreddi, this becomes a breeze. Simply create it in the database and use it like so:

exec SearchAllTables ‘gummy bears’;

This executes a case insensitive search on all tables in the relevant database, and neatly spits out the fields where the string occurs in.

Here is the 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 + http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(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')
                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
END

Enjoy!

Code, Projects

ASP.NET MVC 5 Template for Xamarin Studio

Recently the latest version of Xamarin Studio has been released, along with Mono 3.4. And honestly, no one can deny that cross platform .NET development just keeps on getting better and better. I still see a great future for especially Mono if the open source community and the people at Xamarin manage to keep their current pace of improvements. #kudos++

Although with every update of both Xamarin and Mono the bar is set higher, some fields of interest seem to be lacking to get updated. Clearly the mobile platforms, like iOS, Android and Windows Phone seem to get all the attention. But the web platform seems to lose focus in the Xamarin/Mono tagteam, especially ASP.NET.

For example, the native template for ASP.NET is still set to version 3. Which was the non-MyGet version, if you remember. And that means b*tchy to update and cumbersome to update to recent versions. However, it is still possible to actually run and debug new ASP.NET MVC sites on Mono.

To this end, I’ve created a simple and clean setup for a Xamarin project that allows you to run an ASP.NET MVC 5.1 site on Mac OSX. In short:

Fork it on GitHub

Grab a fresh copy from the git repo and you’ll get:

  • A clean ASP.NET MVC 5 application with RAZOR support
  • Can be run entirely from Xamarin Studio on Mac OSX
  • Can be updated and extended through NuGet
  • It just works…

Known Issues

Do note that an Empty ASP.NET MVC 5 project created in Visual Studio won’t run by default in Xamarin. You have to plumb a bit here and there. If you’re curious on what you need to do to make things blend on Xamarin Studio on Mac, take a look at the changes I’ve made in the repo.

Tip: Want to reproduce it yourself? All required changes to run ASP.NET MVC 5 on Mac are neatly documented as seperate commits.

To summarize these changes, the current limitations for a clean setup seems to be:

  • App relative paths don’t work, e.g. using tildes like ~/assets/style.css
  • The view’s web.config has teh disabled
  • Mono currently supports .NET 4.5, not 4.5.1
  • routes.LowerCaseUrls isn’t supported (yet)
  • System.Web.Entity reference isn’t supported (yet)