"Will" wrote:

> SP stands for stored procedure. I think Omnibuzz was asking whether or
> not you needed to just get an answer out of your database just for a
> particular case, or if you need to be able to use this several times
> for different search criteria (e.g. searching on "HEART" and "LUNGS"
> etc.).
>
> As for searching all columns, there are 2 stored procedures sp_tables
> and sp_columns. these will allow you to list all the tables in a
> database and all the columns in a table. you could use this along with
> the stored procedure sp_executesql to call some dynamic sql. However
> this is getting into some more advanced sql than you seem to be
> wanting.
>
> Cheers
> Will
>
> can u teach mi how to do it?

"Will" wrote:

> Here's some SQL that I'd use to dynamically search all text data in a
> database. Like I said, I think you'll find it too complicated, but
> perhaps it will give you a starting point:
>
> DECLARE @ValueToFind varchar(100)
> SET @ValueToFind = 'LIVER'
>
>
>
>
> CREATE TABLE #tables
> (
>     TABLE_QUALIFIER    sysname,
>     TABLE_OWNER    sysname,
>     TABLE_NAME    sysname,
>     TABLE_TYPE    varchar(32),
>     REMARKS    varchar(254)
> )
>
> CREATE TABLE #Columns
> (
>     TABLE_QUALIFIER    sysname,
>     TABLE_OWNER    sysname,
>     TABLE_NAME    sysname,
>     COLUMN_NAME    sysname,
>     [DATA_TYPE]    smallint,
>     TYPE_NAME    sysname,
>     [PRECISION]    int,
>     LENGTH    int,
>     SCALE    smallint,
>     RADIX    smallint,
>     NULLABLE    smallint,
>     REMARKS    varchar(254),
>     COLUMN_DEF    nvarchar(4000),
>     SQL_DATA_TYPE    smallint,
>     SQL_DATETIME_SUB    smallint,
>     CHAR_OCTET_LENGTH    int,
>     ORDINAL_POSITION    int,
>     IS_NULLABLE    varchar(254),
>     SS_DATA_TYPE    tinyint
>
> )
>
> INSERT INTO #tables
> exec sp_tables
>
>
> DECLARE tables cursor
> FOR
> SELECT Table_name FROM #tables
>
> OPEN tables
>
> DECLARE @Tablename sysname
>
> FETCH NEXT FROM tables INTO @tablename
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
>     INSERT INTO #Columns
>     exec sp_columns @TableName
>
>     DECLARE @SQL nvarchar(4000)
>     SET @SQL = 'SELECT * FROM ' + @Tablename + ' WHERE 1=2 '
>
>     DECLARE cols CURSOR
>     FOR SELECT Column_name FROM #Columns WHERE TYPE_NAME LIKE '%char' or
> type_name LIKE '%text'
>
>
>
>     DECLARE @ColName sysname
>     OPEN cols
>     FETCH NEXT FROM Cols INTO @ColName
>
>     WHILE @@FETCH_STATUS = 0
>     BEGIN
>         SET @SQL = @SQL + ' OR [' + @ColName + ']  LIKE ''%' + @ValueToFind +
> '%'' '
>         FETCH NEXT FROM Cols INTO @ColName
>     END
>
>
>     CLOSE Cols
>     DEALLOcate cols
>     TRUNCATE TABLE #Columns
>     FETCH NEXT FROM tables INTO @tablename
>
>     exec sp_executesql @SQL
> END
>
> CLOSE tables
> DEALLOCATE tables
> DROP TABLE #tables
> DROP TABLE #columns

>
>