January 9, 2003 at 12:48 pm
Hi All,
I was wondering if anyone has ever done the following:
Search for a value within a table. But the catch is search all database tables - minus the system tables for that specific value. Say I want to look for 'toy' in all my tables how can I accomplish that.
Thanks
JMC
JMC
January 9, 2003 at 1:45 pm
Wow, weird, I have been writing a script this morning to do that very thing. I
am searching for ASCII zeroes in all of my string columns. This is probably not
what you want to do, but you could modify it to do as you wish. Oh - btw, the
cast to text makes it run REALLY slow, but we have unicode columns, and as I was
searching for char 0 the cast was necessary, and you can remove it and switch
to PATINDEX for the search. Here ya go:
DECLARE @tableName sysname,
@columnName nvarchar(128),
@dateType nvarchar(128),
@row smallint,
@rowCount int,
@sql nvarchar(1000)
DECLARE ascii_zero_cursor CURSOR FAST_FORWARD FOR
SELECT [name]
FROM SYSOBJECTS
WHERE OBJECTPROPERTY(id, N'IsMsShipped') = 0
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
ORDER BY [name]
OPEN ascii_zero_cursor
FETCH NEXT FROM ascii_zero_cursor
INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @row = 1
SELECT @rowCount = MAX([ORDINAL_POSITION])
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = @tableName
GROUP BY [ORDINAL_POSITION]
WHILE @row <= @rowCount
BEGIN
SELECT @columnName = [COLUMN_NAME],
@dateType = [DATA_TYPE]
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = @tableName
AND [ORDINAL_POSITION] = @row
ORDER BY [ORDINAL_POSITION]
SET @row = @row + 1
IF @dateType IN ( N'char', N'nchar', N'varchar', N'nvarchar', N'text', N'ntext')
BEGIN
SET @sql = 'SELECT * FROM ' + @tableName + ' WHERE CHARINDEX(CHAR(0), CAST(' + @columnName + ' As TEXT)) > 0'
EXEC ('IF EXISTS(' + @sql + ') SELECT ''' + @tableName + '.' + @columnName + ' As TableColumn, ' + @sql + ' As FindSql''')
END
END
FETCH NEXT FROM ascii_zero_cursor
INTO @tableName
END
CLOSE ascii_zero_cursor
DEALLOCATE ascii_zero_cursor
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 9, 2003 at 1:54 pm
Wow is right, you're fast and Thanks. I'll test out the code and provide feedback. Do you mind if I need to modify the sp.
Thanks
JMC
JMC
January 9, 2003 at 2:18 pm
No I don't mind, but here it is modified ro do exactly what you wished to do. And I greatly improved the speed, by only doing the casts for unicode fields...
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFindTextInColumns]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFindTextInColumns]
GO
CREATE PROCEDURE spFindTextInColumns (@TableName sysname = NULL,
@StringToLookFor varchar(500))
AS
-- Example Calls
-- EXECUTE spFindTextInColumns MyTable, 'tcart' --search a specific table
--
-- EXECUTE spFindTextInColumns default, 'tcart' --search all tables
-- GO
DECLARE @columnName nvarchar(128),
@dateType nvarchar(128),
@row smallint,
@rowCount int,
@sql nvarchar(1000)
DECLARE ascii_zero_cursor CURSOR FAST_FORWARD FOR
SELECT [name]
FROM SYSOBJECTS
WHERE (
OBJECTPROPERTY(id, N'IsMsShipped') = 0
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
) AND (@TableName IS NULL OR [name] = @TableName)
ORDER BY [name]
OPEN ascii_zero_cursor
FETCH NEXT FROM ascii_zero_cursor
INTO @tableName
SET @StringToLookFor = '%' + @StringToLookFor + '%'
WHILE @@FETCH_STATUS = 0
BEGIN
SET @row = 1
SELECT @rowCount = MAX([ORDINAL_POSITION])
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = @tableName
GROUP BY [ORDINAL_POSITION]
WHILE @row <= @rowCount
BEGIN
SELECT @columnName = [COLUMN_NAME],
@dateType = [DATA_TYPE]
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = @tableName
AND [ORDINAL_POSITION] = @row
ORDER BY [ORDINAL_POSITION]
SET @row = @row + 1
SET @sql = NULL
IF @dateType IN ( N'char', N'varchar', N'text')
SET @sql = 'SELECT * FROM ' + @tableName + ' WHERE PATINDEX(''' + @StringToLookFor + ''', ' + @columnName + ') > 0'
ELSE IF @dateType IN (N'nchar', N'nvarchar', N'ntext')
SET @sql = 'SELECT * FROM ' + @tableName + ' WHERE PATINDEX(''' + @StringToLookFor + ''', CAST(' + @columnName + ' As TEXT)) > 0'
IF @sql IS NOT NULL
BEGIN
SET @sql = 'IF EXISTS(' + @sql + ') PRINT ''' + @tableName + '.' + @columnName + ''''
--PRINT (@sql)
EXEC (@sql)
END
END
FETCH NEXT FROM ascii_zero_cursor
INTO @tableName
END
CLOSE ascii_zero_cursor
DEALLOCATE ascii_zero_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 9, 2003 at 2:46 pm
I posted this proc in my contributions section(not approved yet) and it will be available from there if I make any changes / updates.
http://www.sqlservercentral.com/scripts/listscriptsbyauthor.asp?author=59500
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 10, 2003 at 6:18 am
No Way! This is unbelievable. It's kinda of like getting a belated Christmas gift. Never having to do this type of functionality before, it became a quest of mine to show my boss that yes you can do table lookups.
Thanks again.
JMC
JMC
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply