July 22, 2008 at 7:24 am
This script is meant to search all columns in a table for a search string, but for some reasons its not compatible with sql 2000.
DECLARE @searchSQL AS VARCHAR(4000)
DECLARE @SearchItem AS VARCHAR(4000) ; SET @SearchItem = '%YOURSTRING%'
SELECT @searchSQL = COALESCE(@searchSQL + '+ CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' +
CAST(Ordinal_position AS VARCHAR(4000)) +
' THEN '' OR '' + IC.Column_Name + '' LIKE ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(4000))' +
CHAR(13), 'CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' +
CAST(Ordinal_position AS VARCHAR(4000)) +
' THEN IC.Column_Name + '' LIKE ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(4000))' + CHAR(13))
FROM INFORMATION_SCHEMA.Columns
GROUP BY Ordinal_Position
ORDER BY Ordinal_Position
EXEC('SELECT REPLACE(Query, ''WHERE OR'', ''WHERE'') FROM
(SELECT ''SELECT '''''' + CAST(TABLE_NAME AS VARCHAR(4000)) + '''''' as tbl, * FROM ['' +
CAST(TABLE_SCHEMA AS VARCHAR(4000)) + ''].'' + CAST(TABLE_NAME AS VARCHAR(4000)) + '' WHERE '' + ' + @searchSQL + ' AS query
FROM INFORMATION_SCHEMA.Columns IC
WHERE DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')
GROUP BY TABLE_NAME, TABLE_SCHEMA) S')
July 22, 2008 at 9:26 am
What does "but for some reasons its not compatible with sql 2000" mean? Are you getting an error message? If so, then what is the message?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 22, 2008 at 9:56 am
OK, it appears that the SQL string that you are building is greater than 4000 characters, which is too big for your varchar(4000) strings.
The problem is that you add in a string like this:
+ CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = 2 THEN ' OR ' + IC.Column_Name + ' LIKE ''%YOURSTRING%''' END), '') AS NVARCHAR(4000))
for each column. Since that line is 141 characters long (varies with the size of "YOURSTRING"), once you get over about 27 columns, you are going to have this problem.
Since all this expression is supposed to do is produce a string, you can replace it with a scalar function like this:
Create function dbo.fnColTest(@pos as int, @col as int, @Name as SYSNAME, @Search as varchar(4000))
Returns NVarchar(4000) AS
BEGIN
Declare @ret as NVarchar(4000)
Select @ret = CAST(COALESCE(MIN(
CASE WHEN @pos = @col THEN ' OR ' + @Name + ' LIKE ''' + @Search + '''' END
), '') AS NVARCHAR(4000))
Return @ret
END
Then your lines would look like this:
+ dbo.fnColTest(IC.Ordinal_position, 2, IC.Column_Name, '%YOURSTRING%')
Which is almost 70 characters less. You could also (or additionally) use a derived table or view to rename "Ordinal_position" and "Column_Name" to shorter names.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 23, 2008 at 2:19 am
Thanks very much for the reply, can you please do me a favour by puting the script together as I keep geting errors whilst trying to do this.
thanks.
July 23, 2008 at 8:55 am
Dean Jones (7/23/2008)
Thanks very much for the reply, can you please do me a favour by puting the script together as I keep geting errors whilst trying to do this.thanks.
Show me what you have so far and I will try to fix it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 24, 2008 at 6:06 am
Hello
below is what i tried to run..
DECLARE @searchSQL AS VARCHAR(4000)
DECLARE @SearchItem AS VARCHAR(4000) ; SET @SearchItem = '%SEARCHSTRING%'
+ CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = 2 THEN ' OR ' + IC.Column_Name + ' LIKE ''%YOURSTRING%''' END), '') AS NVARCHAR(4000))
SELECT @searchSQL = COALESCE(@searchSQL + '
+ CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' +
CAST(Ordinal_position AS VARCHAR(4000))
+ ' THEN '' OR '' + quotename(IC.Column_Name) + '' LIKE ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(4000))'
+ dbo.fnColTest(IC.Ordinal_position, 2, IC.Column_Name, '%YOURSTRING%')
CHAR(13), 'CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' +
CAST(Ordinal_position AS VARCHAR(4000)) +
' THEN quotename(IC.Column_Name) + '' LIKE ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(4000))' + CHAR(13))
FROM INFORMATION_SCHEMA.Columns
GROUP BY Ordinal_Position
ORDER BY Ordinal_Position
exec('SELECT REPLACE(Query, ''WHERE OR'', ''WHERE'') FROM
(SELECT ''SELECT '''''' + quotename(CAST(TABLE_NAME AS VARCHAR(4000))) + '''''' as tbl, * FROM ['' +
CAST(TABLE_SCHEMA AS VARCHAR(4000)) + ''].'' + quotename(CAST(TABLE_NAME AS VARCHAR(4000))) + '' WHERE '' + ' + @searchSQL + ' AS query
FROM INFORMATION_SCHEMA.Columns IC
WHERE DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')
and table_name in (select name from sysobjects where type = ''u'' and name not like ''%CDR%'')
GROUP BY TABLE_NAME, TABLE_SCHEMA) S')
July 24, 2008 at 7:18 am
I just cut-n-pasted this into SSMS verses my SQL 2000 db. It appears you're only getting Syntax errors. Run a check against the code, double-click the error and it will take you almost to the exact spot your error is occuring.
The very first one tells me you forgot to put in a + sign.
I haven't looked at it beyond that. I think you need to learn what these various syntax errors are so you can improve your troubleshooting skills.
July 24, 2008 at 10:25 am
thanks for your reply, I have added the +, but i am still geting errors. can someone help please ?
July 24, 2008 at 10:32 am
Dean,
Unfortunately, a generic "I'm getting errors" doesn't give us anything to go on. We're not going to do all your work for you. If you want the correct answer, you need to be a little more forthcoming with information and a little more willing to work through your problems. Or at least list specifics on what you have and have not tried to resolve the error previous to this post.
If you want assistance, be more specific about the errors you are getting. As in, exact text of the error. Also, you could consider Googling the error message to see if someone has posted about it before and if there was a solution.
July 25, 2008 at 9:24 am
Hi Guys,
When i did try to run it now, here are the errors i am geting.
Msg 107, Level 16, State 2, Line 2
The column prefix 'IC' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 2
The column prefix 'IC' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 2
The column prefix 'IC' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 2
The column prefix 'IC' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 7
The column prefix 'IC' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 7
The column prefix 'IC' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 7
The column prefix 'IC' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 7
The column prefix 'IC' does not match with a table name or alias name used in the query.
from researching, looks like i am missing out some group by columns, but as the code is wrapped in T-SQL its not telling me, the code was from SS central under the script section, it was originally for SQL 2005, but it says to make it work for SQL V8, just change the Varchar(max) to say Varchar(4000).
See http://qa.sqlservercentral.com/scripts/Miscellaneous/31859/
July 25, 2008 at 9:58 am
I had created a search proc I called "uglysearch", that found every varchar/char column, in every table, and searched for a specific string. similar to what you are doing, but without the dynamic SQL, and the errors you get for tables with a lot of varchar columns that make our SQL too large.
the compromise is it uses a cursor to loop thru the list.
Similar to yours, mine returned tablename/colname, along with a query to duplicate the results for further research.
I just modified it to take a table or view name.
Maybe this will help you.
typical results are like this:
TBLNAME COLNAME SQL
VW_POSTED_ACTIVITYSOURCENAME SELECT * FROM [VW_POSTED_ACTIVITY] WHERE [SOURCENAME] LIKE '%FUND%'
and here's the source:
ALTER PROCEDURE TABLEVIEWSEARCH @TABLENAME VARCHAR(60),@SEARCHSTRING VARCHAR(50)
-- EXEC TABLEVIEWSEARCH 'GMACT','SOURCE'
-- EXEC TABLEVIEWSEARCH 'TABLEORVIEW','TEST'
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(500),
@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 #TMPCOLLECTION
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.NAME = @TABLENAME
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT COLNAME FROM #TMPCOLLECTION ORDER BY COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
--SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
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 @COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
Lowell
July 28, 2008 at 7:18 am
Dean,
You are missing table aliases in your code. Look for every instance of the table name in the FROM statement or the JOIN clauses to make sure that you have the correct alias after it.
Also, check your function. It probably also doesn't have a table alias after the table name.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply