January 8, 2009 at 8:08 pm
I'm trying to write a procedure that will cycle through each field in a table, checking for a given value. Specifically, I'm trying to determine if an ASCII Null value (char(0)) exists anywhere in the table; because the presence of this value anywhere in the table will cause some processes to fail.
I wrote a nice neat little procedure to do this; but it doesn't work, because I'm trying to populate a variable (@col_name) with the column name, then run:
--
declare @col_name varchar(60)
set @col_name = 'address2'
select * from test1
where @col_name = char(0)
--
(the above is just a snippet of the whole procedure, and in this example i have hard-coded the table name for simplicity)
the result of this query is an empty rowset, though when i name the column directly in the SELECT statement, i get the desired record returned.
this procedure needs to run this on every column in the table, and needs to be able to be run on any table in the database, so hard-coding column names isn't gonna work.
any pointers?
January 8, 2009 at 9:01 pm
well first you need a proof of concept: can you find a char(0) in a field...actually, you don't need to search every column,
but rather every char, varchar, nchar and nvarchar column (and maybe text and ntext)
[font="Courier New"]
CREATE TABLE #tmp(tmpvar VARCHAR(50) )
INSERT INTO #tmp
SELECT'apples' UNION
SELECT 'bana' + CHAR(0) + 'nanas'
SELECT * FROM #tmp WHERE tmpvar LIKE '%' + CHAR(0) + '%'[/font]
that returns this for me:
tmpvar
------
bana
note how the second have of the word got chopped....wierd.
to search every column, you'll have to use a cursor and dynamic SQL; I've done something very similar before, but this is important:
this kind of query is very server intensive. it can bring your production environment to it's knees. if your production environment has any tables with millions of rows, you will regret it;
it scans the million row tables once for each varchar column....40 such columns...40 searches...
here's my proc, which i call "UGLYSEACH" exactly because it's not a good thing to do unless you have to.
note how it is only getting the column types we needed.
you could modify it to pass a table name, and just search one table at a time, but something like this should be a one-of-a-kind occurrance.
in the end, it produces a list of all the tables that have the search term in it, so you can then run a update field=replace(field) kind of fix, since it returns the table and offending column in the set.
[font="Courier New"]CREATE PROCEDURE UGLYSEARCH
-- EXEC UGLYSEARCH 'TEST'
@SEARCHSTRING VARCHAR(50)
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(500),
@TABLENAME VARCHAR(60),
@COLUMNNAME VARCHAR(60)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @SQL = 'IF EXISTS(SELECT * FROM [' + @TABLENAME
+ '] WHERE [' + @COLUMNNAME + '] LIKE ''%' + @SEARCHSTRING
+ '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES('''
+ @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM ['
+ @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''''%'
+ @SEARCHSTRING + '%'''''') ;'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
[/font]
Lowell
January 8, 2009 at 9:22 pm
i do know for a fact that that field "address2" in table "test1" indeed contains the value in question; and the table only has 4 records, being a test table.
i'll give your code a try and see what i get.
thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply