Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Search String in DB
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chuchi
Starting Member

2 Posts

Posted - 04/25/2013 :  11:35:22  Show Profile  Reply with Quote
Hi there

I hope you can help me
I'm looking for a query that lets me find a specific text in a lets say "unknown" Database.
By this a mean: I wanna search every column in every table if a "text" exists.

I couldnt find anything (maybe i searched for the wrong turns), but i created a query on my own:

CREATE PROCEDURE [dbo].[x_FeldinhaltSuchen]
@i_SucheNach varchar(255)
AS
DECLARE @ObjID int
DECLARE @Table varchar(50)
DECLARE @Column varchar(50)
DECLARE @PrimaryIDColumn varchar(50)
DECLARE @Search varchar(255)
DECLARE @cmd varchar(1024)

-- Temporäre Tabelle für die Rückgabe
CREATE Table #temp
(
[Table] varchar(50),
[Column] varchar(50)
)
-- Suchwert definieren (mit % für Wildcard-Suche)
SELECT @Search = '%' + @i_SucheNach + '%'

DECLARE FieldCursor CURSOR FOR (SELECT sys.tables.object_id, sys.tables.name, sys.columns.name FROM sys.columns
join sys.tables ON sys.tables.object_id = sys.columns.object_id
WHERE system_type_id IN (SELECT system_type_id FROM sys.types WHERE name in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar')))
OPEN FieldCursor
FETCH NEXT FROM FieldCursor
INTO @ObjID, @Table, @Column
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @PrimaryIDColumn = ''
SELECT @PrimaryIDColumn = isnull(name, '') FROM sys.columns WHERE object_id = @ObjID AND is_identity = 1
-- Überprüfung des Inhalts, sofern Inhalt existiert, wird er in die temporäre Tabelle geschrieben
-- Sofern PrimaryID existiert, mit PrimaryID auf Existenz prüfen -> Abfragen sind schneller
IF @PrimaryIDColumn <> ''
SELECT @cmd = 'IF EXISTS(SELECT [' + @PrimaryIDColumn
ELSE
SELECT @cmd = 'IF EXISTS(SELECT [' + @Column
SELECT @cmd = @cmd + '] FROM [' + @Table + '] WHERE convert(varchar(50), [' + @Column + ']) LIKE ''' + @Search + ''') '
SELECT @cmd = @cmd + 'INSERT INTO #temp SELECT ''' + @Table + ''', ''' + @Column + ''''
-- Ausführen des Commands: () da der Command in einem String ist
print @cmd
EXEC (@cmd)
SELECT @ObjID = 0
SELECT @Table = '', @Column = ''
-- nächstes Objekt
FETCH NEXT FROM FieldCursor
INTO @ObjID, @Table, @Column
END
CLOSE FieldCursor
DEALLOCATE FieldCursor
-- Ausgabe der Tabelle
SELECT * FROM #temp
DELETE #temp
DROP TABLE #temp
Finish:
RETURN 0
GO


So what it does is pretty obvious
For every column it creates an "if exist/insert"-String which is then executed
The problem is: its very slow

Is there a proper and faster way to search?
Thank you

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/25/2013 :  11:46:15  Show Profile  Reply with Quote
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

chuchi
Starting Member

2 Posts

Posted - 05/06/2013 :  10:05:06  Show Profile  Reply with Quote
Thank you

that did the job!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 05/07/2013 :  00:22:26  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000