MS Sql find field value

select table_name from information_schema.columns where column_name= '81786000-LBL'

Hello rickchan108,

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  PROC SearchAllTables
      @SearchStr nvarchar(100)
AS
BEGIN


      CREATE TABLE #Results (i int identity, TableName varchar(450), 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', 'uniqueidentifier')
                              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
                        )
                        IF @@ROWCOUNT = 0
                              DELETE FROM #results where i = SCOPE_IDENTITY()-1
                  END
            END      
      END

      SELECT DISTINCT TableName, ColumnName, ColumnValue FROM #Results
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



Aneesh R

Hi,Aneesh R

I can't understand long query like that. so I just copy and paste it to SQL Query Analyzer, and it return the following error.  Apart from that, how come I don't see "81786000-LBL" appear in the query?? thank you very much

Server: Msg 208, Level 16, State 6, Procedure SearchAllTables, Line 59
Invalid object name 'SearchAllTables'.

did you check the query i posted.

so you need all the field having value 81786000-LBL in table sorry i mis understood

hi imran_fast,
that query return an empty table.

may be I didn't make it clear enough. 81786000-LBL is not the name of the column, it is a value/data in the table. I am not sure what column would hold is value.
thank you very much

Chnage the 'ALTER' with a 'CREATE'



ALTER  PROC SearchAllTables
      @SearchStr nvarchar(100)
AS
BEGIN


      CREATE TABLE #Results (i int identity, TableName varchar(450), 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', 'uniqueidentifier')
                              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
                        )
                        IF @@ROWCOUNT = 0
                              DELETE FROM #results where i = SCOPE_IDENTITY()-1
                  END
            END      
      END

      SELECT DISTINCT TableName, ColumnName, ColumnValue FROM #Results
END


Go


and in order to search  run this

exec SearchAllTables '81786000-LBL'

hi imran_fast:,
the result look good, but the tablename column contain names that I can't find in the database. below are some data in tablename column, but these table names are not in the database. what does that mean? are they invisible or rename ? thank you

vp_Item2Hist_CrossTab
vr_SOPlanCheck
vp_83100_Inventory
vp_83100_UnitConv
vp_SOLinePO
vp_SOLinePO

hi aneeshattingal,
the query batch can't be completed. it said it is due to maximum number of row is excessed.
 
but it doesn't have too many tables in the database. why should that happen??

thank you

@rickchan108

May be these are views.

>May be these are views.
 
TABLE_TYPE = 'BASE TABLE'  will select only tables