How to Search the entier DataBase

bex · January 13th, 2009, 09:11 AM

Hi I need to search the entier databse and look for Spesific column and bind it to a listbox.

thanks

alliancejhall · January 13th, 2009, 10:12 AM

I assume that when you want to find the column you want to see what database(s) it would be in...

look in the sysobjects table and the syscolumns table in the master db

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'THE_COLUMN_NAME' )

or if you don't know the full column name

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%PART_OF_NAME%' )

bex · January 13th, 2009, 10:37 AM

It does not mater in what database the column is in what it maters is that i need to finde that column get the value of the column to a listbox that will desplay in pageload.

alliancejhall · January 13th, 2009, 10:43 AM

Do you just want to do a simple query to find a record?

Select * From Table Where Column='Value'

something like that?

what exactly are you searching for and what table and dabase are you searching in.

or are their multiple databases that have the same column and you want to get the values from those columns in all those databases?

bex · January 13th, 2009, 11:13 AM

I am searching 1 database with multipet tables So i can not use SELECT * FROM Table.
The database is called Campaigns and the column name in the tables inside the database is CampaignName

dparsons · January 13th, 2009, 12:09 PM

If this is SQL Server, setup a full text index and index the columns from your table that you want to search. This will give you the ability to do something similar to SELECT * from <full text index> where somecolumn = somevalue.

http://www.google.com/search?source=...le+Search&aq=1

hth.
-Doug

Old Pedant · January 13th, 2009, 04:34 PM

But we have to wonder if perhaps your database design is wrong. If all these tables have *ALL* their columns (or even most of their columns) the same names and types, then likely you should *NOT* have many tables and have one, instead.

bex · January 14th, 2009, 04:20 AM

well the concept is wright the column name is the same in all tables but the records are diferent, so if i have 10 tables with the same column name (CampaignName) they will have: Summer Campaign, Valentine Campaign......, in the listbox i want to display what is inside CampaignName column not column name.

dparsons · January 14th, 2009, 10:27 AM

Again the Full Text Index is what you are probably looking for. I am siding with Pedant on this as, it seems, you have 10 different tables with a similar (if not the same) structure. Unless there is a real good reason as to why you would have these tables broken out, i would probably just merge them.

hth.
-Doug

bex · January 14th, 2009, 01:07 PM

I did sort it out with full text index as you told me on the firs post.