To apply a search on every table in a database
-
hello forum i want to give an option of search on frontend (asp.net) that will find the given keyword in selected columns of each table present in database how can i do that perfectly and without effecting the performance. How to create a stored procedure to apply a search on every table in a database can anybody help me. regards rahul
-
hello forum i want to give an option of search on frontend (asp.net) that will find the given keyword in selected columns of each table present in database how can i do that perfectly and without effecting the performance. How to create a stored procedure to apply a search on every table in a database can anybody help me. regards rahul
Assuming SQL Server 2005/2008 there is a built in command called sp_MSForEachTable
exec sp_MSForEachTable @command1 = "select col1 from ? where x = y"
Note the use of double quotes around the command. When the query runs it replaces the question mark with the table name. I have a feeling you made need single quotes around the question mark, but I don't have sql server on this pc to check. Hopefully this will get you in the right direction
Bob Ashfield Consultants Ltd
-
Assuming SQL Server 2005/2008 there is a built in command called sp_MSForEachTable
exec sp_MSForEachTable @command1 = "select col1 from ? where x = y"
Note the use of double quotes around the command. When the query runs it replaces the question mark with the table name. I have a feeling you made need single quotes around the question mark, but I don't have sql server on this pc to check. Hopefully this will get you in the right direction
Bob Ashfield Consultants Ltd
-
Assuming SQL Server 2005/2008 there is a built in command called sp_MSForEachTable
exec sp_MSForEachTable @command1 = "select col1 from ? where x = y"
Note the use of double quotes around the command. When the query runs it replaces the question mark with the table name. I have a feeling you made need single quotes around the question mark, but I don't have sql server on this pc to check. Hopefully this will get you in the right direction
Bob Ashfield Consultants Ltd
hello Bob I did some job on that but the output is not according to my desire. Let me explain the Demo tables are txnNews NewsId Title News txnWallpapers WallpaperID Name Path txnHoroscope SunSignId Horoscope Now i want to do search on the News and title columns of txnNews on Name column of txnWallpapers and Horoscope column of txnHoroscope the code that i used is
declare @search varchar(128)
declare @mycommand varchar(128)
declare @mywhereand varchar(128)
declare @returnvalue varchar(156)
declare @colname varchar (50)
set @search = '%India%'set @mycommand = 'select * from ? where news like '''+ @search+''''
exec @returnvalue= sp_MSforeachtable
@command1 = @mycommandprint @returnvalue
that gives an error for the other two tables of invalid column what should i do regards rahul
-
hello Bob I did some job on that but the output is not according to my desire. Let me explain the Demo tables are txnNews NewsId Title News txnWallpapers WallpaperID Name Path txnHoroscope SunSignId Horoscope Now i want to do search on the News and title columns of txnNews on Name column of txnWallpapers and Horoscope column of txnHoroscope the code that i used is
declare @search varchar(128)
declare @mycommand varchar(128)
declare @mywhereand varchar(128)
declare @returnvalue varchar(156)
declare @colname varchar (50)
set @search = '%India%'set @mycommand = 'select * from ? where news like '''+ @search+''''
exec @returnvalue= sp_MSforeachtable
@command1 = @mycommandprint @returnvalue
that gives an error for the other two tables of invalid column what should i do regards rahul
Think, if that is the error you are getting what do you THINK may be causing the problem. The columns are different in each table, you expect SQL to be telepathic. Also you don't need to search EVERY table in the database which this will do. looking for the same column in EVERY table. Your requirement is to search different columns in 3 tables, write 3 select statements and deal with the result sets.
Never underestimate the power of human stupidity RAH