Search a value to complete database

  • Gaurav Goel

    Valued Member

    Points: 69

    Comments posted to this topic are about the item Search a value to complete database

  • martin.l

    Newbie

    Points: 3

    Hi Gaurav,

    very helpfull query, thank you.

    Can i suggest to add checks if the temporary tables already exists before creating them?

    Something like:

    IF OBJECT_ID('tempdb..#SearchResult') IS NOT NULL DROP TABLE #SearchResult

    IF OBJECT_ID('tempdb..#SearchTestResult') IS NOT NULL DROP TABLE #SearchTestResult

    IF OBJECT_ID('tempdb..#Table_Name') IS NOT NULL DROP TABLE #Table_Name

    Thanks again,

    Martin

  • Gaurav Goel

    Valued Member

    Points: 69

    Hello,

    Thanks for the comment Martin.

    Yes in case you are using it as a query then these checks need to be applied but if you are using as a proc(as I was using) then there is no need it will slow down the process.

    Thanks

    Gaurav Goel

  • toufeeqt

    Valued Member

    Points: 55

    Hi Gaurav,

    another suggestion is that you add a variable for schema as I see you assume that all tables will be in a "dbo" schema

  • tyboutmarc

    SSC Rookie

    Points: 28

    This doesn't search in the complete database.

    It walks through the .dbo schema only. If the tables are in another schema, the script fails.

    Shouldn't you use

    Declare @Schema_Selected varchar(50)

    set @Schema_Selected= 'whatever'

    with ['+@Schema+']' instead of the hard-coded schemaname?

    or even beter, retrieve the schemaname. Why do you use a cursor? In this cases I use a WHILE loop.

    I work since SQL2000 with this script:

    CREATE PROC [dbo].[SearchAllTables]

    (

    @SearchStr nvarchar(100)

    )

    AS

    BEGIN

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

    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

  • toufeeqt

    Valued Member

    Points: 55

    Also to make provisions for Databases,schemas,Tables and fields names that have spaces by adding "[" and "]" to the strings

  • saintsam

    Grasshopper

    Points: 15

    tyboutmarc (3/14/2013)


    This doesn't search in the complete database.

    It walks through the .dbo schema only. If the tables are in another schema, the script fails.

    Shouldn't you use

    Declare @Schema_Selected varchar(50)

    set @Schema_Selected= 'whatever'

    with ['+@Schema+']' instead of the hard-coded schemaname?

    or even beter, retrieve the schemaname. Why do you use a cursor? In this cases I use a WHILE loop.

    I work since SQL2000 with this script:

    CREATE PROC [dbo].[SearchAllTables]

    (

    @SearchStr nvarchar(100)

    )

    AS

    BEGIN

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

    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

    Nice one.

  • toufeeqt

    Valued Member

    Points: 55

    Here you go I updated your code to reflect some of the suggestions

    set nocount on

    --Cleanup

    if exists (select * from tempdb.sys.tables where name like '#SearchResult%') drop table #SearchResult

    if exists (select * from tempdb.sys.tables where name like '#SearchTestResult%') drop table #SearchTestResult

    if exists (select * from tempdb.sys.tables where name like '#Table_Name%') drop table #Table_Name

    --Variable Delaration

    Declare @keytosearch varchar(max), @Database_Selected varchar(50)

    set @keytosearch ='%Toufeeq%'

    set @Database_Selected= 'MIS-Stage-Production-Analysis'

    Declare @Table varchar(100), @Table_Name Cursor, @Count_Column int, @Result nvarchar(4000),@ID int,@ID_inserted int,@Count_Table int, @data_type varchar(10)

    set @ID_inserted=0

    set @Count_Table=0

    DECLARE @schema varchar(250), @column varchar(max), @Column_Name CURSOR

    --Variable Delaration end

    --Second Cursor start

    declare @informationName varchar(250), @SysName varchar(50), @Var varchar(5)

    set @informationName='[' + @Database_Selected+'].'+'information_schema.COLUMNS'

    Set @SysName='[' +@Database_Selected+'].'+'sys.objects'

    Set @Var='u'

    --Database Selected start

    Create Table #SearchResult(ID int identity(1,1) not null, TableName varchar(50), ColumnName varchar(50))

    Create Table #SearchTestResult(ID int identity(1,1) not null, ColumnName varchar(max))

    Create Table #Table_Name(ID int identity(1,1) not null, table_name varchar(50))

    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Column_Nam')

    drop table Column_Nam

    insert into #Table_Name exec ('Select name from '+@SysName +' where type= '''+@Var+'''')

    --First Cursor open

    SET @Table_Name = CURSOR FOR Select table_name from #Table_Name

    open @Table_Name

    Fetch Next from @Table_Name into @Table

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @Count_Table =@Count_Table+1

    --Second cursor opened

    --print 'Select column_name from '+@informationName +' where table_name= '''+@Table+''''s

    --Print 'SELECT table_schema,table_Name,column_name,data_type INTO Column_Nam FROM '+ @informationName +' where table_name = '''+@Table+''''

    Exec('SELECT table_schema,table_Name,column_name,data_type INTO Column_Nam FROM '+ @informationName +' where table_name = '''+@Table+'''')

    SET @Column_Name = CURSOR FOR (select table_schema,column_name from Column_Nam )

    OPEN @Column_Name FETCH NEXT FROM @Column_Name INTO @schema,@column

    set @Table='[' +@Database_Selected+'].['+ @schema +'].['+@Table+']'

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @data_type= (SELECT top 1 data_type FROM Column_Nam Where column_name= @column)

    if @data_type is not null and (@data_type='varchar' or @data_type='nvarchar' or @data_type='Text')

    begin

    set @Result=null

    /*if @column like '%-%' begin

    set @Result =('SELECT ''' + @column +''' FROM ' + @Table

    +' Where ''' + @column + ''' Like '''+@keytosearch+'''') end

    else*/

    set @Result =('SELECT [' + @column +'] FROM ' + @Table

    +' Where [' + @column + '] Like '''+@keytosearch+'''')

    Print @Result

    insert into #SearchTestResult exec(@Result)

    set @ID=0

    set @ID=(Select ID from #SearchTestResult where ID= @@IDENTITY /*Scope_identity()*/ )

    if @ID is not null

    begin

    select @ID_inserted=(select top 1 ID from #SearchResult order by ID desc)

    if coalesce(@ID_inserted,0) <> @ID

    insert into #SearchResult values (@Table,@column)

    end

    end

    FETCH NEXT FROM @Column_Name INTO @schema,@column

    END

    CLOSE @Column_Name

    DEALLOCATE @Column_Name

    --Second cursor closed

    drop table Column_Nam

    Fetch Next from @Table_Name into @Table

    End

    close @Table_Name

    Deallocate @Table_Name

    --First Cursor Closed

    Select * from #SearchResult

  • Gaurav Goel

    Valued Member

    Points: 69

    Hello All,

    Thanks for your comments and suggestions.

    Also thanks Grasshopper for modifying the code.

    I made a basic version for searching throughout the database and it can be extended as per your needs.

    Thanks

    Gaurav Goel

  • douglas.bentley

    SSC Enthusiast

    Points: 110

    Love this!!! :w00t:

    Could it be modified to search for patterns? Like trying to find Social Security Numbers as long as they were formatted

    where pattern= 'xxx-xx-xxxx'

    That would be the ultimate add at least to me.

    I will work on doing this, however, my level is no way to forum's.

  • Gaurav Goel

    Valued Member

    Points: 69

    Yes douglas.bentley, you can modify it according to your requirement. There are some wild card characters in sql server which can help you out with this.

  • martink-1041194

    SSC Journeyman

    Points: 75

    The original query did not work on CA Clarity database, but produced hundreds of errors.

    The modified query works like a charm.

    Thanks

    Martti K.

  • johnt 57530

    Grasshopper

    Points: 16

    Perhaps I am missing something, but aren't you performing a whole lot more work than is necessary? When you execute the following dynamic SQL:

    set @Result =('SELECT ' + @column +' FROM ' + @Table +' Where ' + @column + ' Like '''+@keytosearch+'''')

    … aren’t you actually storing the column value of every record in the entire table that matches your search criteria? If all you really want to know are the unique tables/columns that contain the value, I would think it much more efficient to just select the top(1) record or use an “exists” statement, that way as soon as the first matching record is found it exits the cycle rather than continuing to scan every record in the entire table, which could be very expensive when doing a “like” comparison on a large table.

  • hurcane

    SSChasing Mays

    Points: 623

    Could it be modified to search for patterns? Like trying to find Social Security Numbers as long as they were formatted

    where pattern= 'xxx-xx-xxxx'

    SQL has very limited support for patterns and no regular expression support built in. Check out this SO question.

  • RichB

    SSCrazy Eights

    Points: 9651

    Ok... so even stepping away from the question that is begging to be asked of why...

    Would it not be better to add a bunch of 'or' additions, so that when you end up scanning all 200 unindexed columns on the big table you only have to scan it once?

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply