SQL server query to search for table and column data in database

10 pts.
Tags:
SQL Database
SQL Developer
SQL development
SQL Server Query
I want to find the data in a table,but i dont know the table name and column name.All I know is the data and the database name .Is there a query to find the table and column

Software/Hardware used:
sql developer

Answer Wiki

Thanks. We'll let you know when a new response is added.

depending on the version of sql server there are a number of ways of finding this.

Try looking at the systables view in your database to track down the table. You can then look at the columns in the table

Alternatively use the sql server management tools to view the database structure

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    I assume you mean that you have some data value, but you don't know in which table or column that data value occurs. There are two ways to do this. The ugly but simple way is to use a simple query to generate a list of queries against all columns in the database to see if the column holds that value, and then run the set of queries and see which one(s) return a positive result. Example:
    select 
    	'union all select ''' + TABLE_NAME + ''' thetable, ''' + 
    	COLUMN_NAME + ''' thecolumn where exists (select 1 from [' + 
    	TABLE_NAME + '] where [' + COLUMN_NAME + '] = ''mysearchvalue'')'
    from INFORMATION_SCHEMA.COLUMNS
    where
    	DATA_TYPE in ('varchar','nvarchar');
    If you take the above, put in your search value in the search string, run it and then paste the result in a new query window - AND remove the first "union all" - you will have a query that will find all the columns that contain the string value. Obviously, change the data type and search type if you are looking for a numeric value. The result of the query will be a list of table/column pairs where the search value exists at least once in that column in that table. The more elegant, but more effort, solution is to write a stored function that uses dynamic SQL to essentially generate and run the same queries, and then return only the matching table/column pairs. In the function, use a similar query to get the list of table/column candidates, then create and execute the dynamic SQL. Example parts:
    select TABLE_NAME, COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS
    where DATA_TYPE in ('varchar','nvarchar');
    I would probably generate a SQL statement such as:
    select count(*) where exists (select 1 from TABLE_NAME where COLUMN_NAME = 'searchvalue')
    as it can be simpler to handle a returned result of a single numeric value (0 or 1) than to handle a return of multiple text strings. The use of "where exists" in both these examples allows the inner query to end as soon as any record matching the criteria is encountered.
    3,830 pointsBadges:
    report
  • Kccrosser
    It looks like my last example got lost in the html... The missing query example is:
    select COUNT(*) 
    where exists 
       (select 1 from TABLE_NAME 
       where COLUMN_NAME = 'mysearchstring');
    This should be more readable.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following