Search for table that have fields with spesific values

Posted on 2011-10-14
Last Modified: 2012-05-12
I'm working (end user) with an application that have a big database. I want to find out which table some of the fields I have in the application is saved. Excample: I want to find the name of every table where the value "1234.50" is stored in one of the fields.  I know how to run sql from Management Studio. Is it possible to write a sql that search every table in the database for a spesific value?
Question by:hallpett
    LVL 10

    Accepted Solution


    There is an article that shows how this can be done:

    See paragraph 2 for details.

    Hope it helps
    LVL 9

    Expert Comment

    Try this store procedure

    Execute store procedure as

    exec GetAllTables '1234.50'
    -- GetAllTables '1234.50'
    create proc GetAllTables 
    @inputValue nvarchar(1000)
    declare @tabeCollection table(Name nvarchar(1000))
    declare @TableName nvarchar(100), @Column_name nvarchar(100)
    declare @count int, @strSql nvarchar(max)
    DECLARE @retCount int
    declare cur cursor for 
    select Table_Name,column_name from INFORMATION_SCHEMA.COLUMNS 
    open cur
    fetch next from cur into @TableName, @Column_name
    while @@FETCH_STATUS =0
    	print 'select count(*) from ' + @TableName + ' where ' + @Column_name + ' like ' + char(39) + convert(nvarchar,@inputValue) + CHAR(39)
    	set @strSql = 'select @retCount = count(*) from ' + @TableName + ' where ' + @Column_name + ' like ' + char(39) + convert(nvarchar,@inputValue) + CHAR(39)
    	exec sp_executesql @strSql,N'@retCount int output',@retCount=@retCount output 
    	if @retCount > 0
    		insert into @tabeCollection
    		select @TableName 
    fetch next from cur into @TableName, @Column_name
    select * from @tabeCollection 
    close cur
    deallocate cur

    Open in new window


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how the fundamental information of how to create a table.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now