sql server - todas - ¿Cómo buscar en la base de datos del servidor sql una cadena?

sql extraer caracteres de una cadena

Question

Sé que es posible, pero no sé cómo.

Necesito buscar en la base de datos de Microsoft SQL todas las menciones de cadenas específicas. Por ejemplo: me gustaría buscar todas las tablas, vistas, funciones, procedimientos almacenados, ... para la cadena "tblEmployes". (No hay datos dentro de las tablas)

Una de las razones por las que necesito esto, me gustaría eliminar algunas tablas de datos adicionales que se crean, pero me temo que quizás se utilicen en algún lugar de procedimientos o funciones.

Cualquier ayuda es apreciada.

Answer 1

Aquí está el mismo script que envió el usuario l - '''' '''' '''' --------- '''' '''' '''' '''' '', pero corregido para trabajar en un SQL sensible a mayúsculas y minúsculas ejemplo, y con algunas otras mejoras menores.

DROP PROCEDURE IF EXISTS dbo.spFind_Text_In_Database
GO

CREATE PROCEDURE dbo.spFind_Text_In_Database
    @strText_To_Find NVARCHAR(4000),
    @bitExact_Match BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.TABLE_NAME, C.TABLE_SCHEMA, C.COLUMN_NAME, C.DATA_TYPE
    FROM    INFORMATION_SCHEMA.COLUMNS AS C
            INNER Join INFORMATION_SCHEMA.TABLES AS T
                ON C.TABLE_NAME = T.TABLE_NAME
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   TABLE_TYPE = ''BASE TABLE''
            And DATA_TYPE In (''ntext'',''text'',''nvarchar'',''nchar'',''varchar'',''char'')


DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @bitExact_Match = 1
                            THEN ''If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       = '''''' + @strText_To_Find + ''''''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0''
                            ELSE ''If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       Like ''''%'' + @strText_To_Find + ''%''''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0''
                            END,
        @PARAMETERS = ''@DataExists Bit OUTPUT'',
        @i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, ''ReplaceTableName'', QUOTENAME(SchemaName) + ''.'' + QUOTENAME(TableName)), ''ReplaceColumnName'', ColumnName)
        FROM    @Temp
        WHERE   RowId = @i


        PRINT @SQL
        EXEC sp_executesql @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

        SET @i = @i + 1
    END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO

Answer 2

Este código busca el procedimiento y la función pero no busca en la tabla :)

SELECT name FROM   sys.all_objects WHERE  Object_definition(object_id) LIKE ''%text%'' ORDER BY name

Answer 3

Esto buscará una cadena sobre cada base de datos:

declare @search_term varchar(max)
set @search_term = ''something''

select @search_term = ''use ? SET QUOTED_IDENTIFIER ON
select
    ''''[''''+db_name()+''''].[''''+c.name+''''].[''''+b.name+'''']'''' as [object],
    b.type_desc as [type],
    d.obj_def.value(''''.'''',''''varchar(max)'''') as [definition]
from (
    select distinct
        a.id
    from sys.syscomments a
    where a.[text] like ''''%''+@search_term+''%''''
) a
inner join sys.all_objects b
    on b.[object_id] = a.id
inner join sys.schemas c
    on c.[schema_id] = b.[schema_id]
cross apply (
    select
        [text()] = a1.[text]
    from sys.syscomments a1
    where a1.id = a.id
    order by a1.colid
    for xml path(''''''''), type
) d(obj_def)
where c.schema_id not in (3,4) -- avoid searching in sys and INFORMATION_SCHEMA schemas
    and db_id() not in (1,2,3,4) -- avoid sys databases''

if object_id(''tempdb..#textsearch'') is not null drop table #textsearch
create table #textsearch
(
    [object] varchar(300),
    [type] varchar(300),
    [definition] varchar(max)
)

insert #textsearch
exec sp_MSforeachdb @search_term

select *
from #textsearch
order by [object]

Answer 4

Para obtener una tabla por nombre en el servidor sql:

SELECT *
FROM sys.Tables
WHERE name LIKE ''%Employees%''

para encontrar un procedimiento almacenado por nombre:

SELECT name
FROM sys.objects
WHERE name = ''spName''

para obtener todos los procedimientos almacenados relacionados con una tabla:

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE ''%tablename%''
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE ''%tablename%''

Answer 5

Puede exportar su base de datos (si es pequeña) a su disco duro / computadora de escritorio, luego simplemente realice una búsqueda de cadenas a través del editor de texto.

Answer 6

Se le dio acceso a una base de datos, pero no a la tabla donde estaba almacenada mi consulta.

Inspirado por la respuesta de @marc_s, eché un vistazo a HeidiSQL que es un programa de Windows que puede tratar con MySQL, MSSQL y PostgreSQL.

Encontró que también puede buscar una cadena en una base de datos.

¡Buscará en cada tabla y le dará cuántas veces encontró la cadena por tabla!

Answer 7

Si necesita encontrar objetos de base de datos (por ejemplo, tablas, columnas, activadores) por nombre, eche un vistazo a la herramienta FREE Red-Gate llamada Búsqueda SQL que hace esto: busca en toda su base de datos cualquier tipo de cadena (s).

Es una gran herramienta imprescindible para cualquier DBA o desarrollador de bases de datos. ¿Ya mencioné que es absolutamente GRATUITO para usar con cualquier tipo de uso?

Answer 8

Supongamos que no realizará una búsqueda pública en una base de datos completa y que solo desea buscar una palabra clave por su cuenta, esta es la solución más simple, más limpia y más rápida.

1. Vuelca tu base de datos a un archivo.

   $ mysqldump -u root -p your_database > your_database.sql

2. Haz un grep en ese archivo

   $ grep ''keyword'' your_database.sql

Y tu estas listo.

Answer 9

También puede probar ApexSQL Search : es un SSMS gratuito agregado similar a SQL Search.

Si realmente desea utilizar solo sql, es posible que desee probar esta secuencia de comandos

select 
S.name as [Schema], 
o.name as [Object], 
o.type_desc as [Object_Type], 
C.text as [Object_Definition]
from sys.all_objects O inner join sys.schemas S on O.schema_id = S.schema_id
inner join sys.syscomments C on O.object_id = C.id
where S.schema_id not in (3,4) -- avoid searching in sys and INFORMATION_SCHEMA schemas
and C.text like ''%ICE_%''
order by [Schema]

Answer 10

Una vieja pregunta, lo sé, pero aquí va mi versión ... La llamé "Aguja en el pajar" por razones obvias.

Busca valores específicos en cada fila y cada columna, no para nombres de columna, etc.

Ejecute la búsqueda (reemplace los valores de las primeras dos variables, por supuesto):

DECLARE @SEARCH_DB VARCHAR(100)=''REPLACE_WITH_YOUR_DB_NAME''
DECLARE @SEARCH_VALUE_LIKE NVARCHAR(100)=N''%REPLACE_WITH_SEARCH_STRING%''

SET NOCOUNT ON;  
DECLARE col_cur CURSOR FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.columns WHERE TABLE_CATALOG=@SEARCH_DB AND DATA_TYPE NOT IN (''timestamp'', ''datetime'');

DECLARE @TOTAL int = (SELECT COUNT(*)
FROM information_schema.columns WHERE TABLE_CATALOG=@SEARCH_DB AND DATA_TYPE NOT IN (''timestamp'', ''datetime''));


DECLARE @TABLE_CATALOG nvarchar(500), @TABLE_SCHEMA nvarchar(500), @TABLE_NAME nvarchar(500), @COLUMN_NAME nvarchar(500), @DATA_TYPE nvarchar(500);
DECLARE @SQL nvarchar(4000)='''';

PRINT ''-------- BEGIN SEARCH --------'';  
OPEN col_cur;

FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;

BEGIN TRY DROP TABLE ##RESULTS; END TRY BEGIN CATCH END CATCH
CREATE TABLE ##RESULTS( TABLE_CATALOG nvarchar(500), TABLE_SCHEMA nvarchar(500), TABLE_NAME nvarchar(500), COLUMN_NAME nvarchar(500), DATA_TYPE nvarchar(500), RECORDS int)  
DECLARE @SHOULD_CAST bit=0
DECLARE @i int =0
DECLARE @progress_sum bigint=0

WHILE @@FETCH_STATUS = 0  
BEGIN
    -- PRINT '''' + CAST(@i as varchar(100)) +'' of '' + CAST(@TOTAL as varchar(100)) + ''  '' + @TABLE_CATALOG+''.''+@TABLE_SCHEMA+''.''+@TABLE_NAME+'': ''+@COLUMN_NAME+'' (''+@DATA_TYPE+'')'';

    SET @SHOULD_CAST = (SELECT CASE @DATA_TYPE 
                                WHEN ''varchar'' THEN 0
                                WHEN ''nvarchar'' THEN 0
                                WHEN ''char'' THEN 0
                                ELSE 1 END)

    SET @SQL=''SELECT ''''''+@TABLE_CATALOG+'''''' catalog_name, ''''''+@TABLE_SCHEMA+'''''' schema_name, ''''''+@TABLE_NAME+'''''' table_name, ''''''+@COLUMN_NAME+'''''' column_name, ''''''+@DATA_TYPE+'''''' data_type, '' + 
            +'' COUNT([''+@COLUMN_NAME+'']) records ''+
            +'' FROM ''+@TABLE_CATALOG+''.''+@TABLE_SCHEMA+''.''+@TABLE_NAME +
            +'' WHERE '' + CASE WHEN @SHOULD_CAST=1 THEN ''CAST([''+@COLUMN_NAME + ''] as NVARCHAR(max)) '' ELSE '' [''+@COLUMN_NAME + ''] '' END 
            +'' LIKE ''''''+ @SEARCH_VALUE_LIKE + '''''' ''

    -- PRINT @SQL;

    IF @i % 100 = 0
        BEGIN
            SET @progress_sum = (SELECT SUM(RECORDS) FROM ##RESULTS)
            PRINT CAST (@i as varchar(100)) +'' of '' + CAST(@TOTAL as varchar(100)) +'': ''+ CAST (@progress_sum as varchar(100))
        END

    INSERT INTO ##RESULTS (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, RECORDS)
    EXEC(@SQL)

    FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;
    SET @i=@i+1
    -- IF @i > 1000
    --  BREAK
END   
CLOSE col_cur;  
DEALLOCATE col_cur;

SELECT * FROM ##RESULTS WHERE RECORDS>0;

Luego, para ver los resultados, incluso mientras se está ejecutando, desde otra ventana, ejecute:

DECLARE @SEARCH_VALUE_LIKE NVARCHAR(100)=N''%@FLEX@%''
SELECT * FROM ##RESULTS WHERE RECORDS>0;

SET NOCOUNT ON;  
DECLARE col_cur CURSOR FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM ##RESULTS WHERE RECORDS>0;

DECLARE @TABLE_CATALOG nvarchar(500), @TABLE_SCHEMA nvarchar(500), @TABLE_NAME nvarchar(500), @COLUMN_NAME nvarchar(500), @DATA_TYPE nvarchar(500);
DECLARE @SQL nvarchar(4000)='''';

OPEN col_cur;

FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;
DECLARE @i int =0
DECLARE @SHOULD_CAST bit=0

WHILE @@FETCH_STATUS = 0  
BEGIN
    SET @SHOULD_CAST = (SELECT CASE @DATA_TYPE 
                                WHEN ''varchar'' THEN 0
                                WHEN ''nvarchar'' THEN 0
                                WHEN ''char'' THEN 0
                                ELSE 1 END)

    SET @SQL=''SELECT ''''''+@TABLE_CATALOG+'''''' catalog_name, ''''''+@TABLE_SCHEMA+'''''' schema_name, ''''''+@TABLE_NAME+'''''' table_name, ''''''+@COLUMN_NAME+'''''' column_name, ''''''+@DATA_TYPE+'''''' data_type, '' + 
            +'' [''+@COLUMN_NAME+'']''+
            +'', * ''
            +'' FROM ''+@TABLE_CATALOG+''.''+@TABLE_SCHEMA+''.''+@TABLE_NAME +
            +'' WHERE '' + CASE WHEN @SHOULD_CAST=1 THEN ''CAST([''+@COLUMN_NAME + ''] as NVARCHAR(max)) '' ELSE '' [''+@COLUMN_NAME + ''] '' END 
            +'' LIKE ''''''+ @SEARCH_VALUE_LIKE + '''''' ''

    PRINT @SQL;

    EXEC(@SQL)

    FETCH NEXT FROM col_cur INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE;
    SET @i=@i+1
    -- IF @i > 10
    --  BREAK
END   
CLOSE col_cur;  
DEALLOCATE col_cur;

Pocos menciones al respecto:

• usa cursores en lugar de un ciclo de bloqueo while
• puede imprimir el progreso (descomentar si es necesario)
• puede salir después de unos pocos intentos (elimine el IF al final)
• muestra todos los registros
• puedes ajustarlo según sea necesario

DESCARGO DE RESPONSABILIDAD:

• ¡NO lo ejecute en entornos de producción!
• Es lento. Si el DB es accedido por otros servicios / usuarios, POR FAVOR agregue "WITH (NOLOCK)" después de cada nombre de tabla en todas las selecciones, especialmente las de selección dinámica.
• No valida / protege contra todo tipo de opciones de inyección SQL.
• Si su base de datos es enorme, prepárese para dormir un poco, asegúrese de que la consulta no se elimine después de unos minutos.
• Emite algunos valores a la cadena, incluidos ints / bigints / smallints / tinyints. Si no los necesita, colóquelos en la misma lista de exclusión con las marcas de tiempo en la parte superior del script.

Espero que esto ayude.

Answer 11

esto buscará cada columna de cada tabla en una base de datos específica. crea el proceso almacenado en la base de datos en la que deseas buscar

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2 :

CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM    Information_Schema.Columns AS C
            INNER Join Information_Schema.Tables AS T
                ON C.Table_Name = T.Table_Name
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   Table_Type = ''Base Table''
            And Data_Type In (''ntext'',''text'',''nvarchar'',''nchar'',''varchar'',''char'')


DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
                            THEN ''If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       = '''''' + @DataToFind + ''''''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0''
                            ELSE ''If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       Like ''''%'' + @DataToFind + ''%''''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0''
                            END,
        @PARAMETERS = ''@DataExists Bit OUTPUT'',
        @i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, ''ReplaceTableName'', QUOTENAME(SchemaName) + ''.'' + QUOTENAME(TableName)), ''ReplaceColumnName'', ColumnName)
        FROM    @Temp
        WHERE   RowId = @i


        PRINT @SQL
        EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

        SET @i = @i + 1
    END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO

para ejecutarlo solo haz esto:

exec FindMyData_string ''google'', 0

funciona increíblemente bien !!!

Answer 12

tú podrías;

1. Guia la base de datos a un solo archivo y busca el archivo para tblEmployees usando un editor de texto. En Herramientas de administración de SQL Server (SSMS), haga clic con el botón derecho sobre la base de datos y elija Generar scripts.
2. Use SSMS ''View Dependancies'' haciendo clic derecho sobre tblEmployees para ver qué otros objetos dependen de él
3. Utilice una herramienta gratuita de terceros como RedGate SQLSearch para buscar todos los objetos de la base de datos por nombre y contenido por palabras clave.