A SQL Query I find useful

Standard

While working on an Accounting Software package some years ago, where we have multiple companies in a single database with GUID’s as primary keys in some 100 tables I needed to find a specific GUID somewhere in the database.

Now, I have not written this this query, and for the life of me, I can not remember where I got it. I am posting it here because I found it immensely useful.

DECLARE @SearchStr nvarchar(100)

set @SearchStr = 'What you want to find'

CREATE TABLE #Results 
(

     ColumnName nvarchar(370),

     ColumnValue nvarchar(3630)

)

SET NOCOUNT ON

DECLARE @TableName nvarchar(256)

DECLARE @ColumnName nvarchar(128)

DECLARE @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','uniqueidentifier','decimal','int')
     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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s