Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2011
    Location
    karachi , Pakistan
    Posts
    13

    Unanswered: How to find table name from column value

    Hi
    How could i find all tables name which have column value 'Ferrari'
    .
    .
    .
    how could we find table name from it's column value?????

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I'm not clear on your use of the phrase "column value." As far as I know columns don't have a value--they contain a collection of values.

    Are you saying that you want the names of any tables which have data in them containing the value "Ferrari" in any column?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    you can find this with

    select * from information_schema.columns
    where column_name like 'Ferr%'

    select * from information_schema.columns
    where column_name = 'Ferrari'


    Syed Jahanzaib Bin Hassan
    MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

    My Blog
    A|U|R|E|U|S – S|A|L|A|H

  4. #4
    Join Date
    Apr 2011
    Location
    karachi , Pakistan
    Posts
    13
    i want all table names in which specific value name 'ferrari' is present i don't want column name i want table name base on column value not on column name

  5. #5
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    Here we can use Cursor

    STORE PROCEDURE
    ==============

    CREATE procedure TABLE_COLUMN_DATA_SP
    @TABNAME varchar(300),
    @COLNAME VARCHAR(300)
    AS
    BEGIN
    DECLARE @COUNT INT
    DECLARE @QUERY NVARCHAR(500)
    SET @QUERY=N'SELECT @CNT=COUNT(*) FROM '+@TABNAME+' WHERE '+@COLNAME+' = ''FERRARI'''
    EXEC SP_EXECUTEsql @QUERY,N'@CNT INT OUTPUT',@CNT = @COUNT OUTPUT
    SELECT @COUNT

    END



    SCRIPT
    ======

    SET NOCOUNT ON

    DECLARE @TABNAME VARCHAR(300)
    DECLARE @COLNAME VARCHAR(300)
    DECLARE @QUERY NVARCHAR(500)
    DECLARE @COLCOUNT INT
    DECLARE @TABCOUNT INT
    SET @TABCOUNT =0

    DECLARE _CURSOR CURSOR FOR
    SELECT NAME FROM SYS.TABLES
    --WHERE NAME LIKE 'TAB%'


    OPEN _CURSOR

    FETCH NEXT FROM _CURSOR INTO @TABNAME
    SET @COLCOUNT = NULL
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE _COLCUR CURSOR FOR
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABNAME
    AND DATA_TYPE IN('VARCHAR','CHAR','NVARCHAR') AND CHARACTER_MAXIMUM_LENGTH > 4

    OPEN _COLCUR
    FETCH NEXT FROM _COLCUR INTO @COLNAME

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SET @QUERY=N'SELECT @CNT=COUNT(*) FROM '+@TABNAME+' WHERE '+@COLNAME+' = ''FERRARI'''
    EXEC SP_EXECUTEsql @QUERY,N'@CNT INT OUTPUT',@CNT = @COLCOUNT OUTPUT

    PRINT @COLCOUNT
    IF @COLCOUNT > 0
    BEGIN
    SELECT @TABNAME AS TABLENAME , @COLNAME AS COLUMNNAME
    PRINT 'TABLE NAME : '+@TABNAME +' AND COLUMN NAME : '+@COLNAME + ' FOUND'
    END
    FETCH NEXT FROM _COLCUR INTO @COLNAME
    END
    CLOSE _COLCUR
    DEALLOCATE _COLCUR

    FETCH NEXT FROM _CURSOR INTO @TABNAME
    SET @TABCOUNT = @TABCOUNT + 1

    END

    PRINT 'TOTAL TABLE : '+CAST(@TABCOUNT AS VARCHAR)

    CLOSE _CURSOR
    DEALLOCATE _CURSOR



    Syed Jahanzaib Bin Hassan
    MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

    My Blog
    A|U|R|E|U|S – S|A|L|A|H

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SJahanzaib. please have a look at the post immediately before yours

    he says " i don't want column name i want table name base on column value not on column name "
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    SJahanzaib, as r937 indicated, your solution is to a different problem than the user is asking to solve.

    But, for the problem you solved, perhaps there is a far simpler solution than you have provided. For instance, I believe the below code will yield the same result without the carpal tunnel syndrome.

    Code:
    select	sO.name
    from	sys.objects sO
    inner
    join	(
    	select	object_id
    	from	sys.columns
    	where	name='Ferrari'
    	) sC on
    		sC.object_id=sO.object_id
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  8. #8
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    He needs column value 'Ferrrai' not a column name 'Ferrari'

    I have suggested

    select * from information_schema.columns
    where column_name like 'Ferr%'

    select * from information_schema.columns
    where column_name = 'Ferrari'

    but that is not required

  9. #9
    Join Date
    Apr 2011
    Location
    karachi , Pakistan
    Posts
    13

    Thumbs up

    it's worked

    thank you....... it's really help me

  10. #10
    Join Date
    Nov 2011
    Posts
    1

    Hi

    How did you resolve your issue, please publish the resolution

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •