recherche dans toutes les tables, toutes les colonnes pour une valeur spécifique SQL Server

Question

J'ai une valeur spécifique, disons chaîne «commentaires». Je dois trouver toutes les occurrences de cela dans la base de données car je dois faire une mise à jour du format pour le changer en (*) Commentaires. Comment puis-je faire ceci? S'il vous plaît aider! La base de données est au format SQL Server 2000.

Answer 1

Je viens de mettre à jour mon article de blog pour corriger l'erreur dans le script que vous rencontriez Jeff, vous pouvez voir le script mis à jour ici: Rechercher dans tous les champs de la base de données SQL Server

Answer 2

J'en ai publié un ici: FullParam SQL Blog

 /* Reto Egeter, fullparam.wordpress.com */

DECLARE @SearchStrColumnName nvarchar(100), @SearchStrColumnValue nvarchar(100), @SearchStrInXML bit
SET @SearchStrColumnValue = '%11%' /* use LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all, use 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 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'',''bigint'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar''' + 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
SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @SearchStrInXML WHEN 1 THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @SearchStrInXML WHEN 1 THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
INSERT INTO #Results
EXEC(@sql)
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
 

Answer 3

Vous pouvez lancer une requête sur le sys.les tables de la base de données pour obtenir les noms des tables, et ensuite utiliser cette requête pour construire vous-même une autre requête pour faire la mise à jour sur le dos de qui. Par exemple:

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

vous donnera un script qui sera exécuté un select * sur toutes les tables dans le catalogue système, vous pouvez modifier la chaîne dans la clause select pour faire votre mise à jour, aussi longtemps que vous savez le nom de la colonne est la même sur toutes les tables que vous souhaitez mettre à jour, de sorte que votre script devrait ressembler à quelque chose comme:

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

Vous pouvez aussi alors prédicat sur les tables de requête pour inclure uniquement les tables qui ont un nom dans un certain format, ou dans un sous-ensemble vous souhaitez créer le script de mise à jour pour.

Answer 4

La requête ci-dessous fonctionne mais très lentement ... copié de 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
 

Answer 5

Voici ce que vous recherchez: Recherche dans tous les champs de la base de données SQL Server.