SQL Script To Search All Tables In A Database For A Particular String?
Apr 9, 2008
Is their a way to search every table in a database for a particular character string that might appear in any column in any of the tables?
For example suppose in a hypothetical situation that the character string "http://www.DrSeusOnTheLoose.com" appears randomly in a database, but we do not know which tables it occurs in and in what columns. How can we do a search to find such a character string?
As you know, this is easily accomplished in MS Windows XX or through a dos command line script. But when it comes to searching a whole database, I don't know how to do this. Can someone please help me?
Ralph
View 1 Replies
ADVERTISEMENT
Sep 14, 2005
I have a string which I need to know where it came from in a database.I don't want to spend time coding this so is there a ready made scriptwhich takes a string as a parameter and searches all the tables whichcontain varchar type columns and searches these columns and indicate whichtables contain that string?Full text search is not enabled.--Tonyhttp://dotNet-Hosting.com - Super low $4.75/month.Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,PHP 5 & webmail support.
View 1 Replies
View Related
Jul 29, 2015
Is it possible to search a string/value across 1000's of tables and just display the table name and column name which it is in. I don't need to know every instance of the string/value only that I can find it in a given table name.
View 9 Replies
View Related
Mar 8, 2006
Hello
I have a Database which contains like 1000 Tables.
I am not the designer of that DB.So I need to in which table and which column
that string exists. IS there a DBWIDE String search possible?
Thanks and Regards
View 8 Replies
View Related
May 16, 2007
Hi All :A couple of tables have been identified to be deleted. My job is tofind if it is at all used.On searching the web, i found a proc to search for a string within alldatabases in a server.using system sproc : sp_msforeachdbit searches for a string inviews, sprocs, functions, check constraints, defaults, foreign key,scalar function, inlined tablefunction, primary key, 'Replicationfilter stored procedure, System table, Table function, Trigger, 'Usertable, 'UNIQUE constraint''Extended stored procedure'So it is pretty extensive. But i dont think it is covering the codewithin execsqltasks in DTS, and tsql code within JOB STEPS. Those arethe two more places where code exists in my server.If any of you have done so in the past, do let me know if there is asystem stored proc or code that you have written, to do the samethanksRSLink to the above procedurehttp://www.sql-server-performance.c...ase_objects.asp
View 1 Replies
View Related
Jan 22, 2008
well i am using vb.net and would like to know how to search a specific table in the databasefor egif table1 exists in database then drop table1end if ...something like that
View 2 Replies
View Related
May 5, 2004
Hi there,
I am currently seaching a db for all tables that have the same column name, for example, 'qdate'. Can anyone let me know how I can write a script that will search a db for all tables with this column name 'qdate'?
Thanks for your help!!!!!!
From
NewtoSql
View 1 Replies
View Related
Nov 16, 2015
I need to look at all tables in a database that has a column name of GEO
Then look for all values in each table where the GEO value is NULL and delete each of the records found...
View 6 Replies
View Related
Mar 10, 2015
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
View 3 Replies
View Related
Sep 1, 2015
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
View 3 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
Mar 1, 2007
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
View 5 Replies
View Related
Nov 22, 2007
hi there,
i am doing a school project and i need to have this search engine that will search the data that i have stored inside the database and display the results out
can anyone help?
thanks
View 6 Replies
View Related
Dec 27, 2004
Hi,
I have an old db with streets names and another one with employees. I have to use both tables to check if an employee adress is ok, but I have the following problem:
Employee adress : 47th street nº12 2nd door b
street in db 47th street
The question is how to say to sql that searchs for the employee street in the streets db cause the employee addres is bigger than any streets in db and I have no results with street like '%47th street nº12 2nd door b%' or something like that.
What can I do?
Thanks so much
View 4 Replies
View Related
Apr 26, 2002
I have a parameter that is a varchar(50)
and I want to know if there are any percent(%) signs in the string,
what function can I use for this?
I am looking for the T-SQL equivalent of InStr()
Thanks,
View 1 Replies
View Related
May 15, 2008
Hi everybodyI have this query which need to extract names where I use the wildcard character % before and after the string to indicate that the string should not contain these characters but it is not working right .. Is there any way I could do that..SELECT name, famnameFROM inftable WHERE NAME NOT IN ('%socius%', '%rector%', '%superior%')In other words my result should not include those names that contains socius, rector, superior, this characters could begin with or in a middle of the field...thanks
View 2 Replies
View Related
Aug 10, 2007
Hi,
Was wondering how you do the equivalent of an Oracle "Instr" function in TSQL, which returns the position of a string pattern in a specified string? I've looked through all the functions and can't find the equivalent.
Thanks
Simon
View 1 Replies
View Related
Aug 10, 2007
Hi,
Is there a T-SQL equivalent of the Oracle "Instr" function, whereby I can retrieve the position of a one specified charactier string within another? I have looked through the list of string functions and can't find an equivalent, which suprises me!
Thanks
Simon
View 1 Replies
View Related
Apr 25, 2008
For example
I want to search all the people in a database that names mary
I now that the sintax is something that looks like this.
SELECT Id, name
FROM Socios
WHERE (name LIKE '%string%')
But how i input this in the vs2008 query builder for it creates an automatic searchbox toolstrip?
I tried this but it didn't work
FROM Socios
WHERE (name LIKE ?)
View 5 Replies
View Related
Sep 20, 2000
The procedure below is for a 3 field wildcard search window.
It works fine if you enter a value such as cheese in @product_name.
If you enter cheese in @product_name1 or @product_name2 it returns nothing.
The string builds OK with values inserted where they should be, obviously there is a problem in the looping back of variable.
I've been looking at this so long I'm sure that the obvious solution is staring me in the face, but I just can't see it.
Any help would be appreciated.
Alter PROCEDURE usp_RobSearchTest
(
@product_name nVarChar(100),
@product_name1 nVarChar(100),
@product_name2 nVarChar(100),
@country_id nchar (3),
@language_id nchar (2)
)
AS
DECLARE @Variable nVarChar (100)
DECLARE @SQLString NVARCHAR(1000)
DECLARE @ParmDefinition NVARCHAR(1000)
/* Build the SQL string once. */
SET @SQLString =
N'SELECT product_name,unit_price,item_id,refund_price
FROM PRODUCT_LISTING
WHERE product_name LIKE ''%' + @Product_name + '%''
ORDER BY product_name'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@product_name nVarChar (100)'
/* Execute the string with the first parameter value. */
SET @Variable = @product_name
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@product_name = @Variable
/* IT WORKS UP TO THIS POINT */
/* Execute the same string with the second parameter value. */
SET @Variable = @product_name1
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@product_name1 = @Variable
/* Execute the same string with the third parameter value. */
SET @Variable = @product_name2
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@product_name2 = @Variable
View 1 Replies
View Related
Jul 12, 2002
I need to search a string starting at the end to find the
last space in the string, so I can determine the position of the last word in
the string.
Any ideas on how to do this are greatly appreciated.
Kellie
View 3 Replies
View Related
Sep 18, 2007
I am not very familiar with MS SQL, and I need to know how to search a table for a string. I know the column that it is in, but I don't know which record. I am looking for a small piece of text in a much larger text field (we are talkinga maybe a 10k char field and I am only looking for about a 15 char string).
How do I do this with a MSSQL query?
Thanks a bunch
Nevermind, i found PATINDEX(). That worked great.
View 1 Replies
View Related
Oct 1, 2013
I have access to a database's web front end and a limited amount of access to the server.
I am trying to find out what column a certain string is in.
I can see some text on the front end via the web that a user enters and then submits. This information must go to the back end of the database but I dont know where it goes...
I am able to provide a unique Sequence number that it would be linked with and obviously I have the string that im looking for its column name but other then that I dont know what I can do.
I don't have write access to the database so I am unable to make procedures. I am also not really aware how the front end works or if I would be able to find the script that deals with the text after the user hits "save"...
View 14 Replies
View Related
Jun 2, 2008
The 'LIKE' function looks for words that start with whatever is in the like condition. Is there an sql function similar but will look and compare at any part of the search string.
For example I am using a webservice in dot net to populate a dropdown list using this sql
SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText
In this table there is a compound called SILCAP310 and I would like the search function to pick up 310 if I put this into the @prefix parameter. (but I would still like the search to perform like the 'LIKE' does also.
SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText or compound_name SearchPartString @prefixText
Thanks in advance
View 4 Replies
View Related
Jan 18, 2008
I have at table that I want to update using another table which contains other values than those in the text column.
table1
id: text:
1 1111, 2222, 3333
2 1234, 2222
3 0123
table2
id: str:
1111 aaaa
2222 bbbb
3333 cccc
1234 qqqq
0123 oooo
I want to replace the text in table1 with the new values so that it looks like below:
table1
id: text:
1 aaaa, bbbb, cccc
2 qqqq, bbbb
3 oooo
My first idea was to split the text column in table1 and insert every new text with its id in a temporary table and then concaternate a new string and update the column.
But how do I manage this?
View 4 Replies
View Related
Feb 25, 2008
Hi All,
Please help, it is urgent
I am having a colume as image datatype, in which candidates resume is stored in binary format.
I have to search for particular words for eg. "ASP.Net, 2 Years" into that column
I have converted that column as varchar and then searching above two words(after spliting them by comma) into that string using CHARINDEX
DECLARE @i int, @j int
SET @i = select MIN(ResumeId) from table_name
SET @j = select MAX(ResumeId) from table_name
WHILE (@i <= @j)
BEGIN
DECLARE @Resume varchar(max)
SET @Resume = CAST((select Resume from table_name where ResumeId=100) as varbinary(max))
--code to search the particular word from @Resume
END
It is working fine, but it is very slow, taking 2+ minutes to scan 15000 records
How can I make it faster or is there any other way to search?
Thanks in advance
View 7 Replies
View Related
Jan 11, 2006
Hi
Is there any function to seperate a sting (VARCHAR) into two based on a character in the string (e.g. '-').
In short is there "InStr" or "split" type function in T-SQL ? If not what is the alternative?
I have a column called "Range" (VarChar) in the table which has ranges like 1-14 or 15-49 etc.
I need to select records by compare a variable (numeric) against these ranges.
So for instace if the variable has 15 its in range 15-49.
I was thinking of spliting the range field into two so that comparison is possible.
Any suggestions?
thanks
View 5 Replies
View Related
Feb 28, 2007
Hi,
I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue
My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers.
Thanks
View 2 Replies
View Related
May 18, 2007
Dear all
I am pretty new to SQL server 2005 and have the following issue:
I get the date from the asp applciation in format dd/mm/yyyy and then try to do a search for all records that might match that date in sql server 2005. The date is storred in ther database in datetime format.
I am having trouble composing the stored procedure to do that.
Any help would be apreciated.
Sincerely
d
View 2 Replies
View Related
May 4, 1999
Hi,
Is it possible to search a field in a database, extract CERTAIN data, and insert THIS data into another field in another table?
Example:
Address
---------------------------------------------------
18 BerryWood Drive, Midland, Doncaster, Y09 2JF
I want to extract the postcode from this field into another field in another table....
Example 2:
Name
-----------
Ivor Smith
I wish to strip the last name from the field, and put this into a field in another table..
David
View 10 Replies
View Related
Aug 22, 2001
Ever needed to find a stored procedure with a specific string in it? You can pretty this up as a stored procedure and pass it a parm or cut and paste it into query analyzer.
select name from sysobjects
where id = (select id from syscomments
where text like '%like%')
Edit:
The above works only for a single hit. For multiple hits, this works
select name from sysobjects as A
join syscomments as B
on (text like '%cursor%')
where A.id = B.id
Live and learn,
Cat
Edited by - cat_jesus on 08/22/2001 10:09:49
Edited by - cat_jesus on 08/22/2001 10:10:29
View 15 Replies
View Related
Jan 8, 2008
I hava at large database and I need to search all tables for the string 'NaN' and replace it to null to enable conversion.
Is there any way to do this so that i do not need to write an update query for every column?
Would appretiate an example if anyone knows how to manage this.
Thanks!
View 2 Replies
View Related
Apr 29, 2015
Currently we have requirement like below.
Input @Filename ="233-sssee-FILENAMETYPE@ssss.xml"
In the DB i have column values like below.
FileType Val
FILENAMETYPE Direct
Now my requirement is to search for FIleTYPE in above table by passing @Filename as parameter and that should return Val as response. How to write a search query for this type.
View 10 Replies
View Related
