August 24, 2004 at 5:42 am
Hi all.
I am using the Stored Procedure below, which I found on the SQLServerCentral website, and it does exactly what its supposed to - display a list of all tables and columns containing the search criteria.
The problem is instead of listing the table name and column name, I am trying to modify it to return the table name and the id of the record that the search criteria was found. The record id column of each of my tables is the table name preceeded by 'pu'.
Anyone know what I am getting at, and if so how to go about solving the problem?
Thanks greatly in advance.
Brendan
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),
@puTable char(50)
DECLARE string_find_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 string_find_cursor
FETCH NEXT FROM string_find_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
SET @puTable = 'pu' + @tableName
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 string_find_cursor
INTO @tableName
END
CLOSE string_find_cursor
DEALLOCATE string_find_cursor
GO
August 24, 2004 at 12:59 pm
Will this work? I added a #log table to the procedure and insert the values into the #log table instead of doing an "if exists". Then select from the #log table at the very end.
Brian
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),
@puTable char(50)
CREATE TABLE #Log (
TableValue INT,
TableName VARCHAR(200),
ColumnName VARCHAR(200)
)
DECLARE string_find_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 string_find_cursor
FETCH NEXT FROM string_find_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
SET @puTable = 'pu' + @tableName
IF @dateType IN ( N'char', N'varchar', N'text')
SET @sql = 'INSERT INTO #Log SELECT pu'+@TableName+','''+@TableName+''','''+@ColumnName+''' FROM ' + @tableName + ' WHERE PATINDEX(''' + @StringToLookFor + ''', ' + @columnName + ') > 0'
ELSE IF @dateType IN (N'nchar', N'nvarchar', N'ntext')
SET @sql = 'INSERT INTO #Log SELECT pu'+@TableName+','''+@TableName+''','''+@ColumnName+''' 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 string_find_cursor
INTO @tableName
END
CLOSE string_find_cursor
DEALLOCATE string_find_cursor
SELECT * FROM #Log
DROP TABLE #Log
GO
August 25, 2004 at 2:40 am
Hi bdohmen
Thanks a lot for your help. Your reply was exactly what I needed and it now works a treat.
Brendan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply