SET NOCOUNT ON --@substr - строка для поиска
DECLARE @name NVARCHAR(128), @column NVARCHAR(128), @substr NVARCHAR(128) = N'%счет на оплату%'
CREATE TABLE #temp ([table] NVARCHAR(128), field NVARCHAR(128), value NTEXT)
--Выберем имена таблиц базы данных
DECLARE s CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' ORDER BY table_name
OPEN s
FETCH NEXT FROM s INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN --Столбцы в таблице
DECLARE c CURSOR FOR
SELECT QUOTENAME(column_name)
FROM information_schema.columns
WHERE data_type IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname') AND TABLE_NAME = @name
SET @name = QUOTENAME(@name)
OPEN c
FETCH NEXT FROM c INTO @column
--Совпадения
WHILE @@fetch_status = 0
BEGIN
EXEC('INSERT INTO #temp select ''' + @name + ''', ''' + @column + ''', ' + @column +
' FROM' + @name + ' WHERE ' + @column + ' LIKE ''' + @substr + '''')
FETCH NEXT FROM c INTO @column
END
CLOSE c
DEALLOCATE c
FETCH NEXT FROM s INTO @name
END
SELECT * FROM #temp ORDER BY [table], field
DROP TABLE #temp
CLOSE s
DEALLOCATE s
Вариант 2:
USE sql1c
SET NOCOUNT ON
DECLARE @Search1 NVARCHAR(128) = '%счет на оплату%'
CREATE TABLE #temp ([Column] NVARCHAR(128), Value NVARCHAR(128))
WHILE @TableName IS NOT NULL
BEGIN
SET @Column = ''
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 (@Column IS NOT NULL)
BEGIN
SET @Column =
(
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 ('varchar', 'char', 'nvarchar', 'char')
AND QUOTENAME(COLUMN_NAME) > @Column
)
IF @Column IS NOT NULL
BEGIN
INSERT INTO #temp
EXEC
(
'SELECT ''' + @TableName + '.' + @Column + ''', LEFT(' + @Column + ', 128)
FROM ' + @TableName + ' (NOLOCK)
WHERE ' + @Column + ' LIKE ' + @Search2
)
END
END
END
SELECT [Column], Value FROM #temp
DROP TABLE #temp
Комментарии пользователей
Эту новость ещё не комментировалиНаписать комментарий
Анонимам нельзя оставоять комментарии, зарегистрируйтесь!