May 10, 2005 at 9:03 am
Hi experts,
I want to find a word "AMKDWAPP" from any tables, any columns in a database, is it possible to have a query to do it? I need to find which columns contain this word as well.
Please help. Thank you.
May 10, 2005 at 3:20 pm
Is the word in the column/table names or in the data?
One way or the other you'll be joining SYSOBJECTS (tables) and SYSCOLUMNS (columns) and either doing the query directly against this or building dynamic SQL to execute.
Unless once again MS has hidden another secret bell or whistle and failed to notify me by registered letter of the new feature... (Bill's always doing that.)
May 10, 2005 at 3:26 pm
Someone else may have a better solution but this works:
this cursor based solution finds columns that are of type VARCHAR, and then returns EVERYTHING that matches your input string
this would not be good for production code, that's for sure; in a big database i had it took over 4 minutes to loop thru all the tables, and find all instances of the word 'TEST'
CREATE PROCEDURE UGLYSEARCH
-- EXEC UGLYSEARCH 'TEST'
@SEARCHSTRING VARCHAR(50)
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(200),
@TABLENAME VARCHAR(30),
@COLUMNNAME VARCHAR(30)
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)='VARCHAR'
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 = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply