SQL Searh in ALL DataBase - SQL Snipplr Social Repository

Revision: 28560

at July 9, 2010 21:51 by Eloi

Updated Code

Create Proc Search
(
    @SearchStr nVarChar(100)
)
As
Begin

    Create Table #Results ( TableName nVarChar(370), 
                            ColumnName nVarChar(370), 
                            ColumnValue nVarChar(3630))

    Set NoCount On

    Declare @TableName nVarChar(256), 
            @ColumnName nVarChar(128),
            @SearchStr2 nVarChar(110),
            @ColumnValue nVarChar(4000)

    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 + ''', ' +
                    '''' + @TableName + '.' + @ColumnName + ''', ' +
                    'LEFT(' + @ColumnName + ', 3630) From ' + @TableName + ' (NOLOCK) Where ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            End
        End   
    End

    Select    TableName, ColumnName, ColumnValue
    From    #Results

End

Revision: 28559

at July 9, 2010 21:48 by Eloi

Updated Code

Create Proc Search
(
    @SearchStr    nVarChar(100)
)
As
Begin

    Create Table #Results (    TableName nVarChar(370), 
                            ColumnName nVarChar(370), 
                            ColumnValue nVarChar(3630))

    Set NoCount On

    Declare @TableName nVarChar(256), 
            @ColumnName nVarChar(128),
            @SearchStr2 nVarChar(110),
            @ColumnValue nVarChar(4000)

    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 + ''', ' +
                    '''' + @TableName + '.' + @ColumnName + ''', ' +
                    'LEFT(' + @ColumnName + ', 3630) From ' + @TableName + ' (NOLOCK) Where ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            End
        End   
    End

    Select    TableName, ColumnName, ColumnValue
    From    #Results

End

Revision: 28558

at July 9, 2010 21:47 by Eloi

Initial Code

Create Proc Search
(
    @SearchStr	nVarChar(100)
)
As
Begin

    Create Table #Results (	TableName nVarChar(370), 
							ColumnName nVarChar(370), 
							ColumnValue nVarChar(3630))

    Set NoCount On

    Declare @TableName nVarChar(256), 
			@ColumnName nVarChar(128),
			@SearchStr2 nVarChar(110),
			@ColumnValue nVarChar(4000)

    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 + ''', ' +
					'''' + @TableName + '.' + @ColumnName + ''', ' +
					'LEFT(' + @ColumnName + ', 3630) From ' + @TableName + ' (NOLOCK) Where ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			End
        End   
    End

    Select	TableName, ColumnName, ColumnValue
	From	#Results

End

Initial URL

Initial Description

Stored Procedure for search a string in all fields of all tables of one DataBase

Initial Title

SQL Searh in ALL DataBase

Initial Tags

search

Initial Language

SQL

Choose a language for easy browsing: