March 6, 2009 at 8:30 am
Hello, I am trying to search for a specific text string however I do not know what table it resides in. Is there a way to do a search across all tables for this string? Thank you.
Best Regards,
~David
March 6, 2009 at 8:40 am
Hi
This may solve your problem.
[font="Courier New"]
SET NOCOUNT ON
DECLARE @Search VARCHAR(128) SET @Search = 'ANSI%'
DECLARE @PrintOnly BIT SET @PrintOnly = 0
DECLARE @Tables TABLE (Name SYSNAME)
INSERT INTO @Tables
SELECT Name
FROM Sysobjects
WHERE TYPE = 'U'
WHILE EXISTS (SELECT TOP 1 * FROM @Tables)
BEGIN
DECLARE @Table SYSNAME
DECLARE @SQL NVARCHAR(4000)
SELECT TOP 1 @Table = Name FROM @Tables
DECLARE @Columns TABLE (Name SYSNAME)
INSERT INTO @Columns
SELECT SC.Name
FROM Syscolumns SC
JOIN Systypes ST ON SC.XType = ST.XType AND ST.CollationID IS NOT NULL
WHERE SC.ID = OBJECT_ID(@Table)
IF EXISTS (SELECT TOP 1 * FROM @Columns)
BEGIN
SET @SQL = 'SELECT ''' + @Table + ':'', *
FROM ' + @Table + '
WHERE '
WHILE EXISTS (SELECT TOP 1 * FROM @Columns)
BEGIN
DECLARE @Column SYSNAME
SELECT TOP 1 @Column = Name FROM @Columns
SELECT @SQL = @SQL + '
' + @Column + ' LIKE ''' + @Search + ''''
DELETE FROM @Columns WHERE Name = @Column
IF EXISTS (SELECT TOP 1 * FROM @Columns)
SET @SQL = @SQL + ' OR '
END
IF (@PrintOnly = 1)
PRINT (@SQL)
ELSE
EXECUTE (@SQL)
END
DELETE FROM @Tables WHERE Name = @Table
END
SET NOCOUNT OFF
[/font]
Greets
Flo
March 6, 2009 at 9:54 am
Flo, this will do the trick. Thank you for the input.
Best Regards,
~David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply