Search anything anywhere

  • Comments posted to this topic are about the item Search anything anywhere

  • Thanks Florian for a buggy script. That thing just fried my server. :angry:

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • Hi JacekO

    What is the problem?

    Greets

    Flo

  • No problem, great script.

    I could not resist the temptation and was trying to play an April's Fool joke on you. 😀

    Thanks.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • Dang! You got me! 😀

    I really feared that something might be damaged and started to review the script...!

    Have a nice day 😉

    Flo

  • It is a really nice script for SQL 2005 and SQL 2008 but it doesn't work in SQL 2000. Maybe that should be noted somewhere.

    And I really like you Garfield. It is first thing in the morning here in AZ and he looks the way I feel!

  • This is a good script. I had a use for something similar some time ago and created the sproc below with some additional parameters to limit searches.

    Create Procedure [dbo].[sp_ColumnValues]

    @iTableNamevarchar(100),

    @iColumnName varchar(100),

    @iWherevarchar(100),

    @idataTypevarchar(100),

    @ivaluevarchar(100)

    as

    /*

    Requires following inputs:

    @iTableName - Table to be searched. Use wildcard '%' to search all tables.

    @iColumnName - Column to be searched. Use wildcard '%' to search all Columns.

    Unlikely to work when searching multiple tables.

    @iWhere - Criteria to restrict results. Use the operator "in" to select multiple results.

    Use an expression that is always true such as 1 = 1 to get all results.

    This may result in a very long running query.

    Unlikely to work when searching multiple tables.

    @idataType - use this value to only search certain datatype ("int","char","decimal","smalldatetime")

    Use wildcard '%' to search all datatypes.

    @ivalue - value to be search for. Can use wildcards to broaden search.

    Example

    EXECUTE [MISDB].[dbo].[sp_ColumnValues]

    @iTableName = 'accounts'

    ,@iColumnName = '%'

    ,@iWhere = 'acct_nbr = 99999999'

    ,@idataType = 'decimal'

    ,@ivalue = '''%.62'''

    */

    If Len(@iTableName) = 0 set @iTableName = '%'

    If Len(@iColumnName) = 0 set @iColumnName = '%'

    IF OBJECT_ID (N'tempdb.dbo.#Tables',N'U' ) IS NOT NULL

    Begin

    Drop Table #Tables

    End

    Create Table #Tables(

    TABLE_Qualifier varchar(100),

    Table_Ownervarchar(100),

    Table_Namevarchar(100),

    Table_Typevarchar(100),

    Remarksvarchar(100))

    insert #Tables Exec sp_Tables

    IF OBJECT_ID (N'tempdb.dbo.##Columns',N'U' ) IS NOT NULL

    Begin

    Drop Table ##Columns

    End

    Create Table ##Columns(

    TABLE_QUALIFIERsysname,

    TABLE_OWNERsysname,

    TABLE_NAMEsysname,

    COLUMN_NAMEsysname,

    DATA_TYPEsmallint,

    TYPE_NAMEsysname,

    iPRECISIONint,

    LENGTHint,

    SCALEsmallint,

    RADIXsmallint,

    NULLABLEsmallint,

    REMARKSvarchar(254),

    COLUMN_DEFnvarchar(4000),

    SQL_DATA_TYPEsmallint,

    SQL_DATETIME_SUBsmallint,

    CHAR_OCTET_LENGTHint,

    ORDINAL_POSITIONint,

    IS_NULLABLEvarchar(254),

    SS_DATA_TYPEtinyint)

    DECLARE @getTableID CURSOR

    DECLARE @TableName varchar(100)

    SET @getTableID = CURSOR FOR

    SELECT Table_Name

    FROM #Tables

    Where Table_Type = 'TABLE' and Table_Name like @iTableName

    OPEN @getTableID

    FETCH NEXT FROM @getTableID INTO @TableName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    insert ##Columns Exec sp_Columns @table_name = @TableName

    FETCH NEXT FROM @getTableID INTO @TableName

    END

    CLOSE @getTableID

    DEALLOCATE @getTableID

    DECLARE @SqlStatement nvarchar(4000)

    IF OBJECT_ID (N'tempdb.dbo.##ColumnValues',N'U' ) IS NOT NULL

    Begin

    Drop Table ##ColumnValues

    End

    Create Table ##ColumnValues(

    cValuevarchar(100),

    ColumnNamevarchar(100))

    DECLARE @getValueID CURSOR

    DECLARE @vcolumn varchar(100)

    Declare @vTablevarchar(100)

    SET @getValueID = CURSOR FOR

    SELECT Column_Name,Table_Name

    from ##Columns

    Where Column_Name like @iColumnName and Type_Name like @iDataType

    OPEN @getValueID

    FETCH NEXT FROM @getValueID INTO @vColumn,@vTable

    WHILE @@FETCH_STATUS = 0

    BEGIN

    Select @SqlStatement =

    'Insert Into ##ColumnValues

    Select Convert(varchar(100),' + @vColumn + ') , ''' + @vColumn + ''' From ' + @vTable + ' Where ' + @iWhere

    --Select @SqlStatement

    EXEC sp_executesql @SqlStatement

    FETCH NEXT FROM @getValueID INTO @vColumn,@vTable

    END

    CLOSE @getValueID

    DEALLOCATE @getValueID

    -- Return columns that match criteria

    Select @SqlStatement = '

    Select *

    From ##ColumnValues

    Where cvalue like ' + @ivalue

    EXEC sp_executesql @SqlStatement

  • Hi thermanson

    So why don't you publish it here? 😉

    My script was only a simple helper I wrote sometime. Since now several people within my company asked for it, so I thought there might be somebody else who may need it.

    Greets

    Flo

  • Hi,

    I got some errors are prompted when I run this script. And I am using 2K8 SE

    Msg 2715, Level 16, State 3, Line 81

    Column, parameter, or variable #1: Cannot find data type SYSNAME.

    Parameter or variable '@column' has an invalid data type.

    Msg 2715, Level 16, State 3, Line 81

    Column, parameter, or variable #3: Cannot find data type SYSNAME.

    Parameter or variable '@schema' has an invalid data type.

    Msg 2715, Level 16, State 3, Line 81

    Column, parameter, or variable #4: Cannot find data type SYSNAME.

    Parameter or variable '@table' has an invalid data type.

    Please assist

  • Hi Madhu

    Try to replace all "SYSNAME" data types with "NVARCHAR(128)".

    Greets

    Flo

  • I use this one (cant remember where I found it though):

    CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int)

    GO

    SET NOCOUNT ON

    DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @COLUMN_NAME sysname, @Sargable varchar(80), @Count int

    SELECT @Sargable = 'enter word or words here'

    DECLARE insaneCursor CURSOR FOR

    SELECT c.TABLE_NAME, c.COLUMN_NAME

    FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t

    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME

    WHERE c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext')

    AND t.TABLE_TYPE = 'BASE TABLE'

    OPEN insaneCursor

    FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SELECT @SQL = 'INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT '

    + '''' + @TABLE_NAME + '''' + ','

    + '''' + @COLUMN_NAME + '''' + ','

    + 'COUNT(*) FROM [' + @TABLE_NAME

    + '] WHERE [' + @COLUMN_NAME + '] Like '

    + ''''+ '%' + @Sargable + '%' + ''''

    --SELECT @SQL

    EXEC(@SQL)

    IF @@ERROR <> 0

    BEGIN

    SELECT @SQL

    SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TABLE_NAME

    GOTO Error

    END

    FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME

    END

    SELECT * FROM myTable99 WHERE Occurs <> 0

    Error:

    CLOSE insaneCursor

    DEALLOCATE insaneCursor

    GO

    DROP TABLE myTable99

    GO

    SET NOCOUNT OFF

  • Thanks for this!

    I've been using a similar script, and I was wondering if it would be possible to include data in the same row as the data found? In other words, I have a 'date_created' column on most tables, and I would like to return that as well as the search result, in order to sort by date created.

    Is this possible?

    Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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