Skip to main content

Search Whole database sample stored procedure


 

 ========================CREDIT=====================

-- 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


=============================END===========================

 DECLARE @SearchStr nvarchar(100)

SET @SearchStr = '9D2BAE6F-062B-4A86-8A92-B64F2AE46CAA'



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 ( 'uniqueidentifier')

                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

Comments

Post a Comment

Popular posts from this blog

Referenced assembly does not have a strong name

  Steps to create strong named assembly Step 1 : Run visual studio command prompt and go to directory where your DLL located.   For Example my DLL located in  D:/hiren/Test.dll Step 2 : Now create  il file using below command.    D:/hiren> ildasm /all /out=Test.il Test.dll   (this command generate code library) Step 3 : Generate new Key for sign your project.    D:/hiren> sn -k mykey.snk Step 4 : Now sign your library using ilasm command.    D:/hiren> ilasm /dll /key=mykey.snk Test.il so after this step your assembly contains strong name and signed. Jjust add reference this new assembly in your project and compile project its running now. codeproject.com/Tips/341645/Referenced-assembly-does-not-have-a-strong-name
Post a Comment
Read more

SQL Server(from 2012) FileTable Features some useful SQL Statements

The FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services - including full-text search and semantic search - over unstructured data and metadata.( FileTable ) Here is sequential SQL statements to enable,alter existing database for FileStream and create FileTable --check current config value EXEC sp_configure filestream_access_level; --reconfig GO EXEC sp_configure 'filestream access level',   2 Go RECONFIGURE GO -- chack file table enabled databases  SELECT DB_NAME(database_id) as [Database Name], non_transacted_access, non_transacted_access_desc ,directory_name FROM sys.database_filestream_options --  Enable a file table in an existing Database ALTER DATABASE TestDB  SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECT...
Post a Comment
Read more

update your package-lock.json according to what you have specified in the package.json file

  The objective of the   npm update   command is to update your   package-lock.json   according to what you have specified in the   package.json   file. This is the normal behavior. If you want to update your package.json file, you can use  npm-check-updates :  npm install -g npm-check-updates . You can then use these commands: ncu  Checks for updates from the package.json file ncu -u  Update the package.json file npm update --save  Update your package-lock.json file from the package.json file
Post a Comment
Read more