Cerca in tutte le tabelle, tutte le colonne per un valore specifico SQL Server

Ho un valore specifico, diciamo stringa “commenti”. Ho bisogno di trovare tutte le istanze di questo nel database in quanto ho bisogno di fare un aggiornamento sul formato per cambiarlo in (*) Commenti.

Come posso fare questo? Il database è in formato SQL Server 2000.

Ho appena aggiornato il mio post sul blog per correggere l’errore nello script che avevi Jeff, puoi vedere lo script aggiornato qui: Cerca in tutti i campi nel database di SQL Server

Come richiesto, ecco la sceneggiatura nel caso in cui lo desideri, ma ti consiglio di rivedere il post del blog in quanto lo aggiorno di volta in volta

DECLARE @SearchStr nvarchar(100) SET @SearchStr = '## YOUR STRING HERE ##'   -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Updated and tested by Tim Gaunt -- http://www.thesitedoctor.co.uk -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010 -- Date modified: 03rd March 2011 19:00 GMT 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  DROP TABLE #Results 

Ne ho pubblicato uno qui: FullParam SQL Blog

 /* Reto Egeter, fullparam.wordpress.com */ DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */ SET @FullRowResult = 1 SET @FullRowResultRows = 3 SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */ SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */ SET @SearchStrInXML = 0 /* Searching XML data may be slow */ IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20)) SET NOCOUNT ON DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110) SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''') DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20)) WHILE @TableName IS NOT NULL BEGIN SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME) AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 ) IF @TableName IS NOT NULL BEGIN DECLARE @sql VARCHAR(MAX) SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2) AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1) AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ') AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)' INSERT INTO @ColumnNameTable EXEC (@sql) WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable) BEGIN PRINT @ColumnName SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),''' ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue INSERT INTO #Results EXEC(@sql) IF @@ROWCOUNT > 0 IF @FullRowResult = 1 BEGIN SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' + ' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue EXEC(@sql) END DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName END END END SET NOCOUNT OFF SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results GROUP BY TableName, ColumnName, ColumnValue, ColumnType 

Query qui sotto funziona ma molto lento … copiato da vyaskn.tripod.com

 Declare @SearchStr nvarchar(100) SET @SearchStr='Search String' 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') 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 

È ansible eseguire una query sulla vista del database sys.tables per ottenere i nomi delle tabelle, quindi utilizzare questa query per creare autonomamente un’altra query per eseguire l’aggiornamento sul retro. Per esempio:

 select 'select * from '+name from sys.tables 

ti fornirà uno script che eseguirà una selezione * rispetto a tutte le tabelle nel catalogo di sistema, potresti modificare la stringa nella clausola select per fare l’aggiornamento, purché tu sappia che il nome della colonna è lo stesso su tutte le tabelle Desidero aggiornare, quindi il tuo script sarà simile a qualcosa:

 select 'update '+name+' set comments = ''(*)''+comments where comments like ''%comment to be updated%'' ' from sys.tables 

È inoltre ansible prevedere nelle query della tabella solo le tabelle che hanno un nome in un determinato formato o in un sottoinsieme per cui si desidera creare lo script di aggiornamento.