Post reply

Search anything anywhere

  • February 25, 2009 at 7:27 am

    #206331

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

  • April 1, 2009 at 7:49 am

    #969833

    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]

  • April 1, 2009 at 8:05 am

    #969853

    Hi JacekO

    What is the problem?

    Greets

    Flo

  • April 1, 2009 at 8:09 am

    #969860

    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]

  • April 1, 2009 at 8:12 am

    #969864

    Dang! You got me! 😀

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

    Have a nice day 😉

    Flo

  • April 1, 2009 at 9:53 am

    #970019

    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!

  • April 1, 2009 at 10:49 am

    #970069

    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

  • April 1, 2009 at 2:54 pm

    #970247

    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

  • March 24, 2010 at 12:39 am

    #1138712

    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

  • March 24, 2010 at 2:17 am

    #1138730

    Hi Madhu

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

    Greets

    Flo

  • June 5, 2013 at 7:54 am

    #1621353

    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

  • June 5, 2013 at 1:02 pm

    #1621476

    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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy