sql fråga som letar i alla tabeller

---> sp_foreachtable

Söker och hittar en del... men förstår inte riktigt hur jag ska använda det jag hittar.

Fann...

Search All Fields in db for a Specific String

DROP PROCEDURE UGLYSEARCH
go
CREATE PROCEDURE UGLYSEARCH 
-- EXEC UGLYSEARCH 'TEST'
-- EXEC UGLYSEARCH 'TEST',1
@SEARCHSTRING VARCHAR(50),
@SEARCHBYCOLUMN   INT = 0
AS
SET NOCOUNT ON
DECLARE @SQL      VARCHAR(8000),
@TABLENAME        VARCHAR(60),
@COLUMNNAME       VARCHAR(60),
@COLZ             VARCHAR(7000)

CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(7800))
SELECT 
  SYSOBJECTS.NAME AS TBLNAME,
  SYSCOLUMNS.NAME AS COLNAME,
  TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
  INTO #FKFINDER
    FROM SYSOBJECTS
      INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
    WHERE SYSOBJECTS.XTYPE='U'
    AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
    AND SYSCOLUMNS.LENGTH >= LEN(@SEARCHSTRING)
    ORDER BY TBLNAME,COLNAME

  IF @SEARCHBYCOLUMN = 0 
    BEGIN
      DECLARE C1 CURSOR FOR 
        SELECT DISTINCT TBLNAME FROM #FKFINDER ORDER BY TBLNAME
      OPEN C1
      FETCH NEXT FROM C1 INTO @TABLENAME
      WHILE @@FETCH_STATUS <> -1
        BEGIN
          SET @COLZ=''
          SELECT   @COLZ= @COLZ + COLNAME + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #FKFINDER WHERE TBLNAME=@TABLENAME
          --@COLZ has a trailing 'OR ' which must be removed
          SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
          --PRINT @COLZ
          SET @SQL = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM  ' + @TABLENAME + '  WHERE ' + REPLACE(@COLZ,'''','''''') +  ''') ;'
          --PRINT @SQL
          EXEC (@SQL)
        FETCH NEXT FROM C1 INTO @TABLENAME
        END
      CLOSE C1
      DEALLOCATE C1
    END --IF
  ELSE
    BEGIN
      DECLARE C1 CURSOR FOR 
        SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME
      OPEN C1
      FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
      WHILE @@FETCH_STATUS <> -1
        BEGIN
          SET @COLZ=''
          SELECT   @COLZ= @COLZ + COLNAME + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #FKFINDER WHERE TBLNAME=@TABLENAME
          --@COLZ has a trailing 'OR ' which must be removed
          SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
          --SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' +  @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
          SET @SQL = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' +  @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' +  @COLUMNNAME + ''','' SELECT * FROM  ' + @TABLENAME + '  WHERE ' + @COLUMNNAME + ' LIKE ''''' + @SEARCHSTRING + ''''' '') ;'
          PRINT @SQL
          EXEC (@SQL)
        FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
        END
      CLOSE C1
      DEALLOCATE C1
    END --ELSE

SELECT * FROM #RESULTS

Det hjälpte mig inte mycket då den generarade massor med fel.

Kan man lösa det med något annat koncept. Exportera till en text fil och söka i den kanske?
Problemet är nog att den blir gigantiskt stor för databasen är inte liten.

How to search all columns of all tables in a database for a keyword?

Testade på Northwind och Pubs och den verkar fungera galant.

Det låter precis som det jag är ute efter Dino!

Får dock följande fel när jag kör koden i SQL Query analyzer. Den jobbar i ca 2 minuter innan felmeddelandet dyker upp.

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'SearchAllTables'.

Jag har inte någon erfarenhet av query analyzern. Klipper in följande kod och försöker exekvera den. Är det något annat som man behöver göra?

EXEC SearchAllTables '20454'
GO 

CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN

	-- 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: [url]http://vyaskn.tripod.com[/url]
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 28th July 2002 22:50 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')
					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

My bad. Det framgick inte att man enbart skulle köra CREATE PROC SearchAllTables
... en gång för att skapa den. Min okunnighet.

Skriptet fungerar utmärkt! Jag röstar på att webforum får det alternativa nobelpriset för att ha sparat 700 timmar i söktid för mig.