sql server - How to define SQL variable as string? - Stack Overflow

admin管理员组

文章数量:1344969

For this answer, where do I input the string I want to search for? In this case, 'john-wick'

The goal is to search which table and which column stored the data called 'john-wick'

Am using DBeaver to access the SQL Server database

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

    SET NOCOUNT ON

    DECLARE @TableName NVARCHAR(256), @SearchStr2 NVARCHAR(110)
    DECLARE @ColumnList NVARCHAR(2048), @ColumnJoin NVARCHAR(2048)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
            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
            )
            
            PRINT @TableName    -- For progress monitoring
            SET @ColumnList = 
            (
                SELECT STRING_AGG(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')
            )
            SET @ColumnJoin = REPLACE(@ColumnList,',','+')
            
            IF @ColumnList <> '' 
                EXEC
                (
                        'SELECT ''' + @TableName + ''' AS [Table],' + @ColumnList + '
                        INTO #Results
                        FROM ' + @TableName + ' (NOLOCK) ' +
                        ' WHERE ' + @ColumnJoin + ' LIKE ' + @SearchStr2 + '
                        
                        IF @@ROWCOUNT > 0 SELECT * FROM #Results'
                )
    END
END

本文标签： sql serverHow to define SQL variable as stringStack Overflow