--Declare variables and initialize them
DECLARE @TableSchema AS VARCHAR(50) = 'dbo'
DECLARE @TableName AS VARCHAR(50) = 'TableName'
DECLARE @SearchString AS VARCHAR(50) = 'SearchString'
DECLARE @Qry AS NVARCHAR(MAX)
DECLARE @Columns AS VARCHAR(MAX)
--Prepare the columns
SET @Columns = STUFF((
SELECT
'+' + CASE WHEN IS_NULLABLE = 'YES' THEN 'ISNULL(' + C.COLUMN_NAME + ','''')' ELSE C.COLUMN_NAME END
FROM
INFORMATION_SCHEMA.COLUMNS C
WHERE
C.TABLE_SCHEMA = COALESCE(@TableSchema,C.TABLE_SCHEMA)
AND C.TABLE_NAME = COALESCE(@TableName,C.TABLE_NAME)
AND C.DATA_TYPE IN ('CHAR','NCHAR','NTEXT','NVARCHAR','TEXT','VARCHAR')
FOR XML PATH('')),1,1,'')
--Prepare the Query
SET @Qry = N' SELECT ' +
' * ' +
' FROM ' +
@TableSchema + '.' + @TableName +
' WHERE ' +
@Columns + ' LIKE ''%' + @SearchString + '%'''
--Execute the Query
EXEC SP_EXECUTESQL @Qry
Be the first to comment
You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.