Need to fix the error in the SQL Script--SSMS Version 19. can some one please resolve this issue.

Question

Trying to search a value in the entire DB and list the tables and column names with the searched value

I am not sure what the error is in the below SQL script. it always throws the below error.

Msg 103010, Level 16, State 1, Line 1

Parse error at line: 1, column: 689520: Incorrect syntax near ']'.

Completion time: 2023-03-21T17:48:15.1221203-07:00

DECLARE @SearchValue NVARCHAR(100) = 'EI-ALD-VXT-CARB-DK';
DECLARE @SQL NVARCHAR(MAX);

WITH CTE_Table_Columns AS (
    SELECT 
        t.name AS TableName,
        c.name AS ColumnName,
        'SELECT ''' + t.name + ''' AS TableName, ''' + c.name + ''' AS ColumnName FROM ' + QUOTENAME(s.name) + '.' 
		+ QUOTENAME(t.name) + ' WHERE ' + QUOTENAME(c.name) + ' LIKE N''%' + REPLACE(@SearchValue, '''', '''''') + '%'';' 
		AS Query
    FROM
        sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
    WHERE
        tp.name IN ('varchar', 'nvarchar', 'char', 'nchar', 'text', 'ntext')
)

SELECT
    @SQL = STRING_AGG(cast(Query AS NVARCHAR(MAX)), ' UNION ALL ')
FROM
    CTE_Table_Columns;

-- Adding a check to make sure we have any tables and columns before executing the above dynamic SQL
IF @SQL IS NOT NULL
BEGIN
    SET @SQL = N'SELECT TableName, ColumnName FROM (' + @SQL + N') AS Results WHERE 
				TableName IS NOT NULL AND ColumnName IS NOT NULL;';
    EXEC sp_executesql @SQL;
END
ELSE
BEGIN
    PRINT 'No tables or columns found with the specified data.';
END;

Answer 1

Hi Vijay

Welcome to Q&A Forum; this is a great place to get support, answers, and tips.

Thank you for posting your query; I'll be more than glad to help you out.

I worked on your query and found a solution...

Change this line (remove the ';'):

		+ QUOTENAME(t.name) + ' WHERE ' + QUOTENAME(c.name) + ' LIKE N''%' + REPLACE(@SearchValue, '''', '''''') + '%'';' 

to

		+ QUOTENAME(t.name) + ' WHERE ' + QUOTENAME(c.name) + ' LIKE N''%' + REPLACE(@SearchValue, '''', '''''') + '%'''

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 2

Hi @Vijay

To debug dynamic query, it is always recommended to use Print @SQL. And as answered above, you need to remove the ';'

Besides, the logic of PRINT 'No tables or columns found with the specified data.' is not right. Because @SQL is always NOT NULL, so you will never get this sentence even there is no data founded.

Try this code:

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'EI-ALD-VXT-CARB-DK'
 
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
 
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
     SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
       FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_TYPE = 'BASE TABLE'
        AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
        AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
    )
  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
    SET @ColumnName =
        (SELECT MIN(QUOTENAME(COLUMN_NAME))
           FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
            AND TABLE_NAME    = PARSENAME(@TableName, 1)
            AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
            AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )
    IF @ColumnName IS NOT NULL
      BEGIN 
	  INSERT INTO #Results
      EXEC('SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
           ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2)
      END
    END   
END
IF EXISTS (SELECT * FROM #Results)
BEGIN 
  SELECT ColumnName, ColumnValue FROM #Results
END
ELSE PRINT 'No tables or columns found with the specified data.';
DROP TABLE #Results

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.