Cercare un’occorrenza in tutte le tabelle di un db

   1: set ANSI_NULLS ON
   2: set QUOTED_IDENTIFIER ON
   3: go
   4:  
   5: CREATE PROC [dbo].[SearchAllTables]
   6: (
   7:     @SearchStr nvarchar(100)
   8: )
   9: AS
  10: BEGIN
  11:     CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
  12:  
  13:     SET NOCOUNT ON
  14:  
  15:     DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
  16:     SET  @TableName = ''
  17:     SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
  18:  
  19:     WHILE @TableName IS NOT NULL
  20:     BEGIN
  21:         SET @ColumnName = ''
  22:         SET @TableName = 
  23:         (
  24:             SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
  25:             FROM     INFORMATION_SCHEMA.TABLES
  26:             WHERE         TABLE_TYPE = 'BASE TABLE'
  27:                 AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
  28:                 AND    OBJECTPROPERTY(
  29:                         OBJECT_ID(
  30:                             QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
  31:                              ), 'IsMSShipped'
  32:                                ) = 0
  33:         )
  34:  
  35:         WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  36:         BEGIN
  37:             SET @ColumnName =
  38:             (
  39:                 SELECT MIN(QUOTENAME(COLUMN_NAME))
  40:                 FROM     INFORMATION_SCHEMA.COLUMNS
  41:                 WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
  42:                     AND    TABLE_NAME    = PARSENAME(@TableName, 1)
  43:                     AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
  44:                     AND    QUOTENAME(COLUMN_NAME) > @ColumnName
  45:             )
  46:     
  47:             IF @ColumnName IS NOT NULL
  48:             BEGIN
  49:                 INSERT INTO #Results
  50:                 EXEC
  51:                 (
  52:                     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
  53:                     FROM ' + @TableName + ' (NOLOCK) ' +
  54:                     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
  55:                 )
  56:             END
  57:         END    
  58:     END
  59:  
  60:     SELECT ColumnName, ColumnValue FROM #Results
  61: END

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }




No Comments


You can leave the first : )