Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

MS Sql find field value

Hi,
I am using MS sql 2000. I need a sql query to  return all table name that contain a field with value "81786000-LBL"

thank you very much
0
rickchan108
Asked:
rickchan108
  • 5
  • 4
  • 4
2 Solutions
 
imran_fastCommented:
select table_name from information_schema.columns where column_name= '81786000-LBL'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
rickchan108Author Commented:
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'.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
imran_fastCommented:
did you check the query i posted.
0
 
imran_fastCommented:
so you need all the field having value 81786000-LBL in table sorry i mis understood
0
 
rickchan108Author Commented:
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
0
 
imran_fastCommented:
create table ##tab  (tablename varchar(128), columname varchar(128))
declare @tab varchar(128), @col varchar(128)
declare c cursor for select table_name,column_name  from information_schema.columns  where data_type in ('nvarchar','varchar','char','nchar')
open c

fetch next from c into @tab, @col
while @@fetch_status = 0
begin

exec ('if exists (select 1 from '+ @tab + ' where ' + @col + '=  ''81786000-LBL'')
      insert into ##tab values('''+@tab+''','''+@col+''');')
fetch next from c into @tab, @col
end
close c
deallocate c
go

select * from ##tab
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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'
0
 
rickchan108Author Commented:
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
0
 
rickchan108Author Commented:
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
0
 
imran_fastCommented:
@rickchan108

May be these are views.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>May be these are views.
 
TABLE_TYPE = 'BASE TABLE'  will select only tables
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE  PROC SearchAllTables
      @SearchStr nvarchar(100)
AS
BEGIN


      CREATE TABLE #Results (i int identity, TableName varchar(450), ColumnName nvarchar(370))

      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
                        IF NOT EXISTS(SELECT 1 FROM #Results WHERE TableName = @TableName and ColumnName = @ColumnName )
                        INSERT INTO #Results SELECT @TableName, @ColumnName
                  END
            END      
      END

      SELECT DISTINCT TableName, ColumnName FROM #Results
END


Go
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now