SQL Server Forums
Profile | Register | 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
 New Topic  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
52309 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
52309 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  
 New 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.03 seconds. Powered By: Snitz Forums 2000