texto - Busque una cadena buscando todas las tablas en SQL Server Management Studio 2008

ejemplos de consultas en sql

Question

¿Hay alguna manera de buscar una cadena en todas las tablas de una base de datos en SQL Server Management Studio 2008?

Quiero buscar una cuerda, di john . El resultado debe mostrar las tablas y sus respectivas filas que contienen john .

Answer 1

He escrito un SP para esto que devuelve los resultados de búsqueda en forma de nombre de tabla, los nombres de columna en los que se encontró la cadena de palabras clave de búsqueda y las búsquedas en las filas correspondientes, como se muestra en la siguiente captura de pantalla.

Puede que esta no sea la solución más eficiente, pero siempre puede modificarla y usarla de acuerdo con sus necesidades.

IF OBJECT_ID(''sp_KeywordSearch'', ''P'') IS NOT NULL
DROP PROC sp_KeywordSearch
GO
CREATE PROCEDURE sp_KeywordSearch @KeyWord NVARCHAR(100)
AS
BEGIN
DECLARE @Result TABLE
(TableName NVARCHAR(300),
ColumnName NVARCHAR(MAX))
DECLARE @Sql NVARCHAR(MAX),
@TableName NVARCHAR(300),
@ColumnName NVARCHAR(300),
@Count INT
DECLARE @tableCursor CURSOR
SET @tableCursor = CURSOR LOCAL SCROLL FOR
SELECT N''SELECT @Count = COUNT(1) FROM [dbo].['' + T.TABLE_NAME + ''] WITH (NOLOCK) WHERE CAST(['' + C.COLUMN_NAME +
''] AS NVARCHAR(MAX)) LIKE ''''%'' + @KeyWord + N''%'''''',
T.TABLE_NAME,
C.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES AS T WITH (NOLOCK)
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C WITH (NOLOCK)
ON T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
T.TABLE_NAME = C.TABLE_NAME
WHERE T.TABLE_TYPE = ''BASE TABLE'' AND
C.TABLE_SCHEMA = ''dbo'' AND
C.DATA_TYPE NOT IN (''image'', ''timestamp'')
OPEN @tableCursor
FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Count = 0
EXEC sys.sp_executesql
@Sql,
N''@Count INT OUTPUT'',
@Count OUTPUT
IF @Count > 0
BEGIN
INSERT INTO @Result
(TableName, ColumnName)
VALUES (@TableName, @ColumnName)
END
FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
END
CLOSE @tableCursor
DEALLOCATE @tableCursor
SET @tableCursor = CURSOR LOCAL SCROLL FOR
SELECT SUBSTRING(TB.Sql, 1, LEN(TB.Sql) - 3) AS Sql, TB.TableName, SUBSTRING(TB.Columns, 1, LEN(TB.Columns) - 1) AS Columns
FROM (SELECT R.TableName, (SELECT R2.ColumnName + '', '' FROM @Result AS R2 WHERE R.TableName = R2.TableName FOR XML PATH('''')) AS Columns,
''SELECT * FROM '' + R.TableName + '' WITH (NOLOCK) WHERE '' +
(SELECT ''CAST('' + R2.ColumnName + '' AS NVARCHAR(MAX)) LIKE ''''%'' + @KeyWord + ''%'''' OR ''
FROM @Result AS R2
WHERE R.TableName = R2.TableName
FOR
XML PATH('''')) AS Sql
FROM @Result AS R
GROUP BY R.TableName) TB
ORDER BY TB.Sql
OPEN @tableCursor
FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @Sql
SELECT @TableName AS [Table],
@ColumnName AS Columns
EXEC(@Sql)
FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
END
CLOSE @tableCursor
DEALLOCATE @tableCursor
END

Answer 2

Intente el procedimiento para buscar todas las columnas de todas las tablas para una cadena de búsqueda determinada:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
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'', ''int'', ''decimal'')
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

Answer 3

Para actualizar la respuesta de para el servidor SQL 2012. ''FROM '' + @TableName + '' (NOLOCK) '' cambiar: ''FROM '' + @TableName + '' (NOLOCK) '' a FROM '' + @TableName + ''WITH (NOLOCK) '' +

De lo contrario, obtendrá el siguiente error: la Deprecated feature ''Table hint without WITH'' is not supported in this version of SQL Server.

A continuación se muestra el procedimiento almacenado actualizado completo:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
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'', ''int'', ''decimal'')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
''SELECT '''''' + @TableName + ''.'' + @ColumnName + '''''', LEFT('' + @ColumnName + '', 3630)
FROM '' + @TableName + ''WITH (NOLOCK) '' +
'' WHERE '' + @ColumnName + '' LIKE '' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END

Answer 4

Si es como yo y tiene ciertas restricciones en un entorno de producción, puede utilizar una variable de tabla en lugar de tabla temporal, y una consulta ad-hoc en lugar de un procedimiento de creación.

Por supuesto, dependiendo de su instancia de servidor SQL, debe admitir variables de tabla.

También agregué una declaración USE para reducir el alcance de la búsqueda

USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = ''SEARCH_TEXT''
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
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'', ''int'', ''decimal'')
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

Answer 5

Un poco tarde pero con suerte útil.

¿Por qué no probar algunas de las herramientas de terceros que se pueden integrar en SSMS?

He trabajado con ApexSQL Search (100% gratis) con buen éxito tanto para la búsqueda de esquemas como de datos y también hay un paquete de herramientas SSMS que tiene esta característica (no gratuita para SQL 2012 pero bastante asequible).

El procedimiento almacenado arriba es realmente genial; es solo que esto es mucho más conveniente en mi opinión. Además, requeriría algunas modificaciones pequeñas si desea buscar columnas de fecha y hora o columnas GUID y tal ...

Answer 6

Un poco tarde, pero puedes encontrar fácilmente una cadena con esta consulta

DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = ''StringtoSearch''
DECLARE tables_cur CURSOR FOR SELECT ss.name +''.''+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE type = ''U''
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id
AND system_type_id IN (167, 175, 231, 239)
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = ''IF EXISTS (SELECT * FROM '' + @table_name + '' WHERE ['' + @column_name + '']
LIKE ''''%'' + @search_string + ''%'''') PRINT '''''' + @table_name + '', '' + @column_name + ''''''''
EXECUTE(@sql_string)
FETCH NEXT FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur