-- To search in all tables content and return a list of tables where the value
-- has been founded
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SearchAllTables]
@SearchStr NVARCHAR(100)
AS
BEGIN
-- Create results table with NVARCHAR to support Arabic characters
CREATE TABLE #Results (
TableName NVARCHAR(370),
ColumnName NVARCHAR(128),
ColumnValue NVARCHAR(MAX),
PrimaryKeyColumn NVARCHAR(128),
PrimaryKeyValue NVARCHAR(500)
)
SET NOCOUNT ON
DECLARE @TableName NVARCHAR(256),
@ColumnName NVARCHAR(128),
@SearchStr2 NVARCHAR(110),
@PKColumn NVARCHAR(128),
@SQL NVARCHAR(MAX)
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
)
-- Get primary key column for current table
IF @TableName IS NOT NULL
BEGIN
SET @PKColumn = (
SELECT TOP 1 QUOTENAME(cu.COLUMN_NAME)
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
AND tc.TABLE_SCHEMA = cu.TABLE_SCHEMA
AND tc.TABLE_NAME = cu.TABLE_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tc.TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND tc.TABLE_NAME = PARSENAME(@TableName, 1)
ORDER BY cu.ORDINAL_POSITION
)
END
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', 'text', 'ntext')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
-- Build dynamic SQL with NVARCHAR casting to preserve Arabic characters
SET @SQL = N'
INSERT INTO #Results (TableName, ColumnName, ColumnValue, PrimaryKeyColumn, PrimaryKeyValue)
SELECT
''' + @TableName + ''',
''' + @ColumnName + ''',
LEFT(CAST(' + @ColumnName + ' AS NVARCHAR(MAX)), 3630),
' + CASE WHEN @PKColumn IS NOT NULL
THEN '''' + @PKColumn + ''''
ELSE 'NULL'
END + ',
' + CASE WHEN @PKColumn IS NOT NULL
THEN 'CAST(' + @PKColumn + ' AS NVARCHAR(500))'
ELSE 'NULL'
END + '
FROM ' + @TableName + ' WITH (NOLOCK)
WHERE CAST(' + @ColumnName + ' AS NVARCHAR(MAX)) LIKE N' + @SearchStr2
EXEC sp_executesql @SQL
END
END
END
SELECT
TableName,
ColumnName,
ColumnValue,
PrimaryKeyColumn,
PrimaryKeyValue
FROM #Results
ORDER BY TableName, ColumnName
DROP TABLE #Results
END
GO
No comments:
Post a Comment