loop throgh all the data to search particular string

Question

I want to loop throgh all the column of all the tables available in my database to check which column containts string "Kroki Homes".

I have approx. 56 tables in my database. So it is hard to check in all the columns of these 56 tables. is there any easy way to achieve this in sql server?

score 1 · Accepted Answer · answered Dec 04 '17 at 17:13

You can create one procedure and can pass the string for search. I found this some where this might help you.

CREATE PROC [dbo].[SearchDataFromAllTables] (@SearchStr NVARCHAR(100))
AS
BEGIN

    SET NOCOUNT ON;

    CREATE TABLE #Results 
    (
        ColumnName NVARCHAR(370),
        ColumnValue NVARCHAR(3630)
    )

    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'
                            )
                        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

    SELECT ColumnName, ColumnValue FROM #Results

END

Thanks. Will it search in all columns of 56 tables? – Sql Learner Dec 04 '17 at 17:15 — Sql Learner, Dec 04 '17 at 17:15
Yes. It will :) You can try it. ;) – Mova Dec 04 '17 at 17:16 — Mova, Dec 04 '17 at 17:16

score 0 · Answer 2 · answered Dec 04 '17 at 17:16

0

You need to write a stored procedure to scan through all the tables.

The script below gives to the tables and columns. From this you should create a SQL in-flight and execute dynamically to SELECT from tables. Let us know if you need more details.

SELECT tb.name AS table_name,
       c.name AS column_name, 
       c.column_id, 
       tp.name AS column_data_type, 
       c.max_length,
       c.precision,
       c.scale,
       CASE c.is_nullable WHEN 1 THEN 'YES' ELSE 'NO' END AS is_nullable
  FROM sys.tables tb,
       sys.columns c, 
       sys.types   tp
  WHERE tb.object_id = c.object_id
    AND SCHEMA_NAME(tb.schema_id) = 'dbo'
    AND c.user_type_id = tp.user_type_id
  ORDER BY table_name, column_id;

answered Dec 04 '17 at 17:16

Ramesh

1,405
10
19

Thanks Ramesh. In above answer i see informational_schema.tables is used and in your answer i see sys.tables. is there any difference between these two? – Sql Learner Dec 04 '17 at 17:22
The information schema is incomplete and not always reliable -[The case against INFORMATION_SCHEMA views](https://sqlblog.org/2011/11/03/the-case-against-information_schema-views), however it is more portable as it is in the ANSI standard. It really depends on what you perceive to be the lesser of two evils. – GarethD Dec 04 '17 at 17:24
Refer this link https://stackoverflow.com/questions/46187767/difference-between-information-schema-vs-sys-tables-in-sql-server – Mova Dec 04 '17 at 17:25

loop throgh all the data to search particular string

2 Answers2